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

Thanks Steve, great that you and everyone are taking part and watching the solutions

Glad to help 🙂

Thanks Alejandra, yep it's more elegant and easier to implement in more complex scenarios

Cheers Rick, glad I'm helping out

Thank you Iván

Thanks Wayne.

I appreciate you taking the time to let me know you found it useful

Great, we all keep learning. Thanks for leaving a great comment

Cheers, getting into M opens a whole new world of options

Glad you’re finding it interesting 😀

Thanks for watching and leaving a comment

You’re welcome Priya. Thanks for taking the time to leave a kind comment

You're welcome Tom, thanks

Yep, cheers Matthew. For this demo it would have been good to turn it off

😀 I’ll be demoing a clever 3rd option that several people submitted in a week or two. All good learning for all of us

Great! Thanks for taking the time to leave a kind comment

Glad to help Darlene

You’re welcome. Thanks for taking the time to to let me know it was useful

Great, thanks for taking the time to leave a kind comment

Thanks for confirming Grainne

Cheers Ivan, thanks for taking part

You’re welcome Bhavik

Glad it helps

Thanks Luciano

You're welcome :)

Thanks for the suggestion Ravi

You’re welcome thanks for taking the time to leave a kind comment

Every year I say the same thing about future me 😀

You’re welcome. I appreciate you taking the time to let me know you found it useful

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

Thanks Sameh

You’re welcome 😀

Thank you

Thank you

No worries Zach

That's great Krishna, thanks

You’re welcome Jennifer

Yeah I like it too, thanks Kayode

You’re welcome

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

Thankyou

Greatly appreciated. Just let people know about the channel and that you found it useful! Thanks 🙏🏻

You're welcome

Feel free to submit a file or code Ryan

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

Yep, nice shortcut

Thanks Antony

@@AccessAnalytic thanks to You for sharing all theese interesting videos.

You’re welcome

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

Sure there’s almost always a way to do anything as long as there’s some logical pattern

Maybe unpivot? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ESap6ptV8fI.html

😀

Not sure what you mean, this is in Excel

This video may give you some ideas on that ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BZuX5zD8sUA.html

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

Thanks for joining in!