Forum Discussion

vmat's avatar
vmat
Frequent Visitor
3 years ago
Solved

Need help with Looping to get latest status

Hello.

I am new to POwer BI and need help with a query. I have a table where we maintain Order status as a Column. So at every stage of an Order a new Column is created containing the latest status.. E.g. Status0="Open", Status1="In Process", etc.. where Status0, Status1, Status2 are created dynamically so the number of columns in table is not fixed. 

 

I need a way to read the latest status for each of the Order, add it to a new column "Latest Status" so that I have one column which has the latest status of the Order.

 

Order#Status0Status1Status2Status3Latest Status?
100001OpenCancelledClosed Closed
100002OpenInProcessDeliveredClosedClosed
100003Open   Open
100004Dropped   Dropped
100005OpenInProcessDelivered Delivered
100006Open   Open
100007OpenInProcessDelivered Delivered
100008OpenInProcessCancelledClosedClosed
100009Open   Open
100010Open   Open
  • vmat Nope you are making some mistake here you only need to use the Result part of the code. I tried by adding new column (Status4, Status5..) and it is working for me 🤘

7 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion

    vmat Paste this in the advanced editor:

    let
        Source = Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjQAAkMlHSX/gtQ8IOWcmJecmpOTmgJi5+QXgxkKSrE6UKVGCKWeeQFF+cmpxcVAtktqTmZZahGyNrgWY4QWBTiGy5qAdBflFxRAbcJQYErYRiTVZngtMyfJLAvsqrEEEVyLJT7rDQ2wysYCAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ #"Order#" = _t, Status0 = _t, Status1 = _t, Status2 = _t, Status3 = _t ]
        ),
        Result = Table.AddColumn (
            Source,
            "Custom",
            each
                let
                    RecordToTable = 
                        Table.SelectRows (
                            Table.Skip ( Record.ToTable ( _ ) ),
                            each [Value] <> " "
                        ),
                    NewCol = 
                        Table.AddColumn (
                            RecordToTable,
                            "Num",
                            each Number.From ( Text.End ( _[Name], 1 ) )
                        ),
                    MaxNum = List.Max ( NewCol[Num] ),
                    Result = Table.SelectRows ( NewCol, each [Num] = MaxNum )[Value]{0}
                in
                    Result,
            type text
        )
    in
        Result
  • vmat's avatar
    vmat
    Frequent Visitor

    Thanks AntrikshSharma for the quick reply. 

    I see you are using the 4 status (columns mentioned) in the table. But my problem is that the status keep increasing as the Order status changes, so I can have status5,status6, status7 and so on.... 

     

    So, the first part of the problem is to find all the columns beginning with status and then for each row find the latest status.

     

    THanks,

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion

      vmat That step is auto generated because I used the Enter Data option in Power BI, did you try the code with your data?

      • vmat's avatar
        vmat
        Frequent Visitor

        Yes,I did. I now inserted one more column "Status4" in my Order Table, but your query considered only first 4 status (till status3). So, the query ignore the status from Last column (Status4)