Forum Discussion
Create list of remaining processes
So I have been working on a project that has me stumped
What i am looking to do is create a concatinated list of steps remaining. My data looks like this
part # step step description
123abc 001 cut
123abc 002 buff
123abc 003 mark
123abc 004 ship
My end resault that i am looking for is something like this
Part # Step Custom Column
123abc 001 buff,mark,ship
123abc 002 mark,ship
123abc 003 ship
123abc 004
Now there are other fields, as this data comes out of a SQL Database. I know how to create the concatinated list using group by, and how to put the if statement in. But, not how it would lookup the next value. Any help would be appreciated, Thank you.
Hello paumac_tubing
try this code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyTkxKVtJRMjAwApLJpSVKsToowoZAMqk0LQ1d3ARI5iYWZaOLGwPJ4ozMArC4hblZRWUVhvFIwijGI4mjGI8kjjA+FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"part #" = _t, step = _t, #"step description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"part #", type text}, {"step", type text}, {"step description", type text}}), Sort = Table.Buffer(Table.Sort(#"Changed Type",{{"part #", Order.Ascending}, {"step", Order.Ascending}})), Grouped = Table.Group(Sort, {"part #"}, {{"group", each _, type table [#"part #"=nullable text, step=nullable number, step description=nullable text]}}), AddList = Table.TransformColumns ( Grouped, { { "group", (tableint)=> let AddIndex = Table.AddIndexColumn(tableint,"Index",0), AddList = Table.AddColumn ( AddIndex, "Subsequent", (add) => try Text.Combine(List.Range(AddIndex[step description], add[Index]+1), ", ") otherwise "" ) in AddList } } ), Expand = Table.ExpandTableColumn(AddList, "group", {"step", "step description", "Subsequent"}, {"step", "step description", "Subsequent"}) in ExpandBut i realized just now that somebody else was working on the similar approach in the same time 🙂
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
7 Replies
Hi, paumac_tubing , pls refer to the following code to achieve your goal. Btw, I use a bit more mockup data containing more part # in an arbitary order; so that the code applies to such a senario.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyTkxKVtJRMjAwBJLJpSVKsToowkZAMqk0LQ1d3BhI5iYWZaOLmwDJ4ozMArC4hblZRWUV3ByY8UjChsjGI4mbIBuPJG4MNz4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"part #" = _t, step = _t, #"step description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"part #", type text}, {"step", Int64.Type}, {"step description", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"part #", Order.Ascending}, {"step", Order.Ascending}}), Grouped = Table.Group(#"Sorted Rows", {"part #"}, {{"group", each _, type table [#"part #"=nullable text, step=nullable number, step description=nullable text]}}), Custom1 = Table.AddColumn( #"Sorted Rows", "Subsequent", (x) => [ tb = Grouped{[#"part #" = x[#"part #"]]}[group], StrList = List.Generate( () => [str = tb[step description]{x[step]}?, counter = x[step]], each not (tb[step description]{[counter]}? is null), each [str = tb[step description]{counter}, counter = [counter] + 1], each [str] ) ][StrList] ), #"Extracted Values" = Table.TransformColumns(Custom1, {"Subsequent", each Text.Combine(List.Transform(_, Text.From), ","), type text}) in #"Extracted Values"Ok, so i see where you are going but this was the issue i was running into either it lists every step description for every step for every part# or i get hit and miss entries that are not right.
Also im not really following what you are trying to do here
paumac_tubing , hmm... it seems that in real senario, the step column doesn't consist of consecutive cardinal number like 01,02,03... In my previous solution to the mockup dataset, I use these 1,2,3... in the step column to iterate all rows of a certain part #.
The general idea of my solution is as follows,
1. sort dataset by part # and then step;
2. grouping dataset by part #, let's say, named as Grouped, like an intermediate table in SQL;
3. for each step in the dataset, fetch sbusequent steps from Grouped with List.Generate fx.
If you paste some snippet of real senario data, original columns' names included, I can tweak my preceding solution
Hi paumac_tubing ,
How about creating a calculated column? It is much easier.
Column = CONCATENATEX ( FILTER ( 'Table', 'Table'[part #] = EARLIER ( 'Table'[part #] ) && 'Table'[step] > EARLIER ( 'Table'[step] ) ), [step description], ", " )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi, I think I've solved your issues as follows with the help of index column:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Step", type text}, {"Step description", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Part", Order.Ascending}, {"Step", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
AddRemaingStep = Table.AddColumn(#"Added Index","RemainingSteps",each Text.Combine(
List.Range(#"Added Index"[Step description],[Index]+1,
List.Max(List.PositionOf(#"Added Index"[Part],[Part],Occurrence.All))-[Index]),
","
)),
#"Removed Columns" = Table.RemoveColumns(AddRemaingStep,{"Index"})
in
#"Removed Columns"Hello paumac_tubing
try this code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyTkxKVtJRMjAwApLJpSVKsToowoZAMqk0LQ1d3ARI5iYWZaOLGwPJ4ozMArC4hblZRWUVhvFIwijGI4mjGI8kjjA+FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"part #" = _t, step = _t, #"step description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"part #", type text}, {"step", type text}, {"step description", type text}}), Sort = Table.Buffer(Table.Sort(#"Changed Type",{{"part #", Order.Ascending}, {"step", Order.Ascending}})), Grouped = Table.Group(Sort, {"part #"}, {{"group", each _, type table [#"part #"=nullable text, step=nullable number, step description=nullable text]}}), AddList = Table.TransformColumns ( Grouped, { { "group", (tableint)=> let AddIndex = Table.AddIndexColumn(tableint,"Index",0), AddList = Table.AddColumn ( AddIndex, "Subsequent", (add) => try Text.Combine(List.Range(AddIndex[step description], add[Index]+1), ", ") otherwise "" ) in AddList } } ), Expand = Table.ExpandTableColumn(AddList, "group", {"step", "step description", "Subsequent"}, {"step", "step description", "Subsequent"}) in ExpandBut i realized just now that somebody else was working on the similar approach in the same time 🙂
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyGood to know List.Range does the trick in such a senario.
Cheers, guys!😀
