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"

 

 

View solution in original post

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"

View solution in original post

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"

 

 

View solution in original post

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"

View solution in original post

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 462 members 4,681 guests
Please welcome our newest community members: