Power Query Multi Column Data - Best In Class Challenge

[@thebhaskarjoshi]

It was amazing solution you provided, for the first time saw the real-life use case of “Removing Alternative Rows” and “Merge Queries” to itself. And the filtering data using "Data Type" technique was also new for me.

[@AccessAnalytic]

Thanks

[@zzota]

I'd never thought of merging a query with itself. Very cool.

[@AccessAnalytic]

Yeah often comes in handy that one 😀

[@gospelmoto2833]

Wow! mind blowing. I didn't know that. Many Thanks!

[@AccessAnalytic]

Glad to help you learn something new

[@kebincui]

Super 👍 Thanks Wyn for this mind-blowing video. Looking forward to more videos like this 🌹

[@AccessAnalytic]

You’re welcome

[@zro.tolerance]

Thanks for this...i have a similar problem and will use this technique

[@AccessAnalytic]

Glad to help

[@ohdjrp4]

This is so good! Thanks!

[@AccessAnalytic]

No worries, you're welcome

[@mirrrvelll5164]

Great video as usually! But that neat trick with type.datatype....damn didnt know it =)) very very useful tricks!!!

[@AccessAnalytic]

Glad you found it helpful 😀

[@warrennalty6599]

VBA could apply the range name to worksheet.usedrange object.

[@ezelkarlkl1284]

What can I say ? :)) Perfect !! Thank you so much..

[@AccessAnalytic]

You’re welcome Ezel

[@themolestones]

Many thanks, Wyn, I'm learning a lot from your vids. Is there any chance to send a possible future challenge? Thanks in advance for your time.

[@AccessAnalytic]

Sure - send to [e-mail]
( no promises I’ll use it though 😊 )

[@navedsaiyed9881]

Very informative on how to clean messy data without converting into a table also how to rearrange the data in power query for having refined data.I have a kind similar sheet but little bit more messy will try on it...........😁

[@AccessAnalytic]

Thanks Naved

[@apostolos55goulandris97]

Great Job, Thanks Wyn
One small notice on the 2nd part (no merge or dbl-grouping):
let
Source = Results,
#"Grouped Rows" = Table.Group(Source, {"Date", "Name"}, {{"Avg of Mark", each List.Average([Mark]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Date", Order.Ascending}, {"Avg of Mark", Order.Descending}, {"Name", Order.Ascending}}),
#"Multiplied Column" = Table.TransformColumns(#"Sorted Rows", {{"Avg of Mark", each _ * 1, type number}}),
#"Removed Duplicates" = Table.Distinct(#"Multiplied Column", {"Date"})
in
#"Removed Duplicates"
If we ommit the #"Multiplied Column", then the #"Removed Duplicates" doesn't work as expected: Instead of keeping the 1st items it keeps some random items, thus ignoring the sort. Like if sort is visible but has not affected the data yet. Some work on the data solves this. But this is my humbble explanation, any real insight would be appreciated
Thanks😊

[@AccessAnalytic]

Hi, sorry for the long delay in response, I missed noticing this one.
There is a “bug” in power query where the sort step gets ignored in certain circumstances. The fix is to add an index step immediately after sort or wrap in a Table.buffer

[@7213261]

Thanks!

[@AccessAnalytic]

No worries

[@naushad123]

very informative

[@AccessAnalytic]

Thanks Naushad

[@shrikantbadge3978]

beautiful

[@AccessAnalytic]

Thanks

[@zachg.9208]

Fun, usefull, thank you. By the way, today are you using a dvorak layout?? XD

[@AccessAnalytic]

You’re welcome, Glad it’s useful

[@ibrahimnajjar7351]

Great video, thanks Wyn!
What if I needed to load two queries in the same sheet on top of each other in the same sheet, is there a way to prevent the error of one of them going over the other?

[@AccessAnalytic]

I just tested and it works if both tables have same columns but otherwise it fails. Not something I'd recommend doing if you can avoid it. Are you able to append the tables in Power Query instead? or load side by side?