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

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

@AccessAnalytic

Жыл бұрын

Yeah often comes in handy that one 😀

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

@AccessAnalytic

7 ай бұрын

Glad to help

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

@AccessAnalytic

8 ай бұрын

Glad to help you learn something new

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

@AccessAnalytic

6 ай бұрын

You’re welcome

Thanks!

@AccessAnalytic

Жыл бұрын

No worries

This is so good! Thanks!

@AccessAnalytic

Жыл бұрын

No worries, you're welcome

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 😀

beautiful

@AccessAnalytic

10 ай бұрын

Thanks

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

@AccessAnalytic

Жыл бұрын

You’re welcome Ezel

very informative

@AccessAnalytic

Жыл бұрын

Thanks Naushad

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

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 info@accessanalytic.com.au ( no promises I’ll use it though 😊 )

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

@AccessAnalytic

Жыл бұрын

You’re welcome, Glad it’s useful

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?

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