Most people are aware that Power Query lets you work with tables that have columns containing other “inner” tables. The classic example is a table representing an Excel workbook where a single column contains several “inner” tables, each representing a sheet. You can then combine all the sheets together with a couple clicks and save yourself a huge amount of time copying and pasting.
What’s less well known is that every function that you can perform on the “outer” table, you can also perform on the “inner” table as well. So when you click the “Use First Row as Headers” button, it promotes the headers of the “outer” table using the Table.PromoteHeaders() function;. that same function can be called within a custom column to promote the headers of each of the “inner” tables as well. Combining this with Group By’s Do Not Aggregate Feature you can perform some impressive tricks without a lot of work.
In this video we’ll show you an example of using these techniques to clean up some data that’s very easy for people to read, but very tricky to work with otherwise. Hope you enjoy!
