Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
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 NameUnit NumberPO ValuePOIndex
NPP - Retail78NX84283600B009143111
PP1 - Retail78NX84173720B009143111
PP1 - Resi Pass78NX84153960B009206431
PP1 - Resi Pass78NX84141618B009207781
PP1 - Resi Pass78NX841424810B009131332
PP1 - Resi Pass78NX841413800B009143113
P0-1A - Hotel Passenger 378NX83963774B009206431
P0-1A - Hotel Passenger 278NX839521824B009141621

 

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 NameUnit NumberPO ValueInvoicedUnit Number1234567891011121314
NPP - Retail78NX84283600078NX8428B00914311-12nullnull           
P0-1A - Hotel Passenger 278NX841721824078NX8395B00914162-02nullnull           
P0-1A - Hotel Passenger 378NX84153774078NX8396B00920643-05nullnull           
PP1 - Resi Pass78NX84141618078NX8414B00920778-18nullnull           
PP1 - Resi Pass78NX841413800078NX8414nullnullB00914311-01           
PP1 - Resi Pass78NX841424810078NX8414nullB00913133-01null           
PP1 - Resi Pass78NX83963960078NX8415B00920643-19nullnull           
PP1 - Retail78NX83953720078NX8417B00914311-04nullnull           

 

 

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 NameUnit NumberPO ValueInvoicedUnit Number1234
NPP - Retail78NX84283600078NX8428B00914311-12nullnull 
P0-1A - Hotel Passenger 278NX841721824078NX8395B00914162-02nullnull 
P0-1A - Hotel Passenger 378NX84153774078NX8396B00920643-05nullnull 
PP1 - Resi Pass78NX841440228078NX8414B00920778-18B00913133-01B00914311-01 
PP1 - Resi Pass78NX83963960078NX8415B00920643-19null  
PP1 - Retail78NX83953720078NX8417B00914311-04nullnull 


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
mahoneypat
Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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"

 

 

View solution in original post

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"

 

 

mahoneypat
Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors