Transforming Nested Tables in Power Query

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!

Share This:
Twitter
Facebook
LinkedIn

Stay up to date with the latest customer data news, expert guidance, and resources.

Iteration and Error Handling in M Query

All iteration roads in M Query eventually lead to the List.Generate function. In strengthening our data manipulation skills through Power Query, we absolutely need this looping function.

More Resources

Trusted By Over 6 Million End Users​

Industry leaders and over 6 million end users choose SkyPoint as their comprehensive and compliant Modern Data Stack Platform. A Gold Certified Microsoft Partner, SkyPoint Cloud turns siloed customer data into connected customer experiences.