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
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
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 removeDataColumnTo 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
