Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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.
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