Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
justlogmein
Helper III
Helper III

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

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...

 

 

 

1 REPLY 1
v-jingzhang
Community Support
Community Support

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.

21122304.jpg

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors