Advanced Rank in Power Query

[jeamps01]

Hello! How can I do this in the Power Query Editor? I need to create a ranking per month of certain values. Here's the table I'm trying to recreate. I need to calculate the Ranking column. 

Date		Index		Ranking
1/1/2021		105		1
1/1/2021		103		2
1/1/2021		85		4
1/1/2021		95		3
2/1/2021		107		2
2/1/2021		108		1
2/1/2021		98		3
2/1/2021		96		4

[cris007]

Hello all, 

Is there a way to achieve the same but only with the function table.addrankcolumn rather than using Group by and Index Column? 

The reason I am asking is because if there is a tie, I should get the same position for the values with a tie. The index column from the power query will not give me this result. 

For example, below, the two first lines rows should get the same ranking based on Index: 

Date		Index		Ranking
1/1/2021		105		1
1/1/2021		105		1
1/1/2021		85		3
1/1/2021		95		2
2/1/2021		107		2
2/1/2021		108		1

Anyone knows how to do this ? 

Thanks

[v-luwang-msft]

Hi @jeamps01 ,

Only need two steps:

step1,group and add inex:

= Table.Group(Source, {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Ranking",1,1), type table}})

Step2, expand table:

= Table.ExpandTableColumn(Partition, "Partition", {"Index", "Ranking"}, {"Index", "Ranking"})

All power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", type number}}),
    Partition = Table.Group(Source, {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Ranking",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Ranking"}, {"Index", "Ranking"})
in
        #"Expanded Partition"

Output result:

Accord you provided ,change to the below:

let
    Source = SharePoint.Files("XXX", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "XXX")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Census#", Int64.Type}, {"DateTY", type text}, {"PropSupTY", Int64.Type}, {"PropDemTY", Int64.Type}, {"PropRevTY", Int64.Type}, {"CompSupTY", Int64.Type}, {"CompDemTY", Int64.Type}, {"CompRevTY", Int64.Type}, {"OccRankTY", type text}, {"ADRRankTY", type text}, {"RevParRankTY", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
    #"Inserted Suffix" = Table.AddColumn(#"Reordered Columns", "Suffix", each [DateTY] & "01", type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Suffix",{{"Suffix", type date}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Suffix", "DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Suffix", "Date"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"DateTY"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Census#", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Index", each ([PropRevTY]/[PropSupTY])/([CompRevTY]/[CompSupTY])),



    #"Changed Type3" = Table.TransformColumnTypes(Source,{{"Index", type number}}),
    Partition = Table.Group(Source, {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Ranking",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Ranking"}, {"Index", "Ranking"})
in
        #"Expanded Partition"

And you could refer:

https://www.myonlinetraininghub.com/dense-ranking-in-power-query 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[PhilipTreacy · Accepted solution]

Hi @jeamps01 

Download example PBIX file

This query does it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyNDBVitVBEzJGF7LAUGQJETFC1maOKWSBLmSJKWKmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}),
    #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}})
in
    #"Renamed Columns"

Regards

Phil

[jeamps01]

Hi Philip

Thank you so much for the response! I tried patching in that query into my original source and didn't get any syntax errors so that's good. However once it tried to run the query I get an error that states: 

Expression.Error: The column 'Index.2' of the table wasn't found.
Details:
Index.2

Here's the query after the space is the your query

let
    Source = SharePoint.Files("XXX", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "XXX")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (6)", each #"Transform File (6)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (6)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (6)", Table.ColumnNames(#"Transform File (6)"(#"Sample File (6)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Census#", Int64.Type}, {"DateTY", type text}, {"PropSupTY", Int64.Type}, {"PropDemTY", Int64.Type}, {"PropRevTY", Int64.Type}, {"CompSupTY", Int64.Type}, {"CompDemTY", Int64.Type}, {"CompRevTY", Int64.Type}, {"OccRankTY", type text}, {"ADRRankTY", type text}, {"RevParRankTY", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
    #"Inserted Suffix" = Table.AddColumn(#"Reordered Columns", "Suffix", each [DateTY] & "01", type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Suffix",{{"Suffix", type date}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Suffix", "DateTY", "Census#", "PropSupTY", "PropDemTY", "PropRevTY", "CompSupTY", "CompDemTY", "CompRevTY", "OccRankTY", "ADRRankTY", "RevParRankTY"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Suffix", "Date"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"DateTY"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Census#", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Index", each ([PropRevTY]/[PropSupTY])/([CompRevTY]/[CompSupTY])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Index", type number}}),

    #"Changed Type4" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Date", Order.Ascending}, {"Index", Order.Descending}}),
    #"Grouped Rows" = Table.Group(Table.Buffer(#"Sorted Rows"), {"Date"}, {{"Count", each _, type table [Date=nullable date, Index=nullable number, Index.1=number]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index.2", 1, 1, Int64.Type)),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Index", "Index.1", "Index.2"}, {"Index", "Index.1", "Index.2"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"Date", Order.Ascending}, {"Index.1", Order.Ascending}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Sorted Rows1",{"Index.1"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Index.2", "Ranking"}})
in
    #"Renamed Columns2"

[PhilipTreacy]

Hi @jeamps01 

Can you please send me the data you are using, and a screenshot of the table in Power Query before you add my query steps.

My query is using column names specific to the columns in the dummy data I set  up.  Your query is using column names that are in your data.  

You need to replicate my steps to carry out the transformsations on your data/columns.  The error you are getting is is because my dummy data has a column called Index.2, yours doesn't.

If I can see what data and column names you have, I can try to modify my code to match your column names.

Regards

Phil