Thanks for replying to my solution email - and loved seeing similar elements in your solution! A few things I learned from your solution: 1) Adding an index to "fix" the sort rather than Table.Buffer - I'm with you on this, PQ can forget the sort. 2) Extracting a list from the grouped tables in the Data column - nice technique 3) Using nQual as a parameter to Text.SplitByDelimiter I'm loving these challenges - keep them coming!
@AccessAnalytic2 жыл бұрын
Thanks Steve, great that you and everyone are taking part and watching the solutions
@ccentermanager Жыл бұрын
i was just looking for that solution for over a week!!!! thanks!!! great tutorial
@AccessAnalytic Жыл бұрын
Glad to help 🙂
@CasperLind1 Жыл бұрын
Fix for : 4:55 (making a list) - try write only writting = [Data][Qualification]
@AccessAnalytic Жыл бұрын
Yep, nice shortcut
@Notgich Жыл бұрын
This is such a massive time saver! I am now interested in the M code!!! Thank you and keep up the good job.
@AccessAnalytic Жыл бұрын
Cheers, getting into M opens a whole new world of options
@malejandrahorvath2 жыл бұрын
Thank you Wyn 😊 Learning so much from you, as always! The second approach is my favorite one 🤯
@AccessAnalytic2 жыл бұрын
Thanks Alejandra, yep it's more elegant and easier to implement in more complex scenarios
@sedarathnadcd2 жыл бұрын
Thank you Wyan. I learned a lot from your Vedios. 2nd method is more attractive and my favourite. Thank you sharing your valuable knowledge.
@AccessAnalytic2 жыл бұрын
You’re welcome Priya. Thanks for taking the time to leave a kind comment
@khawarmalik52392 жыл бұрын
Second approach is super cool. I knew about the first one having used it on lot of occasions but the second one is amazing. Thanks for sharing his. 👍👍👍👍
@AccessAnalytic2 жыл бұрын
Great, we all keep learning. Thanks for leaving a great comment
@matthewwykle89652 жыл бұрын
Just found your challenges. A lot of fun. You can turn off the automatic changed type steps. Data>Get Data> Query Options>Data Load>Type Detection. You can do it for all workbooks (Global) or individual workbooks as needed.
@AccessAnalytic2 жыл бұрын
Yep, cheers Matthew. For this demo it would have been good to turn it off
@martyc56742 жыл бұрын
Hi Wyn- Great Vid, ** Edit the below is incorrect, I was mixing up with table.split function. however I learned something the other day about the splittextbydelimiter function, the last argument is optional. All you need to do is delete the list and your done, no need to do nQuals or anything fancy. Your formula at 11:19 is actually all that’s needed. Just leave the last part blank and it’s totally dynamic.
@AccessAnalytic2 жыл бұрын
Thanks Martin, Sadly that doesn’t actually work properly, it just splits the first record dynamically by the number of delimiters it has. Any subsequent records with more delimiters don’t get split
@martyc56742 жыл бұрын
@@AccessAnalyticI stand corrected , it was the table.split function where this works without passing something to the last argument(and then becomes dynamic). I jumped the gun, and expected similar behaviour with this function. Let me see if I can leverage the table.split function to form a solution to this problem.
@martyc56742 жыл бұрын
So I tried this again today, and turns out I’m completely wrong. I just got lucky(read fooled!!) with the small bit of test data I was using, as it happened to have the most delimiters in the first record. So to my dismay the behavior Wyn highlighted is also true in the table.split function 🥲🥲
@AccessAnalytic2 жыл бұрын
Thanks for letting folks know, I think I’ve been caught by this is the past, I may even have a video where I used it
@tomr99692 жыл бұрын
Nice! I was on the right path (grouping) and lost track of time. Having said that, I learn by doing - I am going to do both steps tonight! Thanks for sharing!
@AccessAnalytic2 жыл бұрын
You're welcome Tom, thanks
@johnhackwood15682 жыл бұрын
Another gem of a video thank you Wyn. If you had just shown the first and left it there that would have been good enough but the 2nd approach, voila!
@AccessAnalytic2 жыл бұрын
😀 I’ll be demoing a clever 3rd option that several people submitted in a week or two. All good learning for all of us
@RaviGupta-mo1nf2 жыл бұрын
Interesting problem to solve & I loved the solutions you offered. To make it more interesting ( or for use at work), add year in which the qualification was acquired. Employee can acquire more than 1 qualification in any year :)
@AccessAnalytic2 жыл бұрын
Thanks for the suggestion Ravi
@GrainneDuggan_Excel2 жыл бұрын
PQ forgot the sort order in my solution to this challenge. I had to sort again at a later step. Definitely still a thing. Thanks for the solution,Wyn
@AccessAnalytic2 жыл бұрын
Thanks for confirming Grainne
@IvanCortinas_ES2 жыл бұрын
Thank you!! Congrats for the 10K, and for the new logo. That's an excellent channel!
@AccessAnalytic2 жыл бұрын
Thank you Iván
@rick_.2 жыл бұрын
As always I learn something new from you videos, and I thank you!
@AccessAnalytic2 жыл бұрын
Cheers Rick, glad I'm helping out
@jazzista19672 жыл бұрын
Great video. I liked your second approach using the nQuail name and replace the list hardecoded by the M code.
@AccessAnalytic2 жыл бұрын
Thanks for watching and leaving a comment
@ivandumalovskijanjusic11592 жыл бұрын
Thanks for the challenge, it was fun and educational, as Power BI should be 🙂
@AccessAnalytic2 жыл бұрын
Cheers Ivan, thanks for taking part
@williamarthur4801 Жыл бұрын
Really enjoyed that, loved the unpivot, pivot, I always get confused with that and it's a bit trial and error, I also forget about using the aggregations in grouping and tend to type everything in the editor , when it came to a dynamic column count I used; List.Max ( List.Transform( Custom2 [List of Lists], (_)=>List.Count(_)) )
@AccessAnalytic Жыл бұрын
Thank you
@leebecker82552 жыл бұрын
Oooh I didn't realised you can use a number on the second argument of the splitcolumn function. What a game changer! 😁
@AccessAnalytic2 жыл бұрын
Glad it helps
@wayneedmondson10652 жыл бұрын
Awesome Wyn! Great demo. Thanks for the two examples. Learned a lot! Thumbs up!!
@AccessAnalytic2 жыл бұрын
Thanks Wayne.
@shahidabano70942 жыл бұрын
This helped a lot. Saved my time! Thanks.
@AccessAnalytic Жыл бұрын
Great! Thanks for taking the time to leave a kind comment
@pytkabarabasza2 жыл бұрын
I would like to thank you for this video. I was struggling with this for a quite long time. Is there any way to support you? Or at least give you coffee?
@AccessAnalytic2 жыл бұрын
Greatly appreciated. Just let people know about the channel and that you found it useful! Thanks 🙏🏻
@davidb751110 ай бұрын
BRILLIANT STUFF!!! Just what I needed...
@AccessAnalytic10 ай бұрын
You’re welcome. Thanks for taking the time to to let me know it was useful
@mirrrvelll51642 жыл бұрын
Great trick! It is amazing and interesting to follow you along!
@AccessAnalytic2 жыл бұрын
Glad you’re finding it interesting 😀
@mirziyodm2 жыл бұрын
Thanks so much, that was just what I was looking for!
@AccessAnalytic2 жыл бұрын
Great, thanks for taking the time to leave a kind comment
@darlenesoukhaphonh8437 Жыл бұрын
Exactly what I needed! Thanks!
@AccessAnalytic Жыл бұрын
Glad to help Darlene
@Bhavik_Khatri2 жыл бұрын
Wow, very nice tricks. Thank you very much for sharing.
@AccessAnalytic2 жыл бұрын
You’re welcome Bhavik
@kayodeogundiya71942 жыл бұрын
First approach is cool for me
@AccessAnalytic2 жыл бұрын
Yeah I like it too, thanks Kayode
@SamehRSameh2 жыл бұрын
Marvelous ideas 💡💡
@AccessAnalytic2 жыл бұрын
Thanks Sameh
@miracleugwu56032 жыл бұрын
Thank you so much. You're a life saver.
@AccessAnalytic2 жыл бұрын
You're welcome :)
@MrJoking4fun10 ай бұрын
This was extremely helpful!
@AccessAnalytic10 ай бұрын
I appreciate you taking the time to let me know you found it useful
@TheLaopi Жыл бұрын
Amazing, i haveto learn a lot to have your level
@AccessAnalytic Жыл бұрын
Every year I say the same thing about future me 😀
@Luciano_mp2 жыл бұрын
Amazing, great, i liked. Thank you!
@AccessAnalytic2 жыл бұрын
Thanks Luciano
@teoxengineer2 жыл бұрын
Thank you so much for this tutorial. I would like to learn that what is the main diffrence between List and Table?
@AccessAnalytic2 жыл бұрын
You're welcome. A list is a collection of items {A,B,C} that looks like a column. A Table consists of rows and columns (records). A single column Table looks very much like a list It's multiple related columns that really separate a table from a list
@pranipradani9853 Жыл бұрын
Thank you for saving me timess 🙏🙏
@AccessAnalytic Жыл бұрын
You’re welcome thanks for taking the time to leave a kind comment
@pkup1011 ай бұрын
Really helpful. Thanks.
@AccessAnalytic11 ай бұрын
You’re welcome. I appreciate you taking the time to let me know you found it useful
@monserratabarca55632 жыл бұрын
Beautiful!
@AccessAnalytic2 жыл бұрын
Thank you
@ryanyou15832 жыл бұрын
my approach is Group then use ''text.combine'' then Split. it should be quicker =)
@AccessAnalytic2 жыл бұрын
Feel free to submit a file or code Ryan
@mahnoorafridi98722 жыл бұрын
Thank you soo much for this video
@AccessAnalytic2 жыл бұрын
You’re welcome 😀
@ravipaul1657 Жыл бұрын
thanks but can we achieve the result as vice versa.
@AccessAnalytic Жыл бұрын
Sure there’s almost always a way to do anything as long as there’s some logical pattern
@chrisharmozi45272 жыл бұрын
Your videos are really nice.👍 I Subscribed you just now🙂
@AccessAnalytic2 жыл бұрын
That's great Krishna, thanks
@zachg.92082 жыл бұрын
Really nice, thanks.
@AccessAnalytic2 жыл бұрын
No worries Zach
@pthapa5510 ай бұрын
Can you please have a short video on splitting columns to rows with aggregated data, like sales amount. Thank you
@AccessAnalytic10 ай бұрын
Can you expand on what the issue is you’re trying to solve?
@pthapa5510 ай бұрын
Example: What is your email, I can send the sample in the email.@@AccessAnalytic Thank you
@vinmeg696 ай бұрын
Hello Wyn thanks, Leads me to a question: How to get the qualifications in a sort order with columns per qualification? A Accountant Column, Plumber Column etc.
@AccessAnalytic6 ай бұрын
A groupby and then add a grouped index. Paste this into the advanced editor to see how it works let // Summary: This query processes a table from Manually entered table (saved as Json, removes unnecessary columns, groups rows by 'Qual', adds an index column, pivots the table, and cleans up the final output. // Developed by: access analytic // Source: Load and decompress JSON data into a table Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpJTUzOUIrViVZyQuE5A3mOycklYI4LkBOQU5oL5rgCOcH5OSmZqUVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Qual = _t]), // GroupedRows: Group the table by 'Qual' column GroupedRows = Table.Group(Source, {"Qual"}, {{"Data", each _, type table [Name = nullable text, Qual = nullable text]}}), // AddedIndex: Add an index column to each grouped table AddedIndex = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)), // KeptOnlyCustom: Select only the 'Custom' column KeptOnlyCustom = Table.SelectColumns(AddedIndex, {"Custom"}), // ExpandedCustom: Expand the 'Custom' column into 'Name', 'Qual', and 'Index' columns ExpandedCustom = Table.ExpandTableColumn(KeptOnlyCustom, "Custom", {"Name", "Qual", "Index"}, {"Name", "Qual", "Index"}), // PivotedQual: Pivot the table on 'Qual' column, transforming 'Name' values into respective 'Qual' columns PivotedQual = Table.Pivot(ExpandedCustom, List.Distinct(ExpandedCustom[Qual]), "Qual", "Name"), // RemovedIndex: Remove the 'Index' column from the pivoted table RemovedIndex = Table.RemoveColumns(PivotedQual, {"Index"}) in RemovedIndex
@jenniferrussell16742 жыл бұрын
Thank you!!
@AccessAnalytic2 жыл бұрын
You’re welcome Jennifer
@MegaNadine2012 Жыл бұрын
Thank you very much
@AccessAnalytic Жыл бұрын
You’re welcome
@hermonkidane15032 жыл бұрын
I want the same thing but the reverse, meaning I want to map multiple rows to a single column, if there is name and say salary of each month in a single row, I would like to have name then each month salary vertically in multiple rows, then the name of the next employee salary of each month vertically and so on...Can any one give me a hint of how to do that.
I have been trying to get this to work on a .pbix I have. It has been running for over 30 minutes and never finishes. Any thoughts? It looks like an amazing tool.
@AccessAnalytic5 ай бұрын
Note sure sorry. I can't quite picture how it would be useful in Power BI
@amanijeanpierre79112 жыл бұрын
How do we extract get data from web using Power Query containing more data in a table than it appears on the websites. Such websites show tables in pages so you need to click page 2 for you to see the next content.
@AccessAnalytic2 жыл бұрын
This video may give you some ideas on that kzbin.info/www/bejne/eIvYiWiweZ2mi6M
@sandeepnetha26165 ай бұрын
How to do it reversly I have data as your required result need to convert it into source data
@AccessAnalytic5 ай бұрын
Right Click the first column and choose Unpivot Other Columns
@sharadgupta20072 жыл бұрын
Great🙏🙏
@AccessAnalytic2 жыл бұрын
Thankyou
@antonysovy58872 жыл бұрын
It's great
@AccessAnalytic2 жыл бұрын
Thanks Antony
@antonysovy58872 жыл бұрын
@@AccessAnalytic thanks to You for sharing all theese interesting videos.
@AccessAnalytic2 жыл бұрын
You’re welcome
@ajitkanabar843 ай бұрын
Hi Wyn, Thanks for the video. I really appreciate your efforts. However though, I want my result to be the table that you initially started with. I have the data as stated below. Col.A - Item Col.B - Qty 1 Col.C - Price 1 Col.D - Qty 2 Col.E - Price 2 and so on... till Qty 10 and Price 10 and would like to transpose/unpivot/pivot to the end result that i am trying to achieve as mentioned below Col.A - each Item repeated 10 times Col.B - Qty (with all the different Qty for each item to be repeated) Col.C - Price (with all their relevant prices for those quantities and items) Because for each item, will have different price points based on the quantities purchased. Hope that makes sense. Would you share some inputs on how this would be realistically possible? Thanks
@AccessAnalytic3 ай бұрын
Something like this let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Price or Quantity", "Price Point"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Price or Quantity]), "Price or Quantity", "Value") in #"Pivoted Column"
@atiqkhan96372 жыл бұрын
that great WYN, HOW CAN WE SOLVE THE SAME PROBLEM IN EXCEL
@AccessAnalytic2 жыл бұрын
Not sure what you mean, this is in Excel
@FRANKWHITE19962 жыл бұрын
🙌🙌🔥🔥🙏🙏
@AccessAnalytic2 жыл бұрын
😀
@crumbdav Жыл бұрын
Wait, isn't this rows to columns?
@AccessAnalytic Жыл бұрын
It;s a bit more involved than that, but you could like do some combination of UNQIUE and then FILTER and TOROWS to get a similar result
@ExcelWizard2 жыл бұрын
Great challenge My approach by Table.FromRows and Table.Combine let Source = Excel.CurrentWorkbook(){[Name="tblQualifications"]}[Content], Sorted = Table.Buffer(Table.Sort(Source,{{"Name", Order.Ascending}, {"Qualification", Order.Ascending}})), Grouped = Table.Group(Sorted, {"Name"}, {{"T", each Table.FromRows({{[Name]{0}}&[Qualification]} ,{"Name"}&List.Transform({1..Table.RowCount(_) }, each "Qualification."&Text.From(_))) }}), Combine = Table.Combine(Grouped[T]) in Combine