Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rosalind90
Frequent Visitor

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 index would help me here.

 

In my sample data (links to a google sheet), I have groups of robots, and different robots are at different steps within each group.

Ultimately, I need to find the earliest step in progress in each group is currently on, and return that in a new column.

For example:

 

Red 1 of the Red group is on step 1, "Measure Coffee Beans". Although Red 4 is on step 4, "Boil Water", the group status would still be "Measure Coffee Beans".

 

 Any advice would be great. Thanks!

1 ACCEPTED SOLUTION

 

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.

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

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

dufoq3_0-1714455831446.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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!

lbendlin
Super User
Super User

Read about TOPN or INDEX - that can give you the first row for each group, and then you can grab the step name for that row.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you for letting me know, I've updated my post with a link, and hopefully clarified some things. TopN has gotten me closer to solving this, but doesn't seem quite specific enough.

 

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.

This did the trick! Thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors