cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

2 ACCEPTED SOLUTIONS
Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Anonymous
Not applicable

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"``````

2 REPLIES 2
Anonymous
Not applicable

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"``````

Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube