Complex Data Sort Challenge - Power Query Solution

I was contacted recently with a complex sort challenge on some data, and this video is my Power Query solution.

This video will walk you through the steps to tur this awkward messy data into something Excel loves, and sorting it would be simple.

And all this can be refreshed with 1 click of a button.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[mueez]

Great Stuff. However I just figured if you delete index column before pivot dont aggregate you can skip 2 additional steps. This would also eliminate any hassle when dealing with more than 3 rows of data

[stephenmccomb]

Fantastic demonstration Alan and very easy to follow. Thank you for uploading the video.

[lenac]

I know how modulo works but never really know the real uses for it until now. So happy to learn this useful trick. Not sure what's the purpose of adding an index column if we are adding the modulo column. It's redundant? May need to experiment to figure out.

[wayneedmondson]

Hi Alan.. great trick. PQ is so useful and your technique is very clever. Here's another challenge.. after creating the proper table sorted.. is there a way to use PQ to write it back to a worksheet in the same form as the original, but now sorted? So, the proper table that you created would be an interim step used to sort the original data and then write the sorted results to a second sheet, but in the same format as the original data with the empty cells and side by side name and stacked address? I ask because the author of the data often wants it back in the same format as provided, but just sorted from the original. Would like to see if that is possible. Thanks and Thumbs up!

[marina]

Hi, great video. I have a large data set and as I am dealing with samples. However not all samples have the same data (I will put an example below). Could I create a row that has the same name but the result as null?

Sample 1: a1, 3. A3, 8.
Sample 2: a1, 3. S2, 7

What I would like to see;
Sample 1: a1, 3. A3, 8. S2, 0
Sample 2; a1, 3. A3, 0. S2, 7
Thank you for your help

[gregbaker]

Love this! What happens if some of the addresses are 3 lines while others occasionally take up 4 lines?

[excelemployeeleavetracker]

Outstanding Video!! I am amazed on your knowledge that I found on your channel!. I Love Power Query! Thank-you!