Partners,,,, russianxnxx

The world is full of garbage data and there is no dearth of nasty data cleaning problems. From that pile of trash, I present to you another Power Query challenge.


Convert Multiple Column Groups to Rows – Power Query Challenge


Consider this Double Headed Data

Convert Multiple Column Groups to Rows Data

  • Each city (the main header) will have 2 sub headers – Name and Age
  • The number of rows for each city columns (i.e. pair of Name and Age) can be different.


Expected Output

You need to convert the multiple column groups to rows, like this.

Convert Multiple Column Groups to Rows in Power Query



First, use Power Query! And then your query should be able to dynamically handle the following conditions,

  • The number of city columns could change.
  • The number of sub headers under each city could also change. For instance, each city can have 3 columns – Name, Age and Height.



Post your M Code in the comments below!



    ⬇️ Pop in your Name & Email to get the file!

    Topics that I write about...