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

[@Sumanth1601]

Excellent content.. loved it.. A tip, the Excel.Workbook function has an additional parameter, by keying in "true" you can skip the promote headers step.

[@FsoOmar]

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

[@FsoOmar]

Please KZbin, Could I like this tutorial a million times?

[@osoriomatucurane9511]

Absolutely Outstanding. Concise and Crystal Clear. Truly Power Query Magic

[@christianvidea]

Great content... exactly what I needed to combine several sheets with different headers into one master sheet!

[@txreal2]

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

[@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!

[@wanman7]

Thanks for sharing your knowledge. Used the pattern extraction and others. Great stuff! Love the modem sound during the intro as well! 56K!

[@basensei8699]

Glad it was helpful!

[@kebincui]

Excellent as always👍. Thanks James❤

[@RonDavidowicz]

Great tips!

[@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...

[@boissierepascal5755]

You're a Meta Wizard 🧙🏽 🤟🏽

[@anneshields6950]

Can I apply this to a folder containing CSV files so that I do not have to convert each of them to Excel?

[@danielhervert3629]

Im 50% sure, that you are able to do so. 1:32 instead of using Excel.Workbook, use CSV.Document. Im having the same issue and will try this tomorrow morning in the office.

[@adrianoschwenkberg6773]

nice workflow, thank you. may I give you an alternative way for this common typo-problem here kzbin.info/www/bejne/eIbEq5uXaLhpiNEsi=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.

[@osoriomatucurane9511]

Absolutely Outstanding. Concise and Crystal Clear. Truly Power Query Magic

[@osoriomatucurane9511]

Absolutely Outstanding. Concise and Crystal Clear. Truly Power Query Magic