Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I want to create the index column below based on duplicates Key column:
Key,Index
A1,1
A1,2
B1,1
C1,1
D1,1
D1,2
E1,1
F1,1
I started with this but get only 1s.....
Thanks for your help.
Solved! Go to Solution.
hi @fandecine68 ,
this is mostly done with power query. try to paste the code below into a blank query,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitWBU04QyhlCuaBQrhDKDUjFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t]),
#"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each _, type table [Key=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
#"Removed Columns"
for detailed steps, check like:
https://community.fabric.microsoft.com/t5/Power-Query/Group-by-a-column-and-set-index/m-p/2918028
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
Hi @fandecine68
The rankx() function can only order by the distinct values, the duplicate values can get the same number.
You can try the method posted by @FreemanZ , or add a index column in power query:
And then add a calculate column:
Column =
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Key] = EARLIER ( 'Table'[Key] ) ),
'Table'[Index],
,
ASC
)
The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @fandecine68
The rankx() function can only order by the distinct values, the duplicate values can get the same number.
You can try the method posted by @FreemanZ , or add a index column in power query:
And then add a calculate column:
Column =
RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Key] = EARLIER ( 'Table'[Key] ) ),
'Table'[Index],
,
ASC
)
The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @fandecine68 ,
this is mostly done with power query. try to paste the code below into a blank query,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitWBU04QyhlCuaBQrhDKDUjFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t]),
#"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each _, type table [Key=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
#"Removed Columns"
for detailed steps, check like:
https://community.fabric.microsoft.com/t5/Power-Query/Group-by-a-column-and-set-index/m-p/2918028
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
User | Count |
---|---|
51 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
23 | |
22 |