Power Query Convert Columns to Rows for groups and expand columns dynamically in 2 ways

  Рет қаралды 48,721

Access Analytic

Access Analytic

Күн бұрын

Пікірлер
@Mister_Bates
@Mister_Bates 2 жыл бұрын
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!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks Steve, great that you and everyone are taking part and watching the solutions
@ccentermanager
@ccentermanager Жыл бұрын
i was just looking for that solution for over a week!!!! thanks!!! great tutorial
@AccessAnalytic
@AccessAnalytic Жыл бұрын
Glad to help 🙂
@CasperLind1
@CasperLind1 Жыл бұрын
Fix for : 4:55 (making a list) - try write only writting = [Data][Qualification]
@AccessAnalytic
@AccessAnalytic Жыл бұрын
Yep, nice shortcut
@Notgich
@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
@AccessAnalytic Жыл бұрын
Cheers, getting into M opens a whole new world of options
@malejandrahorvath
@malejandrahorvath 2 жыл бұрын
Thank you Wyn 😊 Learning so much from you, as always! The second approach is my favorite one 🤯
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks Alejandra, yep it's more elegant and easier to implement in more complex scenarios
@sedarathnadcd
@sedarathnadcd 2 жыл бұрын
Thank you Wyan. I learned a lot from your Vedios. 2nd method is more attractive and my favourite. Thank you sharing your valuable knowledge.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
You’re welcome Priya. Thanks for taking the time to leave a kind comment
@khawarmalik5239
@khawarmalik5239 2 жыл бұрын
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. 👍👍👍👍
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Great, we all keep learning. Thanks for leaving a great comment
@matthewwykle8965
@matthewwykle8965 2 жыл бұрын
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.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Yep, cheers Matthew. For this demo it would have been good to turn it off
@martyc5674
@martyc5674 2 жыл бұрын
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.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
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
@martyc5674
@martyc5674 2 жыл бұрын
@@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.
@martyc5674
@martyc5674 2 жыл бұрын
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 🥲🥲
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
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
@tomr9969
@tomr9969 2 жыл бұрын
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!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
You're welcome Tom, thanks
@johnhackwood1568
@johnhackwood1568 2 жыл бұрын
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!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
😀 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-mo1nf
@RaviGupta-mo1nf 2 жыл бұрын
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 :)
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks for the suggestion Ravi
@GrainneDuggan_Excel
@GrainneDuggan_Excel 2 жыл бұрын
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
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks for confirming Grainne
@IvanCortinas_ES
@IvanCortinas_ES 2 жыл бұрын
Thank you!! Congrats for the 10K, and for the new logo. That's an excellent channel!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thank you Iván
@rick_.
@rick_. 2 жыл бұрын
As always I learn something new from you videos, and I thank you!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Cheers Rick, glad I'm helping out
@jazzista1967
@jazzista1967 2 жыл бұрын
Great video. I liked your second approach using the nQuail name and replace the list hardecoded by the M code.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks for watching and leaving a comment
@ivandumalovskijanjusic1159
@ivandumalovskijanjusic1159 2 жыл бұрын
Thanks for the challenge, it was fun and educational, as Power BI should be 🙂
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Cheers Ivan, thanks for taking part
@williamarthur4801
@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
@AccessAnalytic Жыл бұрын
Thank you
@leebecker8255
@leebecker8255 2 жыл бұрын
Oooh I didn't realised you can use a number on the second argument of the splitcolumn function. What a game changer! 😁
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Glad it helps
@wayneedmondson1065
@wayneedmondson1065 2 жыл бұрын
Awesome Wyn! Great demo. Thanks for the two examples. Learned a lot! Thumbs up!!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks Wayne.
@shahidabano7094
@shahidabano7094 2 жыл бұрын
This helped a lot. Saved my time! Thanks.
@AccessAnalytic
@AccessAnalytic Жыл бұрын
Great! Thanks for taking the time to leave a kind comment
@pytkabarabasza
@pytkabarabasza 2 жыл бұрын
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?
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Greatly appreciated. Just let people know about the channel and that you found it useful! Thanks 🙏🏻
@davidb7511
@davidb7511 10 ай бұрын
BRILLIANT STUFF!!! Just what I needed...
@AccessAnalytic
@AccessAnalytic 10 ай бұрын
You’re welcome. Thanks for taking the time to to let me know it was useful
@mirrrvelll5164
@mirrrvelll5164 2 жыл бұрын
Great trick! It is amazing and interesting to follow you along!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Glad you’re finding it interesting 😀
@mirziyodm
@mirziyodm 2 жыл бұрын
Thanks so much, that was just what I was looking for!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Great, thanks for taking the time to leave a kind comment
@darlenesoukhaphonh8437
@darlenesoukhaphonh8437 Жыл бұрын
Exactly what I needed! Thanks!
@AccessAnalytic
@AccessAnalytic Жыл бұрын
Glad to help Darlene
@Bhavik_Khatri
@Bhavik_Khatri 2 жыл бұрын
Wow, very nice tricks. Thank you very much for sharing.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
You’re welcome Bhavik
@kayodeogundiya7194
@kayodeogundiya7194 2 жыл бұрын
First approach is cool for me
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Yeah I like it too, thanks Kayode
@SamehRSameh
@SamehRSameh 2 жыл бұрын
Marvelous ideas 💡💡
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks Sameh
@miracleugwu5603
@miracleugwu5603 2 жыл бұрын
Thank you so much. You're a life saver.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
You're welcome :)
@MrJoking4fun
@MrJoking4fun 10 ай бұрын
This was extremely helpful!
@AccessAnalytic
@AccessAnalytic 10 ай бұрын
I appreciate you taking the time to let me know you found it useful
@TheLaopi
@TheLaopi Жыл бұрын
Amazing, i haveto learn a lot to have your level
@AccessAnalytic
@AccessAnalytic Жыл бұрын
Every year I say the same thing about future me 😀
@Luciano_mp
@Luciano_mp 2 жыл бұрын
Amazing, great, i liked. Thank you!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks Luciano
@teoxengineer
@teoxengineer 2 жыл бұрын
Thank you so much for this tutorial. I would like to learn that what is the main diffrence between List and Table?
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
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
@pranipradani9853 Жыл бұрын
Thank you for saving me timess 🙏🙏
@AccessAnalytic
@AccessAnalytic Жыл бұрын
You’re welcome thanks for taking the time to leave a kind comment
@pkup10
@pkup10 11 ай бұрын
Really helpful. Thanks.
@AccessAnalytic
@AccessAnalytic 11 ай бұрын
You’re welcome. I appreciate you taking the time to let me know you found it useful
@monserratabarca5563
@monserratabarca5563 2 жыл бұрын
Beautiful!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thank you
@ryanyou1583
@ryanyou1583 2 жыл бұрын
my approach is Group then use ''text.combine'' then Split. it should be quicker =)
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Feel free to submit a file or code Ryan
@mahnoorafridi9872
@mahnoorafridi9872 2 жыл бұрын
Thank you soo much for this video
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
You’re welcome 😀
@ravipaul1657
@ravipaul1657 Жыл бұрын
thanks but can we achieve the result as vice versa.
@AccessAnalytic
@AccessAnalytic Жыл бұрын
Sure there’s almost always a way to do anything as long as there’s some logical pattern
@chrisharmozi4527
@chrisharmozi4527 2 жыл бұрын
Your videos are really nice.👍 I Subscribed you just now🙂
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
That's great Krishna, thanks
@zachg.9208
@zachg.9208 2 жыл бұрын
Really nice, thanks.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
No worries Zach
@pthapa55
@pthapa55 10 ай бұрын
Can you please have a short video on splitting columns to rows with aggregated data, like sales amount. Thank you
@AccessAnalytic
@AccessAnalytic 10 ай бұрын
Can you expand on what the issue is you’re trying to solve?
@pthapa55
@pthapa55 10 ай бұрын
Example: What is your email, I can send the sample in the email.@@AccessAnalytic Thank you
@vinmeg69
@vinmeg69 6 ай бұрын
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.
@AccessAnalytic
@AccessAnalytic 6 ай бұрын
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
@jenniferrussell1674
@jenniferrussell1674 2 жыл бұрын
Thank you!!
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
You’re welcome Jennifer
@MegaNadine2012
@MegaNadine2012 Жыл бұрын
Thank you very much
@AccessAnalytic
@AccessAnalytic Жыл бұрын
You’re welcome
@hermonkidane1503
@hermonkidane1503 2 жыл бұрын
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.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Maybe unpivot? kzbin.info/www/bejne/e4TEoWmmqbtrnKs
@azmf80
@azmf80 Жыл бұрын
Great 👍
@AccessAnalytic
@AccessAnalytic Жыл бұрын
You're welcome
@SandraRumle
@SandraRumle 5 ай бұрын
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.
@AccessAnalytic
@AccessAnalytic 5 ай бұрын
Note sure sorry. I can't quite picture how it would be useful in Power BI
@amanijeanpierre7911
@amanijeanpierre7911 2 жыл бұрын
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.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
This video may give you some ideas on that kzbin.info/www/bejne/eIvYiWiweZ2mi6M
@sandeepnetha2616
@sandeepnetha2616 5 ай бұрын
How to do it reversly I have data as your required result need to convert it into source data
@AccessAnalytic
@AccessAnalytic 5 ай бұрын
Right Click the first column and choose Unpivot Other Columns
@sharadgupta2007
@sharadgupta2007 2 жыл бұрын
Great🙏🙏
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thankyou
@antonysovy5887
@antonysovy5887 2 жыл бұрын
It's great
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks Antony
@antonysovy5887
@antonysovy5887 2 жыл бұрын
@@AccessAnalytic thanks to You for sharing all theese interesting videos.
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
You’re welcome
@ajitkanabar84
@ajitkanabar84 3 ай бұрын
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
@AccessAnalytic
@AccessAnalytic 3 ай бұрын
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"
@atiqkhan9637
@atiqkhan9637 2 жыл бұрын
that great WYN, HOW CAN WE SOLVE THE SAME PROBLEM IN EXCEL
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Not sure what you mean, this is in Excel
@FRANKWHITE1996
@FRANKWHITE1996 2 жыл бұрын
🙌🙌🔥🔥🙏🙏
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
😀
@crumbdav
@crumbdav Жыл бұрын
Wait, isn't this rows to columns?
@AccessAnalytic
@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
@ExcelWizard
@ExcelWizard 2 жыл бұрын
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
@AccessAnalytic
@AccessAnalytic 2 жыл бұрын
Thanks for joining in!
Power Query Multi Column Data  - Best In Class Challenge
17:03
Access Analytic
Рет қаралды 13 М.
Вопрос Ребром - Джиган
43:52
Gazgolder
Рет қаралды 3,8 МЛН
Convert Multiple Column Groups to Rows in Power Query
17:18
Power Query - Avoid "Helper Queries" (+10 Cool Tricks)
18:40
Power Query Unpivot - fix 4 common data layouts (incl. workbook)
19:24
MyOnlineTrainingHub
Рет қаралды 235 М.
Expand Multiple Columns to Rows in Power Query - Solution
21:40
7 Database Design Mistakes to Avoid (With Solutions)
11:29
Database Star
Рет қаралды 94 М.
10 awesome Power Query tricks you NEED to know! | Excel Off The Grid
15:44
Excel Off The Grid
Рет қаралды 55 М.
The Fastest Replace Values method using a list in Power Query
32:18
Access Analytic
Рет қаралды 5 М.
Power Query Pivot and adding an Index column to a Grouped Field
6:27
Access Analytic
Рет қаралды 10 М.