Forum Discussion
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: Sam...
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 removeDataColumnNew output:
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
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 |
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 removeDataColumnNew output:
Pete