Excel Magic Trick 1315: Create A Unique Sorted List with Power Query

preview_player
Показать описание
Download Excel Files:

See how to create a unique sorted list with Power Query.
Рекомендации по теме
Комментарии
Автор

Thanks Mike for presenting this simple method (simple with PQ). I think this is an easiest way to get this list.
If you want to get the result in a single cell, you can add two steps to the query.

#"Changed Type2" = Rows", {{"Account", type text}}),
OneCellResult = Text.Combine(#"Changed Type2"[Account], ", ")
in
OneCellResult

Unfortunatelly, the last step (OneCellResult) have to be written by hand.

BillSzysz
Автор

My new job changed out the old computer for a new one that has excel 2016. I'm literally the "crying Jordan" trying to figure out how to use this new excel. Lol. I will need to catch up on your tutorials for 2016 excel stuff. You are awesome!

pctLowBattery
Автор

Thank you Mike for this awesome video.

himanshudalai
Автор

I just gave this a try with one of my dashboards and it worked great. I used to use an array formula but this is easier for other people to understand and modify. In order to solve the problem of refreshing 5 tables and their queries, I just created a simple macro and a button people will have to click to run it. Btw, is there a way to group data in Power Query just like you would in a Pivot table (i.e. <10, 10, 20, >20) Thank you so much! You're the best!

ennykraft
Автор

Wow Mike, that was so easy! The PQ solution is impressive.
Can PQ do it with case sensitive text? Or does the Remove Duplicates function not distinguish case sensitivity?

kjvstats
Автор

Thanks Mike That's much more easier than a formula :-)
By the way, I didn't find the video to EMT1315 Debits minus Credits Total For Each Accounting Classification (2 Examples)?

mohamedchakroun
Автор

Hi Mike, thanks for the video! I was wondering if there was a way to keep the “remove duplicates” functionality as a constant filter or ‘criterion’ if you will, without having to click the button in power query?
This way, feeding new data into the original table would still result in a unique list by merely clicking the refresh button.

Thanks!

sjvg_
Автор

Any suggestions for utilizing Excel for client data (Name. address, etc)? Places such as Staples requires a PDF or JPG format in order to use their Direct Mail Service. By using a column per item( name, address, city, zip), then I can do a Sort and select my target for a particular promotion. If you think a different program would better sit me needs please tell me, Thanks for your time and help! Laura

laurabarber
Автор

Sorting is buggy and unreliable in PQ. It works fine in the editor but often is not applied to the external table

stephanweaver
Автор

Does turning "on" then "off" the filter button still invoke Power Query, instead of making the data into an Excel table?

Sal_A