Power Query: Stacking Columns of Data (Unpivot, Split Columns by delimiter, and everything!)
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: • The Wildest Excel Spreadsheet Ever: ... )
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: [email protected]
Website: http://ozdusoleil.com
My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analy...
My old blog: http://datascopic.net/blog-2-2