Power Query function for adding column sequence number for the same rows-CodePudding

Is there a function for that, or how to make/add one? Sorry, I am new to the DAX function. Below is some function but don't know how to make Power Querry function from that.

Sequence = 
VAR CurrentID = TableX[Order]
RETURN

CALCULATE(
    COUNTROWS( TableX ),
    FILTER ( 
        ALL( TableX ),
        CurrentID = TableX[IOrder]
    )
)

Should look in Power query like this below.

Thanks in Advance.

CodePudding user response：

This is what I have tried,

• Select any cell in the table,

• From Data Tab, Under Get & Transform Data Group, Click From Table/Range

• This will import & open the table in Power Query Editor

• From Home Tab --> Under Transform Group --> Click Group By

• In Group By Window --> New Column Name --> All --> Operation --> All Rows --> OK

• From Add Column Tab --> Click on Custom Column • New Column Name --> Index

• Custom Column Formula

= Table.AddIndexColumn([All],"ID",1,1)

• Press Ok.

• Now click on the drop down in Index column & expand, select only ID

• Remove the column Named All,

• From Home Tab --> Click on Closed & Load To

• You shall get an Import Datastrong text Window --> Select either Existing Worksheet with the cell reference you want to have the output table or New Worksheet. Press Ok.

Or you can paste this M-Code in the Advance Editor in Power Query Editor -> Note that the Table Name should be TableX or if you have something else you can change it in the code.

let
    Source = Excel.CurrentWorkbook(){[Name="TableX"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Order"}, {{"All", each _, type table [Order=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([All],"ID",1,1)),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom", "Index", {"ID"}, {"ID"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Index",{"ID", "Order"})
in
    #"Removed Other Columns"