Power Bi dax query

[Vishnu812]

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

Id	Rank
abc	0
abc	1
xyz	0
pqr	0
xyz	1
abc	2

Expected output

Id	Rank
abc	2
xyz	1
pqr	0

[BA_Pete]

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

[Vishnu812]

Hello @BA_Pete

Power Query

[BA_Pete]

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

[Vishnu812]

Hi, thanks for the help. I need little modify in the question like if there are two rank with same number then it should display both in output because it has another column branch so it should separate it by branch also. Refer below table for example

sample data

Branch	Id	Rank
a	abc	0
a	abc	1
a	xyz	0
a	pqr	0
a	xyz	1
a	abc	2
b	xyz	1

Output

Branch	Id	Rank
a	abc	2
a	xyz	1
b	xyz	1
a	pqr	0

[BA_Pete · Accepted solution]

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