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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors