Forum Discussion

paumac_tubing's avatar
paumac_tubing
Regular Visitor
5 years ago
Solved

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 descript...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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's avatar
paumac_tubing
Regular Visitor
5 years ago

 

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's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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