Power Query count rows

[mbosch]

Hello!

I would like to add a columns Index like the one you can see below.

Index should count accounts by user and Cyle sorted by higher to lower. Scores can be repeated.

I am using following formula but it only ranks 1,1,1,1
= Table.Group(
Personalizado1,
{"Cycle", "User", "Account"},
{
{"Max Score", each List.Max([Score]), type number},
{"Index", each Table.AddIndexColumn(_, "Index", 1, 1), type table}
}
)

Cycle	User	Account	Score	INDEX
A	Alex	XXy	100	1
A	Alex	XXz	100	2
A	Alex	XXu	50	3
A	Vik	XXc	100	1
A	Vik	XXv	80	2
A	Vik	XXb	80	3
B	Alex	XXy	100	1
B	Alex	XXz	100	2
B	Alex	XXu	50	3
B	Vik	XXc	100	1
B	Vik	XXv	80	2
B	Vik	XXb	80	3

Really appreaciate some help! Thank you!

[tackytechtom · Accepted solution]

Hi @mbosch ,

How about this? 🙂

The NewIndex was the column I created.

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLMSa0AUhERlUDS0MAARCrF6qBJVsEljTAlS4GkKUjOGC4XlpkNlkrGYihMrgxIWqAaCZNKgklBTHTC504nfO50wuNOJzzudMLtTicc7owFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cycle = _t, User = _t, Account = _t, Score = _t, INDEX = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cycle", type text}, {"User", type text}, {"Account", type text}, {"Score", Int64.Type}, {"INDEX", Int64.Type}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Score", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows1", {"Cycle", "User"}, {{"Grouping", each _, type table [Cycle=nullable text, User=nullable text, Account=nullable text, Score=nullable number, INDEX=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "NewIndex", 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Cycle", "User", "Account", "Score", "INDEX", "NewIndex"}, {"Cycle", "User", "Account", "Score", "INDEX", "NewIndex"})
in
    #"Expanded Custom"

I took idea from here.

Let me know if this solves your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/