Mr. Excel (Bill Jelen) Challenge | Flattening Crazy Data | Podcast 2316

Mr. Excel, Bill Jelen, was presented with a challenge where someone's data was twisted in a weird sort of way and needed to be untwisted; e.g. the data needed to be flattened.

Bill asked for other solutions than his own. Here's mine. This is one of those examples where it helped me most to bring the data into Power Query and then Transpose it. This may not have been the most efficient solution. However, after the Transpose, I could see a clear bath to the end.

The solution involves:
- Transpose
- Unpivot
- Pivot

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



My book: Guerrilla Data Analysis 2nd Edition

[MrXL]

At 6:12 of the video, I could've sworn I WAS Kaique Pacheco because I fell out of my chair with how easy you added the total column. Great video!

[excelworx]

Woooops! I had to watch it a second time to really understand what you were doing Oz! Kaique would be impressed

[wildecorrea]

That was one of the best Power Query's challenge I've seen. Congratulations on this video! Hi from Brazil.

[excelisfun]

Thanks for the Kaique radness, Oz!!!!

[wildecorrea]

Great solution, master Oz! Congratulations! I've just seen the two solutions, yours and Mr. Excel's. Both are great! I have a sugestion. At 07:17 of the video (Ribbon Formulas, Name Manager, UglyData), you could also refers to the range as = Sheet1!$3:$9. I mean, till the right end of the sheet, selecting all the rows, 3 to 9. And also if you transform this interval into a Table (by selecting the interval, ribbon Insert, Table option), rigth? So it can read automatically other employees' inputs. I've just tried and it worked.

[DougHExcel]

power query + ugly data = super fine data...! great video Oz!

[wayneedmondson]

Hi Oz. Nice transformation! Thanks for sharing it. Thumbs up!!

[Buzzygirl]

This is amazing... I work in Excel all day, every day, but my needs are not this critical when it comes to sorting data. Gives me something to think about! Thanks!

[HowToExcelBlog]

Oh and cool trick about adding a total column which I didn't know 😮🤯

[jeisonsanchez]

Subscribed! - after watching this one video. I’ve used power query to merge tables. I’m obviously missing out on so much more.

[entertainmentgalaxy]

Love it OZ how you transformed ugly data. I think begins with Q may work in both conditional column and filtered row step. thanks for sharing ugly crazy fun:).

[GrainneDuggan_Excel]

Great solution Oz, especially if you know the data will only ever be viewed in the final format. I would have stopped PQ at unpivot, and not added the totals column. Then I would do all analysis in an excel pivot table. So interesting to see the different solutions you each have used.

[HusseinKorish]

Amazing .... and just perfect ....thanks OZ

[Softwaretrain]

It was really hot, creative solution.
Just I think conditional column for every quarter could be change by smaller way.
Thanks.

[barttitulaerexcelbart]

Great video Oz, I am always looking at your clock at the backgroud, figuring out how long it took you to solve this great puzzle. Can't figure it out, so I am asking: how long did you worked on this solution (so not the video editing)? All kinds of techniques in the right order: Bravo !!

[crazyfurbabieslady]

I found you on LinkedIn. I love your personality and videos. Do you have classes for beginners? Thank you

[hariomgupta]

Sir provide working excel file because i want to practice same data and always provide data file in your youtube video