Power Query Dynamic Merge & Expansion
Power Query in it’s default form is simply glorious and allows any one with a basic knowledge to merge and collate data sets with the use of the excellent User Interface. But it comes at a price. Hard coding. Now you’ll find in many organisations where power query has been used to transform data and processes that most of the column headers are hard coded into the m queries. So once any of the source column headers change or new ones are added or deleted that you’ll have to go back and painstakingly fix each query. Now in this 2 part video series i show you how to dynamically merge and expand datasets you combine in power query. No longer will you need to go back and edit old merges. You simply just refresh and the dataset is aligned with source.
Dynamic Merge
The simplest of the 2 i’m showing in this series. Using the Mquery function table.columnnames and a couple of list functions you get a perfectly dynamic merge.
Dynamic Expand
Now this is a bit more complicated as we’ll be looking into using the same function as above but then creating a query within a query in m query to provide us with a Dynamic Expand.
I had a great time making these videos and also learned a whole lot about dynamic queries and list functions. Hope this helped you in your data journey. BASensei out!
