Power Query - Table. Group, Transformation with a dynamic number of Columns

[Anonymous]

Hello, 

I am look for help to build a function that would transform a table as follow:

Group Name	Unit Number	PO Value	PO	Index
NPP - Retail	78NX8428	3600	B00914311	1
PP1 - Retail	78NX8417	3720	B00914311	1
PP1 - Resi Pass	78NX8415	3960	B00920643	1
PP1 - Resi Pass	78NX8414	1618	B00920778	1
PP1 - Resi Pass	78NX8414	24810	B00913133	2
PP1 - Resi Pass	78NX8414	13800	B00914311	3
P0-1A - Hotel Passenger 3	78NX8396	3774	B00920643	1
P0-1A - Hotel Passenger 2	78NX8395	21824	B00914162	1

The second table is the result of a pivot column (Index) With the Min value of the Colum PO. - > That is not my problem

Group Name

Unit Number

PO Value

Invoiced

Unit Number

1

2

3

4

5

6

7

8

9

10

11

12

13

14

NPP - Retail

78NX8428

3600

0

78NX8428

B00914311-12

null

null

P0-1A - Hotel Passenger 2

78NX8417

21824

0

78NX8395

B00914162-02

null

null

P0-1A - Hotel Passenger 3

78NX8415

3774

0

78NX8396

B00920643-05

null

null

PP1 - Resi Pass

78NX8414

1618

0

78NX8414

B00920778-18

null

null

PP1 - Resi Pass

78NX8414

13800

0

78NX8414

null

null

B00914311-01

PP1 - Resi Pass

78NX8414

24810

0

78NX8414

null

B00913133-01

null

PP1 - Resi Pass

78NX8396

3960

0

78NX8415

B00920643-19

null

null

PP1 - Retail

78NX8395

3720

0

78NX8417

B00914311-04

null

null

What I would like to achieve:

- Being Able to do a pivot base on the column index.

- Once the pivot done to Sum the colum PO Value & Invoiced. 

Group Name	Unit Number	PO Value	Invoiced	Unit Number	1	2	3	4
NPP - Retail	78NX8428	3600	0	78NX8428	B00914311-12	null	null
P0-1A - Hotel Passenger 2	78NX8417	21824	0	78NX8395	B00914162-02	null	null
P0-1A - Hotel Passenger 3	78NX8415	3774	0	78NX8396	B00920643-05	null	null
PP1 - Resi Pass	78NX8414	40228	0	78NX8414	B00920778-18	B00913133-01	B00914311-01
PP1 - Resi Pass	78NX8396	3960	0	78NX8415	B00920643-19	null
PP1 - Retail	78NX8395	3720	0	78NX8417	B00914311-04	null	null

My issue is that the number of columns can vary which make the command Table.Group Difficult

Thanks For Your help

Regards

[mahoneypat · Accepted solution]

It might be easier to get your sum before you pivot.  I didn't see a Invoiced column, but here is how to do it with the PO Value column.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdC7CoMwFAbgVwmZFXIuTY5jnTpJ6FQQB4dQBGmh+v40UdTBXpxyhnzn8te1rrxXubqGse16nWkn1U0YJZZkjYlPaUwBTACxBt1ktfYe9gRcIg5/k6FTvh2GTZ2SKuyi0Fim/4rTFwuyKufkmEIWWFckoDQMDwwj2YVBMzM5nCO8PMfQTzI87uGlaGkQr5uScfz5xi8eN58yQhDkdQGwODdo3g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group Name" = _t, #"Unit Number" = _t, #"PO Value" = _t, PO = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group Name", type text}, {"Unit Number", type text}, {"PO Value", Int64.Type}, {"PO", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit Number"}, {{"PO Sum", each List.Sum([PO Value]), type nullable number}, {"AllRows", each _, type table [Group Name=nullable text, Unit Number=nullable text, PO Value=nullable number, PO=nullable text, Index=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Group Name", "PO", "Index"}, {"Group Name", "PO", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded AllRows", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded AllRows", {{"Index", type text}}, "en-US")[Index]), "Index", "PO", List.Min)
in
    #"Pivoted Column"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous · Accepted solution]

I have to admit I'm not sure I understand the description. I only rely on the expected table that you have shown. I added the fancy Invoiced column to the input table.
Maybe the following code can be useful as a starting point ...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZA/C4MwEMW/imRWyP0xiWM7dZLQqSAODlIEaaH6/WkuVqWWti7JDfd7996rKlV6n2TJuR2brlepsq68OEYXRjJahw8AwnvUugCmOIOq00p5D58gWAEtCoiIP8GhS3wzDCubC1sYYYleKGrD9B9lWTEgtpl5Ya11+1hkB3I3z/PZM0E0gTsOk4tNGWM2gWmCdQaHgJ/uY9tHvr1d20dCs0wIHWuz/FbbJvsXFVxVxDuCQ5EhXpIwGJxk6ic=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group Name" = _t, #"Unit Number" = _t, #"PO Value" = _t, Invoiced = _t, PO = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group Name", type text}, {"Unit Number", type text}, {"PO Value", Int64.Type}, {"Invoiced", Int64.Type}, {"PO", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group Name"}, {{"sum", each List.Sum([PO Value]&[Invoiced]), type number}, {"all", each _, type table [Group Name=text, Unit Number=text, PO Value=number, Invoiced=number, PO=text, Index=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Unit Number", "PO", "Index"}, {"all.Unit Number", "all.PO", "all.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded all", {{"all.Index", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Expanded all", {{"all.Index", type text}}, "it-IT")[all.Index]), "all.Index", "all.PO")
in
    #"Pivoted Column"

[mahoneypat · Accepted solution]

It might be easier to get your sum before you pivot.  I didn't see a Invoiced column, but here is how to do it with the PO Value column.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdC7CoMwFAbgVwmZFXIuTY5jnTpJ6FQQB4dQBGmh+v40UdTBXpxyhnzn8te1rrxXubqGse16nWkn1U0YJZZkjYlPaUwBTACxBt1ktfYe9gRcIg5/k6FTvh2GTZ2SKuyi0Fim/4rTFwuyKufkmEIWWFckoDQMDwwj2YVBMzM5nCO8PMfQTzI87uGlaGkQr5uScfz5xi8eN58yQhDkdQGwODdo3g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group Name" = _t, #"Unit Number" = _t, #"PO Value" = _t, PO = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group Name", type text}, {"Unit Number", type text}, {"PO Value", Int64.Type}, {"PO", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit Number"}, {{"PO Sum", each List.Sum([PO Value]), type nullable number}, {"AllRows", each _, type table [Group Name=nullable text, Unit Number=nullable text, PO Value=nullable number, PO=nullable text, Index=nullable number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Group Name", "PO", "Index"}, {"Group Name", "PO", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded AllRows", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded AllRows", {{"Index", type text}}, "en-US")[Index]), "Index", "PO", List.Min)
in
    #"Pivoted Column"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat