Use Rank function in 2 different table

[Amardeep100115]

Is there any way to put rank on according to Emp # in Table1 and Table2 data? i would like have same rank ID for Customer ID # from both data sources,

(this should be done at query editor)  without merging two table

Please note both tables have monthly data 

[Anonymous · Accepted solution]

Hi @Amardeep100115 

Here I build a sample to show you how to get rank in power query according to EMP and use rank in two tables by customer id.

Table A:

TableB:

Add a rank column in  TableA:

Sort TableA by Year, Month and EMP. Here I sort by asce. Then group all rows by Year and Month column then add index column in Advance editor and expand table to get result.

... 
...
Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDIEUqZAbGygFKsTreSEKmoEEXVGFTU0BYsimWAGEjVDNwEsao5uAljUEizqgioKsi0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, Year = _t, Month = _t, Emp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Emp", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Month", Order.Ascending}, {"Emp", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Year", "Month"}, {{"Rows", each _, type table [Customer ID=nullable text, Year=nullable number, Month=nullable number, Emp=nullable number]}}),
    Indexed = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Customer ID", "Emp", "GroupIndex"}, {"Rows.Customer ID", "Rows.Emp", "Rows.GroupIndex"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Rows",{{"Rows.Customer ID", "Customer ID"}, {"Rows.Emp", "Emp"}, {"Rows.GroupIndex", "GroupIndex"}})
in
    #"Renamed Columns"

Then add a custom column in Table B.

(x) =>Table.SelectRows(#"Table A", each _[Customer ID] = x[Customer ID] and _[Year] = x[Year] and _[Month] = x[Month]){0}[GroupIndex]

Replace error by null and get result.

 Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Greg_Deckler]

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.