Forum Discussion

Vishnu812's avatar
Vishnu812
Frequent Visitor
3 years ago
Solved

Power Bi dax query

Hi,

I have table contains multiple values with same name in Id column and has corresponding rank value in Rank column.

Need to remove the old rank values and keep the latest one.

For example:

Sample data

IdRank
abc0
abc1
xyz0
pqr0
xyz1
abc2

 

Expected output

IdRank
abc2
xyz1
pqr0
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    No problem. Just include the new [Branch] column within the Group By step, so you group on both [Branch] and [ID].

     

    New example code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpMSgaSBkqxOsh8Qzi/orIKRb6gsAiFD5E3RNNvBOYnIcvHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Branch = _t, Id = _t, Rank = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Rank", Int64.Type}}),

// Relevant steps from here ----->
    addIndex = Table.AddIndexColumn(chgTypes, "Index", 1, 1, Int64.Type),
    groupBranchId = Table.Group(addIndex, {"Branch", "Id"}, {{"data", each _, type table [Branch=nullable text, Id=nullable text, Rank=nullable number, Index=number]}}),
    addLatestRecord = Table.AddColumn(groupBranchId, "latestRecord", each Table.Max([data], "Index")),
    expandLatestRecord = Table.ExpandRecordColumn(addLatestRecord, "latestRecord", {"Rank"}, {"Rank"}),
    removeDataColumn = Table.RemoveColumns(expandLatestRecord,{"data"})
in
    removeDataColumn

     

    New output:

     

    Pete

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Vishnu812 ,

     

    Do you want this done in Power Query or in the data model?

    You've posted in the Power Query forum but referenced DAX in your title.

     

    Pete

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Ok. Try this:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRMlCK1YGxDcHsisoquHhBYRGcDRE3RFJvpBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Rank = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Rank", Int64.Type}}),

// Relevant steps from here ----->
    addIndex = Table.AddIndexColumn(chgTypes, "Index", 1, 1, Int64.Type),
    groupId = Table.Group(addIndex, {"Id"}, {{"data", each _, type table [Id=nullable text, Rank=nullable number, Index=number]}}),
    addLatestRecord = Table.AddColumn(groupId, "latestRecord", each Table.Max([data], "Index")),
    expandLatestRecord = Table.ExpandRecordColumn(addLatestRecord, "latestRecord", {"Rank"}, {"Rank"}),
    removeDataColumn = Table.RemoveColumns(expandLatestRecord,{"data"})
in
    removeDataColumn

         

        To get this output:

         

         

        Pete