How to transpose groups of rows into columns

[pjmorris]

Hi,

I have the following table part way through a Powerquery:

Book1
	A	B	C
2	Input:
3	Index	DP Group 1	DP Group 2
4	23	DP2	null
5	34	DP11	DP8
6	34	DP11	-DP11
7	34	DP11	DP9
8	34	DP11	DP10
9	35	null	DP2
10	35	null	DP3
Sheet1

And need to transpose it to something along these lines:

Book1
	A	B	C	D	E	F	G	H	I
12	Output
13	Index	DP Group 1.1	DP Group 1.2	DP Group 1.3	DP Group 1.4	DPGroup2.1	DPGroup2.2	DPGroup2.3	DPGroup2.4
14	23	DP2
15	34	DP11	DP11	DP11	DP11	DP8	-DP11	DP9	DP10
16	35					DP2	DP3
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

 

[Bo_Ry]

Please try

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
	A	B	C	D	E	F	G	H	I
1
2	Table1
3	Index	DP Group 1	DP Group 2
4	23	DP2
5	34	DP11	DP8
6	34	DP11	-DP11
7	34	DP11	DP9
8	34	DP11	DP10
9	35		DP2
10	35		DP3
11
12
13	Index	DP Group 1.1	DP Group 1.2	DP Group 1.3	DP Group 1.4	DP Group 2.1	DP Group 2.2	DP Group 2.3	DP Group 2.4
14	23	DP2
15	34	DP11	DP11	DP11	DP11	DP8	-DP11	DP9	DP10
16	35					DP2	DP3
Sheet2

 

[pjmorris]

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

 

[pjmorris]

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

 

[pjmorris]

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
	A	B	C	D	E	F
1	Input:
2	Column3	Column2	Column1	Index	DP Group 1	DP Group 2
3	Words	Things	Wotsits	23	DP2	null
4	Stuff	Nonsense	house	34	DP11	DP8
5	Stuff	Nonsense	house	34	DP11	-DP11
6	Stuff	Nonsense	house	34	DP11	DP9
7	Stuff	Nonsense	house	34	DP11	DP10
8	Wonderous	Information	Things	35	null	DP2
9	Wonderous	Information	Things	35	null	DP3
Sheet1

Which I hope to result in:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
14	Output
15	Column3	Column2	Column1	Index	DP Group 1.1	DP Group 1.2	DP Group 1.3	DP Group 1.4	DPGroup2.1	DPGroup2.2	DPGroup2.3	DPGroup2.4
16	Words	Things	Wotsits	23	DP2
17	Stuff	Nonsense	house	34	DP11	DP11	DP11	DP11	DP8	-DP11	DP9	DP10
18	Wonderous	Information	Things	35					DP2	DP3
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.

 

[Bo_Ry]

Try replace {"Index"} to {"Column3","Column2","Column1","Index"}

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

 

[pjmorris]

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

 

[pjmorris]

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.