How to transpose groups of rows into columns

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,172
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have the following table part way through a Powerquery:

Book1
ABC
2Input:
3IndexDP Group 1DP Group 2
423DP2null
534DP11DP8
634DP11-DP11
734DP11DP9
834DP11DP10
935nullDP2
1035nullDP3
Sheet1


And need to transpose it to something along these lines:

Book1
ABCDEFGHI
12Output
13IndexDP Group 1.1DP Group 1.2DP Group 1.3DP Group 1.4DPGroup2.1DPGroup2.2DPGroup2.3DPGroup2.4
1423DP2
1534DP11DP11DP11DP11DP8-DP11DP9DP10
1635DP2DP3
Sheet1


In effect, I want to group by the Index column and then spread the other rows across sufficient columns. I can't say at this stage how many columns will be required.

Any ideas gratefully accepted (even if the answer is put it into a sheet and run some VBA against it! - hope not).

Regards
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Please try

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Group(Source, {"Index"}, {{"T", each let
        A = Table.UnpivotOtherColumns(Table.AddIndexColumn(_,"i",1), {"Index", "i"}, "H", "V"),
        B = Table.Sort(A,{{"H", Order.Ascending}, {"i", Order.Ascending}}),
        C = Table.CombineColumns(Table.TransformColumnTypes(B, {{"i", type text}}),{"H", "i"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"M")
        in Table.Pivot(C, List.Distinct(C[M]), "M", "V")    }}),
    Combine = Table.Combine(Grouped[T])
in
    Combine

Example.xlsx
ABCDEFGHI
1
2Table1
3IndexDP Group 1DP Group 2
423DP2
534DP11DP8
634DP11-DP11
734DP11DP9
834DP11DP10
935DP2
1035DP3
11
12
13IndexDP Group 1.1DP Group 1.2DP Group 1.3DP Group 1.4DP Group 2.1DP Group 2.2DP Group 2.3DP Group 2.4
1423DP2
1534DP11DP11DP11DP11DP8-DP11DP9DP10
1635DP2DP3
Sheet2
 
Upvote 0
Morning Bo_Ry,

Apologies, I was intended to try your solution this today as it is a work problem, but things have stopped me doing so. I will come back to you as soon as I'm able. One quick question: do I need to modify the code if there are more DP Group's to be considered?

Many thanks.

Peter
 
Upvote 0
Hi,

Now had a chance to try your code - absolutely brilliant. Provides exactly the transformation required - now I need to understand how it works.

Many many thanks

Regards

Peter
 
Upvote 0
HI Bo-Ry,

Apologies, I thought I'd constrained the question sufficiently to make it easy to understand. I've oversimplified it. The actual input table looks like this:

Book1.xlsx
ABCDEF
1Input:
2Column3Column2Column1IndexDP Group 1DP Group 2
3WordsThingsWotsits23DP2null
4StuffNonsensehouse34DP11DP8
5StuffNonsensehouse34DP11-DP11
6StuffNonsensehouse34DP11DP9
7StuffNonsensehouse34DP11DP10
8WonderousInformationThings35nullDP2
9WonderousInformationThings35nullDP3
Sheet1


Which I hope to result in:

Book1.xlsx
ABCDEFGHIJKL
14Output
15Column3Column2Column1IndexDP Group 1.1DP Group 1.2DP Group 1.3DP Group 1.4DPGroup2.1DPGroup2.2DPGroup2.3DPGroup2.4
16WordsThingsWotsits23DP2
17StuffNonsensehouse34DP11DP11DP11DP11DP8-DP11DP9DP10
18WonderousInformationThings35DP2DP3
Sheet1


I had amended your code to replace {"Index"} with ColumnsToGroup in the Tables.Group function; it is a list of the column headings excluding those prefixed with 'DP - Group', but I ended up with many column duplications. Really hope you don't mind having another look.
 
Upvote 0
Try replace {"Index"} to {"Column3","Column2","Column1","Index"}

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Group(Source, {"Column3","Column2","Column1","Index"}, {{"T", each let
        A = Table.UnpivotOtherColumns(Table.AddIndexColumn(_,"i",1), {"Column3","Column2","Column1","Index", "i"}, "H", "V"),
        B = Table.Sort(A,{{"H", Order.Ascending}, {"i", Order.Ascending}}),
        C = Table.CombineColumns(Table.TransformColumnTypes(B, {{"i", type text}}),{"H", "i"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"M")
        in Table.Pivot(C, List.Distinct(C[M]), "M", "V")   }}),
    Combine = Table.Combine(Grouped[T])
in
    Combine
 
Upvote 0
Solution
HI Bo_Ry,

The list 'ColumnsToGroup' is defined as List.RemoveMatchingItems(Table.ColumnNames(#"Filtered Rows7"),{"DP Group 1", "DP Group 2"})). Which should result in the same thing. I've amended your code in the same way and it works a treat. I'll now have to test it on the data at work and find out what I did wrong.

All the best.

Peter
 
Upvote 0
Hi Bo_Ry

Many thanks for your solution - it works perfectly, though I still haven't deciphered it :)!

Now to make the whole thing run quickly - its taking hours at the moment, but that's an entirely different problem.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top