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!

Brian Grant

Brian Grant

Brian Grant is a masterful Senior Analytics Consultant by day, and a Power BI legend by night. A passion for Power BI drove him to holistically master the tool from all three perspectives: M, DAX, and the visualization layer. Brian is a constant teacher who leads Power BI training sessions, while sharing his wealth of expertise through video and blogging resources.
Share on twitter
Share on facebook
Share on linkedin
Share on email


Subscribe to CSG. We’ll supply your inbox with the latest blog posts, training videos, and upcoming events.

Ready to wrangle your data?