cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DanBusIntel Member
Member

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

Accepted Solutions
Super User
Super User

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

@DanBusIntel 

 

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"

 

 

Highlighted
Super User
Super User

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

@DanBusIntel 

 

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"
7 REPLIES 7
Super User
Super User

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

Hi @DanBusIntel 

 

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"

 

Super User
Super User

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

@DanBusIntel 

 

Please see attached file's query editor for steps

 

ssgp.png

DanBusIntel Member
Member

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

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.

Super User
Super User

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

@DanBusIntel 

 

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"

 

 

Highlighted
Super User
Super User

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

@DanBusIntel 

 

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"
Super User
Super User

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

@DanBusIntel 

 

Please see attached file with above 2 methods

DanBusIntel Member
Member

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

Thanks Zubair.

 

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