Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Forum,
I need an assistance in adding up two or multiple values for my calculation in datamodel. Please find the below sample data and output I expect.
Parent | Child | Info |
A | 1 | 22 |
A | 2 | 45 |
now I want to sum the info values as per the Parent and remove the duplicate parent values. so that I get unique values at the end.
Please note I am doing this in Data model -PowerPivot for Excel.
Solved! Go to Solution.
Hi @nikhil0511 ,
Is this your expected result?
If so, I can do it in Power Query. Please copy-paste the below code in a blank query to see the details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMjpVgdCNcIiE1MlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t, Info = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", Int64.Type}, {"Info", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Parent"}, {{"Child", each null, type nullable number},{"Info", each List.Sum([Info]), type nullable number} }),
#"Appended Query" = Table.Combine({#"Changed Type", #"Grouped Rows"})
in
#"Appended Query"
I also attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nikhil0511 ,
Is this your expected result?
If so, I can do it in Power Query. Please copy-paste the below code in a blank query to see the details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMjpVgdCNcIiE1MlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t, Info = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", Int64.Type}, {"Info", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Parent"}, {{"Child", each null, type nullable number},{"Info", each List.Sum([Info]), type nullable number} }),
#"Appended Query" = Table.Combine({#"Changed Type", #"Grouped Rows"})
in
#"Appended Query"
I also attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yanjiang-msft,
Thanks for the response.
I was able to perform the following output in Power Query using GroupBy and Sum. but i was expecting this to be done in Excel Data Model, as the Info column I provided in example is a calculated column in DataModel and needs to provide me the result in DataModel.
Regards
Nikhil