Excel Power Query - how to Split To Rows and Combine Rows

How to analyze data separated by commas.
In this video we see 4 core techniques

Link to file

Did you know I've written a book "Power BI for the Excel Analyst"?

Connect with me

[SndfOmar]

Thank you Wyn. I've learned a lot about PQ from your tutorials.

[baskis]

Thanks for sharing... here is my solution.
After the TRIM step, with this code in one step we can get to the solution:
= Table.Group(
#{"Trimmed text",
{"Name", "Session"},
{{"Days", each Text.Combine([Day], ", "), type text}}
)

I think it is the same solution, but without filtering the single day records.

[DJPejsen]

GroupedTable = Table.Group(
#”Trimmed Text”,
{ "Name", "Session" },
{{"Days", each Text.Combine( List.Sort ( List.Distinct( [Day] ), 0 ), ", "), type text}}
)

[carolshipley]

Can you try this one please. Data has 13 columns every time. First 3 rows are headings. Last 3 rows are grand totals. All other rows are groups of 4 where first row of group requires row one header, second row of 4 requires row two of headers, third row of 4 requires row three of headers and fourth row of group are all nulls.
Each group of 4 rows has one unique number in the first column on the first of the four rows.
First 4 columns are details remaining columns are numerical values.
Required is 3 column table with unique number column headers and numerical values😮

[williamarthur]

I must be improving I did it pretty much the same, no trim, I did try without unpivoting which is a
function i hate as it's one I always have to use via the interface, the syntax will not stick.