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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power Query - create new row in table based on a calculation and grouing by an additional columns

Hi,

I have the following example table:

Division          Grouping         Value        Session
1                     Sales                200           2
1                     Adjustments    200           3
1                     Costs               55             3
2                     Refunds          210            2
2                     Costs              35              1
2                     Sales               140            5
2                     Rebate            200            3

I would like to create an additional row named SSGP which calculates the Sales value minus the Costs value, grouped by Division.

 

So the end result I am looking for here is:
Division          Grouping         Value        Session     
1                     Sales                200           2
1                     Adjustments    200           3
1                     Costs               55             3
2                     Refunds          210            2
2                     Costs              35              1
2                     Sales               140           5
2                     Rebate            200           3
1                     SSGP               145          
2                     SSGP               105

 

How do I do this in Power Query?

Thanks in advance.

2 ACCEPTED SOLUTIONS

@Anonymous 

 

In this case, we can use

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY+xDoAgDET/hdmBUgYcjR+giSNhYCAuRo34/5ErEeNS7novHHivSHVqTvHK5SSCM6YM1hoLQyp0FRrOc0ugHBJm4WHZNWY88g3ECAKBbd/yJdYrpIJ0K2MhIKcr7qswZL/HWK7ypd4eghECw7X812OtlMl/SlF4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Division = _t, Grouping = _t, Value = _t, Target = _t, MValue = _t, MTarget = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Grouping", type text}, {"Value", Int64.Type}, {"Target", Int64.Type}, {"MValue", Int64.Type}, {"MTarget", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(ChangedType, each ([Grouping] = "Costs" or [Grouping] = "Sales")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Division"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Value", each [All]{[Grouping="Sales"]}[Value]-[All]{[Grouping="Costs"]}[Value]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Target", each [All]{[Grouping="Sales"]}[Target]-[All]{[Grouping="Costs"]}[Target]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "MValue", each [All]{[Grouping="Sales"]}[MValue]-[All]{[Grouping="Costs"]}[MValue]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "MTarget", each [All]{[Grouping="Sales"]}[MTarget]-[All]{[Grouping="Costs"]}[MTarget]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Grouping", each "SSGP"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"All"}),
    #"Appended Query" = Table.Combine({#"Removed Columns", ChangedType}),
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"Division", "Grouping", "Value", "Target", "MValue", "MTarget"})
in
    #"Reordered Columns"

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

@Anonymous 

 

This is another alternative method

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY+xDoAgDET/hdmBUgYcjR+giSNhYCAuRo34/5ErEeNS7novHHivSHVqTvHK5SSCM6YM1hoLQyp0FRrOc0ugHBJm4WHZNWY88g3ECAKBbd/yJdYrpIJ0K2MhIKcr7qswZL/HWK7ypd4eghECw7X812OtlMl/SlF4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Division = _t, Grouping = _t, Value = _t, Target = _t, MValue = _t, MTarget = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Grouping", type text}, {"Value", Int64.Type}, {"Target", Int64.Type}, {"MValue", Int64.Type}, {"MTarget", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(ChangedType, each ([Grouping] = "Costs" or [Grouping] = "Sales")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Division", "Grouping"}, "Attribute", "Value1"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Grouping]), "Grouping", "Value1", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "SSGP", each [Sales]-[Costs]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Costs", "Sales"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "SSGP", List.Sum),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column1", "Grouping", each "SSGP"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Division", "Grouping", "MTarget", "MValue", "Target", "Value"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns", ChangedType})
in
    #"Appended Query"

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

Hi @Anonymous 

 

Try this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpOzEktBtJGBgYgUilWByLumJJVWlySm5pXgpA1hss65xeDxU1N4cJGQFZQalppXgpYgyHCOCMkDcYgDYZwYZjthiYg5aZIBiUllqQiWxwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Division = _t, Grouping = _t, Value = _t, Session = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Grouping", type text}, {"Value", Int64.Type}, {"Session", Int64.Type}}),
    Custom1 = ChangedType[[Division],[Grouping],[Value]],
    #"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[Grouping]), "Grouping", "Value", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "SSGP", each [Sales]-[Costs]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Division"}, "Grouping", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns",{"Division", "Grouping"},ChangedType,{"Division", "Grouping"},"Unpivoted Columns",JoinKind.LeftOuter),
    #"Expanded Unpivoted Columns" = Table.ExpandTableColumn(#"Merged Queries", "Unpivoted Columns", {"Session"}, {"Session"})
in
    #"Expanded Unpivoted Columns"

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

That's brilliant Zubair, thanks.

 

Okay, I need a slight variation to it. I will attempt the required myself but wishing to write it most efficiently I will ask you...

 

I require the same but for the following:

Division      Grouping       Value       Target      MValue      MTarget
1                 Pears              111          22            300            121
1                 Apples            80            33            11              838
1                 Costs              22            343          221            992

1                 Sales              22             1              22             33

2                 Oranges         141           22           343            22
2                 Costs              122           222         2232          88
2                 Sales               22             44           10             3

 

So the end result will be:

Division      Grouping       Value       Target      MValue      MTarget
1                 Pears              111          22            300            121
1                 Apples            80            33            11              838
1                 Costs              20            3              21              9

1                 Sales              22             10            22             33

2                 Oranges         141           22           343            22
2                 Costs              12             2             32              8
2                 Sales               22            44           100            13

1                 SSGP               2              7              1               24

2                 SSGP              10             42            68             5

 

The difference is I now requiring the SSGP calculation for Value, Target, MValue, and MTarget.

What is the most efficent Power Query code for this?

Thanks.

@Anonymous 

 

In this case, we can use

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY+xDoAgDET/hdmBUgYcjR+giSNhYCAuRo34/5ErEeNS7novHHivSHVqTvHK5SSCM6YM1hoLQyp0FRrOc0ugHBJm4WHZNWY88g3ECAKBbd/yJdYrpIJ0K2MhIKcr7qswZL/HWK7ypd4eghECw7X812OtlMl/SlF4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Division = _t, Grouping = _t, Value = _t, Target = _t, MValue = _t, MTarget = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Grouping", type text}, {"Value", Int64.Type}, {"Target", Int64.Type}, {"MValue", Int64.Type}, {"MTarget", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(ChangedType, each ([Grouping] = "Costs" or [Grouping] = "Sales")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Division"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Value", each [All]{[Grouping="Sales"]}[Value]-[All]{[Grouping="Costs"]}[Value]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Target", each [All]{[Grouping="Sales"]}[Target]-[All]{[Grouping="Costs"]}[Target]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "MValue", each [All]{[Grouping="Sales"]}[MValue]-[All]{[Grouping="Costs"]}[MValue]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "MTarget", each [All]{[Grouping="Sales"]}[MTarget]-[All]{[Grouping="Costs"]}[MTarget]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Grouping", each "SSGP"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"All"}),
    #"Appended Query" = Table.Combine({#"Removed Columns", ChangedType}),
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"Division", "Grouping", "Value", "Target", "MValue", "MTarget"})
in
    #"Reordered Columns"

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thanks Zubair.

 

Very impressive. I went with the first option only due to it being easier to digest.

@Anonymous 

 

This is another alternative method

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY+xDoAgDET/hdmBUgYcjR+giSNhYCAuRo34/5ErEeNS7novHHivSHVqTvHK5SSCM6YM1hoLQyp0FRrOc0ugHBJm4WHZNWY88g3ECAKBbd/yJdYrpIJ0K2MhIKcr7qswZL/HWK7ypd4eghECw7X812OtlMl/SlF4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Division = _t, Grouping = _t, Value = _t, Target = _t, MValue = _t, MTarget = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Grouping", type text}, {"Value", Int64.Type}, {"Target", Int64.Type}, {"MValue", Int64.Type}, {"MTarget", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(ChangedType, each ([Grouping] = "Costs" or [Grouping] = "Sales")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Division", "Grouping"}, "Attribute", "Value1"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Grouping]), "Grouping", "Value1", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "SSGP", each [Sales]-[Costs]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Costs", "Sales"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "SSGP", List.Sum),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column1", "Grouping", each "SSGP"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Division", "Grouping", "MTarget", "MValue", "Target", "Value"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns", ChangedType})
in
    #"Appended Query"

Regards
Zubair

Please try my custom visuals

@Anonymous 

 

Please see attached file with above 2 methods


Regards
Zubair

Please try my custom visuals

@Anonymous 

 

Please see attached file's query editor for steps

 

ssgp.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.