Forum Discussion

nikhil0511's avatar
nikhil0511
Advocate I
3 years ago
Solved

Adding rows in power Pivot for Excel -datamodel

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 2...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

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.

nikhil0511's avatar
nikhil0511
Advocate I
3 years ago

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