Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with modeling - duplicate values

Folks, need a help. I receive from a system the following table:

Engagement IDValeu USDStatus
12345100Active
12345200Active
12345-400Active
12345600Active
12345200Active

 

I need to transform it in something like this in a easy way using PBI

Engagement IDValeu USDStatus
12345700Active

 

I´ve been strugling to work with this data and I believe these duplicates fields are the reason. Any help?

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    You can use group by in the edit queries, then advance and add multiple column(Screenshot below)

     

3 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    7 years ago

    I believe this can be achieved using Group By on power query.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEyNDAAko7JJZllqUqxOggJI1wSuia4ZMyIMysWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Engagement ID" = _t, #"Valeu USD" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Engagement ID", Int64.Type}, {"Valeu USD", Int64.Type}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Engagement ID", "Status"}, {{"Value USD", each List.Sum([Valeu USD]), type number}})
in
    #"Grouped Rows"
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You can use group by in the edit queries, then advance and add multiple column(Screenshot below)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hey Anonymous 

     

    You can use the following DAX expression once the data has been imported into PBI:Consolidated Table = 

    SUMMARIZE(
    EngagementID, EngagementID[Status],
    EngagementID[Engagement ID],
    "Value USD", SUM(EngagementID[Value USD])
)

     

     

     

     

     

     

     

    Click HERE to access my PBIX file if needed.