Add a column with steps based on number of entries in a column

[Greg888]

I have a bit of strange problem, which would be easily solved in SQL using RowNumber and order by but I am looking for something similar in Power BI 

I have a document workflow but the steps in the workflow can vary from document to document, I just want to add a column to show how many steps the document has been through. Example of how I would like to 

Document Number	Step
001	1
001	2
001	3
001	4
002	1
002	2
003	1
003	2
003	3
003	4

Etc... 

[Greg888]

Perhaps I should have said the docnumbers contain Text so 

P001

L002

G003

for instance

I think this is what is causing the error

[amitchandak]

@Greg888 ,

first add an index column in power query 

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

then add a new column in dax

countx(filter(Table, [Document] =earlier([Document]  && [Index] <= earlier([Index]) ), [index] )

[Greg888]

Sadly it didn't work.


Steps = COUNTX(FILTER(DocStepSequence,DocStepSequence[DocNumber] = EARLIER(DocStepSequence,DocStepSequence[DocNumber] && DocStepSequence,DocStepSequence[DocNumber_ID] <= EARLIER(DocStepSequence,DocStepSequence[DocNumber_ID],DocStepSequence,DocStepSequence[DocNumber_ID])

too Many arguments passed to the FILTER functions 

[v-easonf-msft · Accepted solution]

Hi, @Greg888 

You can try add index column within a Group with Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwMFSK1cHL8DEwMEJluBsYGONlxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Number", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Document Number"}, {{"Count", each _, type table [Document Number=nullable text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Rank",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Document Number", "Rank"}, {"Document Number", "Rank"})
in
    #"Expanded Custom"

For more details, please refer to this tutorial.

Ranking within a Group with Power Query 

Best Regards,
Community Support Team _ Eason