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.
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😊
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
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...........😁
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?
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?
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.
Thanks
I'd never thought of merging a query with itself. Very cool.
Yeah often comes in handy that one 😀
Thanks for this...i have a similar problem and will use this technique
Glad to help
Super 👍 Thanks Wyn for this mind-blowing video. Looking forward to more videos like this 🌹
You’re welcome
Wow! mind blowing. I didn't know that. Many Thanks!
Glad to help you learn something new
This is so good! Thanks!
No worries, you're welcome
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😊
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
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.
Sure - send to info@accessanalytic.com.au
( no promises I’ll use it though 😊 )
VBA could apply the range name to worksheet.usedrange object.
Thanks!
No worries
Great video as usually! But that neat trick with type.datatype....damn didnt know it =)) very very useful tricks!!!
Glad you found it helpful 😀
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...........😁
Thanks Naved
Fun, usefull, thank you. By the way, today are you using a dvorak layout?? XD
You’re welcome, Glad it’s useful
very informative
Thanks Naushad
What can I say ? :)) Perfect !! Thank you so much..
You’re welcome Ezel
beautiful
Thanks
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?
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?