Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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"
@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"
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"
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"
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"
@Anonymous
Please see attached file with above 2 methods
@Anonymous
Please see attached file's query editor for steps
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |