Replace Each x in a Matrix With Column Headers in PowerQuery

Imagine a grid that has students and subjects. In the Algebra column, Eric has an x. How do we replace the x in the column to show ‘Algebra’?

While I was at ExcelWeekend4 someone asked how he could replace each x in a matrix with its respective column header.

The solution is tough but it’s sexy. It requires a full outer join, Unpivot, Pivot – Don’t Aggregate. But first, we have to break the original data into 4 pieces. Check it out!

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2