Translating dynamic array formula for ranking into Power Query M code

Hi,

I have the following formula (courtesy of Bo_Ry), which gives incrementally increasing rank numbers to rows in a table, with equal rank to duplicate rows:

=LET(z,Table[Batch]&" | "&Table[Step Text]&" | "&Table[Control recipe],u,SORT(UNIQUE(z)),MATCH(z,u,)-MATCH(Table[Batch]&" | "&Table[Step Text]&" | "&"*",u,))+1

Please see attached workbook with example.

I am trying to achieve what this formula does by working with the data in Power Query. I have imported the table, and pasted values of the rank numbers, but I need an M code that does what the dynamic array function above does.

Any suggestions?

Best regards,
Marbleking

Hi,

Power Query? Are you sure you don't mean within DAX? Power Query is intended for accessing and transforming data; it is not designed to perform calculations, especially complex ones as you have here. In general: import, shape and transform the data within Power Query, then perform calculations within DAX (using either measures or calculated columns added to your tables).

Regards

Try

PHP Code: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    AIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Grouped = Table.Group(AIndex, {"Batch", "Step Text"}, {{"C", each Table.AddIndexColumn(Table.Group(_,{"Control recipe"},{"T",each _}),"Rank",1)  }}),
    ExpandedC = Table.ExpandTableColumn(Grouped, "C", {"T", "Rank"}, {"T", "Rank"}),
    ExpandedT = Table.ExpandTableColumn(ExpandedC, "T", {"Control recipe", "Index"}, {"Control recipe", "Index"}),
    Sorted = Table.Sort(ExpandedT,{{"Index", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(Sorted,{"Index"})
in
    RemovedColumns