Great video Wyn, learned some new things, especially remove alternate rows. Also copy the index code , nice trick! I though I was the only one who could not remember these code...😀
I really enjoyed the use of two index columns and the subsequent merge. I used one index column and then a Modulo column divided by two to achieve the same thing, but your method is more elegant
Two other ways are If you use two index columns both the same rather than merging you could do math operations divide integer by 2 to produce record IDs both name and percentage for each record would be the same number and modulo by 2 to produce 1-0 pattern (column IDs) those can be pivoted. You can also use list.zip and feed it a dynamic list of record values using list select on column names and text contains as one contains "name" and one contains "%", list zip will zip two lists in order forming a list of lists for each line so when expanded to rows you have a list of the column pairs that need extracting and then extract values and split by the delimiter you chose.
@@siread7223 yes the real life scenario was around 5,000 rows of data and 10 projects and had to be made easily repeatable as the data was going to change a few times. Copy Pasting is always a last resort for me these days unless I'm 101% sure it's a completely one off exercise that will be quicker to do manually (and even then I'd consider Dynamic Array formulas) and only then do it manually.
This is how I love to learn stuff! Looking at the best doing it!! Worked for dax, and I think it's working for powerquery
Cheers Bernat, we all benefit from sharing with each other and we never stop learning
classroom speak theory end practice utility speak english 🏛🌍🌎🌏
classroom motivation presentasi symbol year 2022 / 2023 quality utility commonication software 2022 2023 🏛🌍🌎🌏🏪🏫🏬🏭🏯🏰💒🏩🏨🏧🏦🏥🏤🏣
That's brilliant. Merging a table with itself. Something I never would have thought of.
Yeah that’s a mind bender Philip ! Thanks for letting me know you liked it
Grouping with all rows and count rows - nice one.
Cheers Grainne
Just amazing .In the era of RUclips no one can remain unskilled because of people like you.
Thanks for the kind comment Kamran
Great video Wyn, learned some new things, especially remove alternate rows. Also copy the index code , nice trick! I though I was the only one who could not remember these code...😀
Cheers Bart. The buttons regularly save me 😆
Followed blindly along loved it, kinda lost, but learning
Stick with it, learning Power Query is brilliant investment of time that will pay huge dividends in the future
I really enjoyed the use of two index columns and the subsequent merge. I used one index column and then a Modulo column divided by two to achieve the same thing, but your method is more elegant
Cheers
Very efficient! Nice exercise with a great many tricks!
Thank you.
Thank you Geert. It’s good to know folks find this stuff useful
Awesome Wyn! A great Power Query workout. Thanks for the tips and techniques to solve this challenge! Thumbs up!!
Cheers Wayne
Best PQ video seen today 😎
Cheers, I appreciate you leaving a kind comment
Super, Wyn. This is amazing.
Thanks very much :)
I've been trying to find a way to deal with a similar problem I have, so I'm going to give this is a go. Your videos are so clear, thank you.
Hope it helps Mark
Two other ways are If you use two index columns both the same rather than merging you could do math operations divide integer by 2 to produce record IDs both name and percentage for each record would be the same number and modulo by 2 to produce 1-0 pattern (column IDs) those can be pivoted.
You can also use list.zip and feed it a dynamic list of record values using list select on column names and text contains as one contains "name" and one contains "%", list zip will zip two lists in order forming a list of lists for each line so when expanded to rows you have a list of the column pairs that need extracting and then extract values and split by the delimiter you chose.
I'd enjoy seeing your submitted solution Glyn, feel free to submit an example file to info@accessanalytic.com.au
Great video, well explained and easy to follow. Thanks Wyn!
Thanks Bunmi
Thanks for sharing! 👍
You're welcome
Great 👍 Many logics unlocked 👌👌
Wow, great handle Power Query, you are a crack Wyn.
Thanks, I’ll take that as a compliment Jose 😀
VERY VERY useful! Thanks a lot!
Simply 👍
Thank you
Brilliant
Thanks Carlos
@@AccessAnalytic Thanks to you for sharing such a useful approach, to be applied in other use cases.
This is great, thanks
No worries Antony, thanks for letting me know
Sir you're Awesome 👌 👏 👍 😍
Thanks Michael ☺️
Super tutorial sir
I need your help in some how relatable allocation process. could you help me ?
For free help with Excel issues I'd recommend posting your scenario to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat
Ludicrous- its a small table - why not just copy and paste into that columnar format table rather than going to all that pq effort?
Not a fair-minded comment tbf. Appreciate its the principle for larger datasets. I have learnt a great deal from wyn's pq tutorials. Cheers
@@siread7223 yes the real life scenario was around 5,000 rows of data and 10 projects and had to be made easily repeatable as the data was going to change a few times. Copy Pasting is always a last resort for me these days unless I'm 101% sure it's a completely one off exercise that will be quicker to do manually (and even then I'd consider Dynamic Array formulas) and only then do it manually.
Question please, why add "+64" in the formula - minute 09:56
In Power Query the letter A is 65, letter B is 66 etc
@@AccessAnalytic Thank you so much!