Create list of remaining processes

[paumac_tubing]

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.

[CNENFRNL]

Good to know List.Range does the trick in such a senario.

Cheers, guys!😀

[Jimmy801 · Accepted solution]

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
    Expand

But 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

[Crystal_YW]

@paumac_tubing 

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"

[Icey]

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.

[CNENFRNL]

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"

[paumac_tubing]

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

[CNENFRNL]

@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