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
- 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.
Rules
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!
