Power Query - Combine Files with inconsistent column names! 😎🍔

In this video i show you how to cater for this common use case where you want to combine various files into one but all of the files have different column names. This video you'll learn how to define the destination columns and remove the ones you don't need.😎🍔
#data #dataconsistency #dataanalysis #dataanalytics #powerquery #powerbi #excel #mquery
File used in video
github.com/jbotes/powerbiTuto...
Source Files
github.com/jbotes/powerbiTuto...
github.com/jbotes/powerbiTuto...
github.com/jbotes/powerbiTuto...
Functions used:
Table.TransformColumns
Table.PromoteHeaders
Excel.Workbook
Table.Combine
Value.Metadata
Meta
Table.RenameColumns
List.Distinct
List.Transform
Table.selectrows
Table.ToRows
Noteworthy
1 - Using Meta to refer to a step in another query

[@Alpacastan21m]

That meta tip is gold. Thank you

[@tonybatty504]

Great as usual but I'll have to go through it a couple of times to work it out!

[@txreal2]

The meta magic is incredible! Just what I have been looking for.
Thanks

[@SndfOmar]

Man! You are a freak of PQ. Thanks dude.

[@RonDavidowicz]

Great tips!

[@kebincui]

Excellent as always👍. Thanks James❤

[@boissierepascal5755]

You're a Meta Wizard 🧙🏽 🤟🏽

[@ExcelWithChris]

I have a card in Power BI showing the last value of a column in a date format. How can I make the background colour change if the date displayed is older than two days from today. I have clicked the FX button next to the background colour in the card formatting section, but it only allows some conditional formatting with a text option, no formula option. Thanks

[@rauljimenez5485]

You have to develop a measure that checks the condition and returns the hex color code that you want. E.g. if condition met then "#4B55W4" (here you place the color code you want)

[@larmondoflairallen4705]

The "meta" trick is very useful in some situations, but it should come with a big warning that if you add steps to the end of the query containing the "meta" statement, you have to manually cut/paste that part to the end of whatever the new "last step" is. Otherwise, any queries that depend on that meta step will break. Unfortunately, it isn't one of those persistent things that automatically updates the M code when you add more steps 😒

[@adrianoschwenkberg6773]

That's the reason why I prefer the record-replacement trick instead of using hardcoded meta step-value . Just replace the LET and the IN part with square brackets and voilà... your query keeps functional and you can reference each step you want from outside.

[@larmondoflairallen4705]

​@@adrianoschwenkberg6773 Ohhhhhh, that's the thing where you convert the whole query into one big record, right? I saw someone do that, and I was like, "Well, that's interesting, but I don't know what I would do with it." Thanks!!

[@adrianoschwenkberg6773]

@larmondoflairallen4705 for example as a container for your personal custom function collection...

[@adrianoschwenkberg6773]

nice workflow, thank you.
may I give you an alternative way for this common typo-problem here ua-cam.com/video/BUazha3S6Ro/v-deo.htmlsi=mgUNjhxXRUT5fo7Z&t=129
After autocomplete the functionname by pressing tab-key, then hold shift-key + Down-Arrow. Now you can type the open-bracket and the whole expression becomes wrapped arround the brackets and you dont even need to close it.
Second note, i prefer to type functionnames by starting with the period-key. For example .Combine instead of Table.Combine. In that case you have less typing and the autocomplete dont mess with double-ing the text TableTable.Combine.

[@larmondoflairallen4705]

That is a handy tip. It will be a grand and glorious day when Microsoft finally fixes that infuriating "feature" that everyone complains about.