Скачать или смотреть Steamy Data Transformation, Pivot, UnPivot & Expression.Error

We have a matrix showing Students and the courses they took: Connie took Menu Planning and Whiskey, Bourbon & Rye. But now, we'd like a view that lists the courses and which students took those courses; e.g., Who took the Nutrition course?

MarvP in Seattle hit me with this challenge and on one of the Excel forums, there was an Array Formula solution, and a solution with some List and M-Code trickery. I believe I came up with a sweeter, steamy, more sexy solution in Power Query with Pivot - Don't Aggregate, UnPivot, Duplicate Column, Merge Columns.

I also show you one of those weird, unhelpful error messages in Power Query (Get and Transform): Expression.Error

Basically, when using Pivot - Don't Aggregate, we're asking Power Query to put our raw data into a grid. Don't count it, sum it, or anything. But, if there are duplicates, Don't Aggregate craps out.

A regular Pivot could give us a 2, 3 or whatever. But the Don't Aggregate piece needs a place for each of those 2 or 3.

Check out the video, let me know if you have questions. And check out my courses at LinkedIn Learning for more Power Query and Excel.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/ins...


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