Excel and Power BI Stacking Grouped Columns Challenge

The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic...
👏🏻 Update - thanks to @ianrhile for suggesting a simpler approach avoiding GroupBy and utilising INTEGER DIVIDE. I've added that method to the demo file below
Demo File
aasolutions.sh...
Submit alternative solutions to [e-mail]
A super simple DYNAMIC ARRAY formula such as
=WRAPROWS(TOCOL(Table1,1),3) could solve this solution if it was a one off Excel only solution
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@AccessAnalytic]

Check out the description section if you want to play along. A super simple DYNAMIC ARRAY formula such as =WRAPROWS(TOCOL(Table1,1),3) could solve this solution if it was a one off Excel only solution

[@ianrhile]

Fun. You could also add the index column to the unpivoted data (as shown); add the modulo 3 of the index column (as shown), and then add the integer-divide-by 3 of the same index column. Delete all columns but the value, modulo, and integer-divide columns, and then pivot based on the modulo column. The integer-divide column makes each one unique. (Hopefully that makes sense; it's hard to write this down.) Then delete the integer-divide column, rename the other columns, and change type.

[@AccessAnalytic]

I think I follow 😆

[@dmclean3324]

This was my solution as well

[@AccessAnalytic]

Ah yeah after a night’s sleep this made perfect sense. I even did a video using that method a few weeks back 🙄. Good call I’ll add that option into my solution file

[@raimundojs9547]

Thank you for sharing! Here's my contribution: let LstTableToColumns = Table.ToColumns(Source), LstSplit = List.Split(LstTableToColumns, 3), LstTables = List.Transform(LstSplit, each Table.FromColumns(_, {"Name", "Date", "e-mail"})), TbOutput = Table.Combine(LstTables) in TbOutput

[@AccessAnalytic]

Cheers. Would that still work if a 4th or 5th member was added?

[@raimundojs9547]

@@AccessAnalytic Yes, it would. Thank you so much for your response and thank you for all the content you've been sharing.

[@Ahmad_Askar]

this is very neat

[@user-mma173]

This is how I would solve it 👍

[@AccessAnalytic]

@@raimundojs9547 - This is a neat solution, I may well do a video showcasing this in the future and giving you a shout out. The slight downside is the members from one group aren't listed together.

[@derkgeerdink13]

This is absolutely amazing!

[@AccessAnalytic]

⭐️ Thanks very much ⭐️

[@BillSzysz1]

For those who like to play with M, a one-step solution (apart from changing column types).😉 let Source = Table.FromRows( List.Combine( List.Transform( Table.ToRows( Excel.CurrentWorkbook(){[Name="Table1"]}[Content] ), each List.Split(_, 3))), {"Name", "DOB", "Email"} ) in Source

[@AccessAnalytic]

Nice Bill

[@TopBam]

Excellent power query solution. In this particular case, I would just use a TOCOL and WRAPROWS formula combo.

[@AccessAnalytic]

Yep agreed, but if the data was a recurring export, or if using Power BI then I’d stick with Power Query

[@mnowako]

Amazing! Thank you!

[@AccessAnalytic]

You’re welcome.

[@chrism9037]

Awesome! PQ is the best

[@AccessAnalytic]

Absolutely

[@damiannorton6450]

Elegant solution, nice!!

[@AccessAnalytic]

Cheers

[@kebincui]

Excellent!👍

[@AccessAnalytic]

Cheers

[@rameshkunda6775]

Nice 👍

[@AccessAnalytic]

Cheers

[@williamarthur4801]

I know it's not very dynamic , but I just selected all columns ; unpivot ; remove "attribute' then ; Table.Combine( List.Transform( Table.Split( Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), 3 ), (A)=> Table.Transpose(A) )) , you could probably make the page size dynamic and the columns still need naming . Just had a though if you don't use transpose, but table from rows you can renaame; Table.FromRows( Table.ToColumns( A), { "Name", "DOB", "Email"} ) )

[@AccessAnalytic]

Thanks for taking part

[@rjbush7955]

Brilliant! Seen this type of data many times and tried to breakout but got lost after pivoting the first time. My solution is easier; go back to the person and ask them to do it properly 😂

[@AccessAnalytic]

Yeah sadly all too often these are the outputs from source systems and ERPs which you often cant alter.

[@adrianoschwenkberg6773]

Starting from demotable and a list with column names, you could solve the task thing like this let source = Demodata, grouplist = List.Split(Table.ColumnNames(source),List.Count(groupdef)), groupdef = {"NAME","DOB","EMAIL"}, unstacking = List.Accumulate(grouplist,Table.FromRows({},groupdef),(tbl_out,colName) => Table.Combine({tbl_out,Table.RenameColumns(Table.SelectColumns(source,colName), List.Zip({Table.ColumnNames(Table.SelectColumns(source,colName)),groupdef}))}) ) in unstacking

[@adrianoschwenkberg6773]

the approach is : groupdef is a list used for the final columnnames and also by the count as a columncount for the stackingprocess for the outputtable. The stacking is done by list.accumulate which iterates throuh the grouplist and takes the group-columnes , rename them and stack them on the outputtable.

[@AccessAnalytic]

Thanks for taking part.

[@jerrydellasala7643]

Love your PQ videos. However, it sounded like the original Table1 data was of group leaders or organizers in the first column [Oname], their DOB [ODOB] and email [OEmail], and the 3 members of those groups. The solution just lumps everyone together. Would love to see a solution similar to yours, but with columns for the organizers. Hope that makes sense!

[@AccessAnalytic]

Thanks I could keep the Group ID and Sequence ID which would then allow filtering by leaders or groups. If you want the groups to go horizontally then maybe a simple transpose might do it. Or pivot based on group ID rather than the header id

[@milindkapadia1425]

Hi there, I found this video really interesting, and it reminded me of a Power Query challenge I recently worked on. Interestingly, the solution presented in the video is similar to the problem I had, and vice versa. The key difference is that I restricted my data to text type only, as I'm merging columns. Let me share a brief overview of the scenario: Original Table: Sr No Country State City 1 India Maharashtra Mumbai 2 Australia N. South Wales Sydney 3 USA California Los Angeles 4 Canada Ontario Toronto My Solution: I created a solution that dynamically rearranges the table into stacked blocks. The user can choose the number of blocks they want to generate. Here’s how it looks: Sr No_0 Country_0 State_0 City_0 Sr No_1 Country_1 State_1 City_1 1 India Maharashtra Mumbai 3 USA California Los Angeles 2 Australia N. South Wales Sydney 4 Canada Ontario Toronto Key Features: Dynamic Flexibility: The solution is fully dynamic. You can add as many columns or rows as needed, and the table will adjust accordingly. User Input: The user can specify the number of blocks they require. In the above example, I chose to create two blocks. Text Data Restriction: The only limitation is that the data types must be text, as I’m merging the columns. This solution has proven to be very versatile, allowing for easy expansion and reorganization of data based on user needs. However, managing data types while keeping everything in text format has been a unique challenge.

[@AccessAnalytic]

Glad you got a solution working and that Power Query is solving your data issues.

[@AccessAnalytic]

Sounds a little like this : kzbin.info/www/bejne/e6rXc2dpbcd7mpIsi=ATCCECJHxsRqx6OD

[@milindkapadia1425]

@@AccessAnalytic No, actually, your solution is my question, and my question is your solution. In other words, I used your output table to create what would be your input table.

[@milindkapadia1425]

This is with reference to your video 'Excel and Power BI Stacking Grouped Columns Challenge".

[@mienzillaz]

ok, i'm tracking social here on YT to get challenge notifications, but there was none.. i missed this. Anyways, here's my go: let Source = List.Combine(Table.ToRows(Table1)), Custom1 = List.Zip({Source, List.Transform(List.Positions(Source), each Number.RoundDown(_/3,0))}), Custom2 = List.Accumulate({0..((List.Count(Custom1)/3)-1)}, #table({"Name", "DOB", "Email"},{}), (s,c)=> Table.InsertRows(s, c, {[Name= List.Select(Custom1, each _{1}=c){0}{0}, DOB= List.Select(Custom1, each _{1}=c){1}{0}, Email= List.Select(Custom1, each _{1}=c){2}{0}]})) in Custom2

[@AccessAnalytic]

Thanks for taking part

[@mienzillaz]

@@AccessAnalytic how is my query?

[@AccessAnalytic]

I rarely write M code so seeing it standalone on the screen doesn’t jump out at me as good or bad. I appreciate the skill in the knowledge and use of functions and approaches like list.accumulate. My preference is wherever possible to use the UI as it’s easier to explain and for others to learn.

[@mienzillaz]

@@AccessAnalytic I learned rather quickly that in my tasks UI is insufficient and inefficient, leading to slow and maintenance hungry data models.

[@AccessAnalytic]

Yes, that’s definitely the right time to delve into the code.

[@Ahmad_Askar]

sorry for this long code = Table.Combine({Table.SelectColumns(Data, List.FirstN( Table.ColumnNames(Data),3)),Table.RenameColumns(Table.SelectColumns(Data,List.Split( Table.ColumnNames(Data),3){1}),List.Zip({List.Split( Table.ColumnNames(Data),3){1},List.Split( Table.ColumnNames(Data),3){0}})),Table.RenameColumns(Table.SelectColumns(Data,List.Split( Table.ColumnNames(Data),3){2}),List.Zip({List.Split( Table.ColumnNames(Data),3){2},List.Split( Table.ColumnNames(Data),3){0}})),Table.RenameColumns(Table.SelectColumns(Data,List.Split( Table.ColumnNames(Data),3){3}),List.Zip({List.Split( Table.ColumnNames(Data),3){3},List.Split( Table.ColumnNames(Data),3){0}}))})

[@AccessAnalytic]

Thanks for taking part