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!
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!
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. 👍👍👍👍
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.
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.
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
@@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.
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 🥲🥲
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 :)
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(_)) )
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?
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
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
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"
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.
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.
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
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.
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.
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
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!
Thanks Steve, great that you and everyone are taking part and watching the solutions
i was just looking for that solution for over a week!!!! thanks!!! great tutorial
Glad to help 🙂
Thank you Wyn 😊
Learning so much from you, as always!
The second approach is my favorite one 🤯
Thanks Alejandra, yep it's more elegant and easier to implement in more complex scenarios
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!
You're welcome Tom, thanks
This is such a massive time saver! I am now interested in the M code!!! Thank you and keep up the good job.
Cheers, getting into M opens a whole new world of options
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. 👍👍👍👍
Great, we all keep learning. Thanks for leaving a great comment
Thank you!! Congrats for the 10K, and for the new logo. That's an excellent channel!
Thank you Iván
As always I learn something new from you videos, and I thank you!
Cheers Rick, glad I'm helping out
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.
Yep, cheers Matthew. For this demo it would have been good to turn it off
Thank you Wyan. I learned a lot from your Vedios. 2nd method is more attractive and my favourite. Thank you sharing your valuable knowledge.
You’re welcome Priya. Thanks for taking the time to leave a kind comment
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
Thanks for confirming Grainne
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.
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
@@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.
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 🥲🥲
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
This helped a lot. Saved my time! Thanks.
Great! Thanks for taking the time to leave a kind comment
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!
😀 I’ll be demoing a clever 3rd option that several people submitted in a week or two. All good learning for all of us
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 :)
Thanks for the suggestion Ravi
Awesome Wyn! Great demo. Thanks for the two examples. Learned a lot! Thumbs up!!
Thanks Wayne.
Great video. I liked your second approach using the nQuail name and replace the list hardecoded by the M code.
Thanks for watching and leaving a comment
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(_)) )
Thank you
BRILLIANT STUFF!!! Just what I needed...
You’re welcome. Thanks for taking the time to to let me know it was useful
Exactly what I needed! Thanks!
Glad to help Darlene
Marvelous ideas 💡💡
Thanks Sameh
Thanks so much, that was just what I was looking for!
Great, thanks for taking the time to leave a kind comment
This was extremely helpful!
I appreciate you taking the time to let me know you found it useful
Wow, very nice tricks. Thank you very much for sharing.
You’re welcome Bhavik
Great trick! It is amazing and interesting to follow you along!
Glad you’re finding it interesting 😀
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?
Greatly appreciated. Just let people know about the channel and that you found it useful! Thanks 🙏🏻
Thanks for the challenge, it was fun and educational, as Power BI should be 🙂
Cheers Ivan, thanks for taking part
Really helpful. Thanks.
You’re welcome. I appreciate you taking the time to let me know you found it useful
Thank you so much. You're a life saver.
You're welcome :)
Amazing, i haveto learn a lot to have your level
Every year I say the same thing about future me 😀
Amazing, great, i liked. Thank you!
Thanks Luciano
Oooh I didn't realised you can use a number on the second argument of the splitcolumn function. What a game changer! 😁
Glad it helps
First approach is cool for me
Yeah I like it too, thanks Kayode
Thank you for saving me timess 🙏🙏
You’re welcome thanks for taking the time to leave a kind comment
Thank you soo much for this video
You’re welcome 😀
Thank you so much for this tutorial.
I would like to learn that what is the main diffrence between List and Table?
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
Really nice, thanks.
No worries Zach
Your videos are really nice.👍 I Subscribed you just now🙂
That's great Krishna, thanks
Beautiful!
Thank you
Thank you very much
You’re welcome
Thank you!!
You’re welcome Jennifer
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
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"
my approach is Group then use ''text.combine'' then Split. it should be quicker =)
Feel free to submit a file or code Ryan
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.
Note sure sorry. I can't quite picture how it would be useful in Power BI
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.
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
Fix for : 4:55 (making a list) - try write only writting = [Data][Qualification]
Yep, nice shortcut
Can you please have a short video on splitting columns to rows with aggregated data, like sales amount. Thank you
Can you expand on what the issue is you’re trying to solve?
Example:
What is your email, I can send the sample in the email.@@AccessAnalytic
Thank you
thanks but can we achieve the result as vice versa.
Sure there’s almost always a way to do anything as long as there’s some logical pattern
Great 👍
You're welcome
Great🙏🙏
Thankyou
It's great
Thanks Antony
@@AccessAnalytic thanks to You for sharing all theese interesting videos.
You’re welcome
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.
Maybe unpivot? ua-cam.com/video/ESap6ptV8fI/v-deo.html
How to do it reversly I have data as your required result need to convert it into source data
Right Click the first column and choose Unpivot Other Columns
that great WYN, HOW CAN WE SOLVE THE SAME PROBLEM IN EXCEL
Not sure what you mean, this is in Excel
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.
This video may give you some ideas on that ua-cam.com/video/BZuX5zD8sUA/v-deo.html
🙌🙌🔥🔥🙏🙏
😀
Wait, isn't this rows to columns?
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
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
Thanks for joining in!