Forum Discussion

Rosalind90's avatar
Rosalind90
Frequent Visitor
2 years ago
Solved

How to represent a step-by-step process with rank or index

Hi, I have a step-by-step process I need to represent in a table, in multiple ways. I essentially need a way to assign numerical values to text, but that isn't possible. I'm not sure if rank or inde...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldK9DoMgEAfwV7kwuwj2AdqlU5PuxkHLaUwIJKfU+PYlQhuxVuvAx/Dj8ucgz9kNy84SwsPUNSJUWOqOJSx1g1D6GVJWJDm7UqvlEvII8gmepYSGjNWyg95A3aoeyQkRWTHZi2kVDKUHWQSyCdyNJQ/APN0UCjtzcqNSFsMSUn4VnBN+sKTYvvic+rQb7WwIUb/XEHarU7Hf72zsxbE4//R6RKXM8NnsXIGvnODrL7RkP37GkrnMxQs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Step Names" = _t, #"Step Order" = Int64.Type, Groups = _t, #"ROBOT NAME" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Group Status", (k)=> let lm=List.Min(Table.SelectRows(Source, each [Groups]=k[Groups])[Step Order])
in Table.SelectRows(Source, each [Groups]=k[Groups] and [Step Order]=lm)[Step Names]{0},type text)
in
    #"Added Custom"

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi Rosalind90, check this.

 

You can delete Ad_SortHelper, SortedRows and RemovedColumns steps if you have huge dataset and do not need to preserve sort order (without such steps the query will be faster).

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvSk3NU4rViVZyQuE5o/BcUHggfU45palwbXCOMzLHBc6JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Step Names" = _t, Group = _t]),
    Ad_SortHelper = Table.AddIndexColumn(Source, "SortHelper", 0, 1, Int64.Type),
    GroupedRows = Table.Group(Ad_SortHelper, {"Step Names"}, {{"All", each 
        [ a = Table.AddIndexColumn(_, "IndexHelper", 1, 1),
          b = Table.AddColumn(a, "Robot Name", (x)=> x[Group] & Text.From(x[IndexHelper]), type text),
          c = Table.RemoveColumns(b, {"IndexHelper"})
        ][c], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    SortedRows = Table.Sort(CombinedAll,{{"SortHelper", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(SortedRows,{"SortHelper"})
in
    RemovedColumns
Rosalind90's avatar
Rosalind90
Frequent Visitor
2 years ago

This almost got it! I needed the names of the earliest steps returned instead of the numbers. I learned a ton about creating, accesing, and hiding columns with this though. Thanks so much!