Create an index column for the order of rows based on Next Step and Last Step columns

[justlogmein]

There are two things I am trying to do in Power Query here. The first is to create a numbered index for each part number in the list. The second is to create an Order column which follows the logic of two other columns, Next Step and Last Step (actually only need one really). I just want to filter ascending on the Order colum so that it list all of the steps in the correct order, from start to finish.

This is best shown in the example in the link.

https://jmservicescomau-my.sharepoint.com/:x:/g/personal/jacob_jmservices_com_au/EVh0WcdQEuFHolllafq...

[v-jingzhang]

Hi @justlogmein 

The first index column is easy to add. You can first group by PARTID column and select All Rows for operation. Then add an Index column starting from 0. Finally expand AllData column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVU5bsQwDPxK4HoLUZftMvOC9Ist8oUg/0fWlLzUjmCmoQGPeQ2H9P2+fH3//H58LrcllKK2qo3L4zaCEnZ9xMOuBNagVr8oO4FZXXI+bGKw7Ob/zEw5FYwKysaghpV0Fsflbq3QIzphUdTGHpwclQRREiQTuB+eGpfdWnt56w2/g+tIYOBGGm1r6ESQa3jlPT6kWvNQceIug/Uqk+faOW29ErgZ+cKjbi2Wc6aUU1+n0jPTqKOJLE/TrENZhcM24hooTFHaTWWJXesgBpl0K2fE51QJSQPB8y6sr9gTJsFKZgaqSb5wpY211kwcouJSePC2C//GvKoGLm/wVAl3WvCWD56g4e0JPHHBUzvc5cSlQuApBMbqfNbgrRCshfkIw9tMWLb5CGMYJd9DOJJF78x6DdOoL24MvJWGdyngbTQGdUwHGuMc+S8FZ21hnM6/Gth22XF//AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARTID = _t, #"Process Step ID" = _t, #"Next Step" = _t, #"Last Step" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PARTID", type text}, {"Process Step ID", Int64.Type}, {"Next Step", Int64.Type}, {"Last Step", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PARTID"}, {{"AllData", each _, type table [PARTID=nullable text, Process Step ID=nullable number, Next Step=nullable number, Last Step=nullable number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Added Index", "AllData", {"Process Step ID", "Next Step", "Last Step"}, {"Process Step ID", "Next Step", "Last Step"})
in
    #"Expanded AllData"

For the second Order column, I haven't come up with a good idea to do it. 

Best Regards,
Community Support Team _ Jing