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

[@kebincui]

Brilliant ❤👍. Thanks Wyn

[@AccessAnalytic]

Thank you

[@FsoOmar]

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

[@AccessAnalytic]

You’re welcome. Greatly appreciated.

[@nazarkamal8831]

❤❤❤❤❤ brilliant

[@AccessAnalytic]

Thank you

[@Azhar_Khan383]

Awesome Sir

[@AccessAnalytic]

Cheers

[@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}
}
)

[@ChristosKolokotronis]

Impressive!!!!!!

[@AccessAnalytic]

Thanks

[@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:
th-cam.com/video/u0raaZ8BW0k/w-d-xo.htmlsi=oC0BeaRkKtB7qDby
th-cam.com/video/Tzv7jZ9WjAM/w-d-xo.htmlsi=rWHobgr26xU42Nfn

[@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

[@josh_excel]

Good stuff.

[@AccessAnalytic]

Cheers

[@carolshipley7903]

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😮

[@AccessAnalytic]

I’d suggest you post a screenshot and requirements to
www.reddit.com/r/excel/
Or
techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589

[@ramruttunaubeeluck9235]

Awesome

[@AccessAnalytic]

Thanks