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

Подписаться 3,1 тыс.

50% 1

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

Опубликовано:

23 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 14

@kebincui 9 дней назад

Excellent as always👍. Thanks James❤

@RonDavidowicz 9 дней назад

Great tips!

@tonybatty504 8 дней назад

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

@boissierepascal5755 9 дней назад

You're a Meta Wizard 🧙🏽 🤟🏽

@SndfOmar 9 дней назад

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

@txreal2 9 дней назад

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

@ExcelWithChris 9 дней назад

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

@larmondoflairallen4705 8 дней назад

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 8 дней назад

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 7 дней назад

@@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 7 дней назад

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

@adrianoschwenkberg6773 9 дней назад

nice workflow, thank you. may I give you an alternative way for this common typo-problem here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BUazha3S6Ro.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 8 дней назад

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