Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I saw this old question online and wanted to ask because unlike the question posted here - https://community.powerbi.com/t5/Desktop/Group-By-ID-and-rank-it-in-sequence-of-date/m-p/687683
I want to rank it without duplicates. So if a Number like 8028999 had 3 same modified date and time, it should be 1,2,3 and not all 1.
I followed the formula found in the link too but for my data, I need to avoid duplicated sequence.
DuplicateNumber = RANKX(FILTER('Table', 'Table'[Number] = EARLIER('Table'[Number])), 'Table'[Modified Date], , ASC, Dense
Solved! Go to Solution.
Hi @Honne2021
Here is my solution with Power Query.
Step #1
First sort Number column ascendingly. Then sort Modified Date ascendingly.
Step #2
From Transform > Group By, group by Number column and select All Rows for the new "AllData" column.
Step #3
Add a custom column with below code
Table.AddIndexColumn([AllData],"Index",1,1)
Every Table value in AllData_2 column will have an Index column like below.
Step #4
Remove Number and AllData columns. Preserve only AllData_2 in the query. Expand AllData_2 column.
You will have the result you want.
Full M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQCJR0lIwNDS31DfSMFQyMrIwMrYwOFAF+lWB0ayJuiyhsBAULeFEM/qjzYfBNkeWMgwGc/sryRviVeeQz9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, #"Modifited Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Modifited Date", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Number", Order.Ascending}, {"Modifited Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Number"}, {{"AllData", each _, type table [Number=nullable number, Modifited Date=nullable datetime]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AllData_2", each Table.AddIndexColumn([AllData],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"AllData_2"}),
#"Expanded AllData_2" = Table.ExpandTableColumn(#"Removed Other Columns", "AllData_2", {"Number", "Modifited Date", "Index"}, {"Number", "Modifited Date", "Index"})
in
#"Expanded AllData_2"
Cheers
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
-
Hi @Honne2021
Here is my solution with Power Query.
Step #1
First sort Number column ascendingly. Then sort Modified Date ascendingly.
Step #2
From Transform > Group By, group by Number column and select All Rows for the new "AllData" column.
Step #3
Add a custom column with below code
Table.AddIndexColumn([AllData],"Index",1,1)
Every Table value in AllData_2 column will have an Index column like below.
Step #4
Remove Number and AllData columns. Preserve only AllData_2 in the query. Expand AllData_2 column.
You will have the result you want.
Full M code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQCJR0lIwNDS31DfSMFQyMrIwMrYwOFAF+lWB0ayJuiyhsBAULeFEM/qjzYfBNkeWMgwGc/sryRviVeeQz9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, #"Modifited Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Modifited Date", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Number", Order.Ascending}, {"Modifited Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Number"}, {{"AllData", each _, type table [Number=nullable number, Modifited Date=nullable datetime]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AllData_2", each Table.AddIndexColumn([AllData],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"AllData_2"}),
#"Expanded AllData_2" = Table.ExpandTableColumn(#"Removed Other Columns", "AllData_2", {"Number", "Modifited Date", "Index"}, {"Number", "Modifited Date", "Index"})
in
#"Expanded AllData_2"
Cheers
If this reply helps solve the problem, please mark it as Solution! Kudos are appreciated too!
-
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |