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
aasolutions.sharepoint.com/:f...
Vote here to get the hidden setting changed ideas.fabric.microsoft.com/id...
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@kebincui]

Brilliant ❤👍. Thanks Wyn

[@AccessAnalytic]

Thank you

[@SndfOmar]

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

[@AccessAnalytic]

You’re welcome. Greatly appreciated.

[@ChristosKolokotronis]

Impressive!!!!!!

[@AccessAnalytic]

Thanks

[@baskis69]

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.

[@AccessAnalytic]

Thanks:
Yep we could add in the count of rows too in order to then filter out the 1s
= Table.Group(#"Trimmed Text", {"Session", "Names"},
{
{ "Double Booked", each Text.Combine([Day],","),type text },
{"Count", each Table.RowCount(_), Int64.Type}
}
)

[@josh_excel]

Good stuff.

[@AccessAnalytic]

Cheers

[@ramruttunaubeeluck9235]

Awesome

[@AccessAnalytic]

Thanks

[@DJPejsen]

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

[@AccessAnalytic]

Thanks for taking part 🙂. I'm not sure what the List.Distinct part is doing., Also this misses the need to just keep where days are doubled up

[@williamarthur4801]

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.

[@AccessAnalytic]

First person I've ever heard who hates unpivoting 🫨!

[@williamarthur4801]

@@AccessAnalytic To be honest I've never really got it, why if you unpivot and then pivot do you not end up back where you started, also, and this may not be true,
that it's an expensive process on large data sets.

[@AccessAnalytic]

@@williamarthur4801 Yeah Pivotting can be a pain but Unpivotting is a joy. You're right it is odd that in some cases they don't reverse each other.
Couple of related videos:
czcams.com/video/u0raaZ8BW0k/video.htmlsi=oC0BeaRkKtB7qDby
czcams.com/video/Tzv7jZ9WjAM/video.htmlsi=rWHobgr26xU42Nfn