This was interesting, and the solution is sweet.
We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns.
This was a real challenge that was recently presented to me. It was overwhelming, and I searched for existing solutions. The search turned up complex methods that got into M code or complex processes of peeling the data apart and then putting it back together.
But I thought of a simpler way. First …
In my last video I described how to use a small, nonsensical model to focus on a solution. So, I built a model.
(the previous video: https://youtu.be/WkOR6telzdA)
Second. The small model revealed that the last step in our solution should be an UnPivot. Thus, an intermediate step need to be something that looks like it needs to be unpivoted.
Therefore, the trick is in getting the data set up right in Excel BEFORE taking it into Power Query (Get and Transform)!!!
It was so sweet to see how simple the solution was … after seeing it as a very small model.
Check it out!
Contact me: firstname.lastname@example.org
My book: Guerrilla Data Analysis 2nd Edition
My old blog: http://datascopic.net/blog-2-2