Need help with Looping to get latest status

[Syndicate_Admin]

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#	Status0	Status1	Status2	Status3	Latest Status?
100001	Open	Cancelled	Closed		Closed
100002	Open	InProcess	Delivered	Closed	Closed
100003	Open				Open
100004	Dropped				Dropped
100005	Open	InProcess	Delivered		Delivered
100006	Open				Open
100007	Open	InProcess	Delivered		Delivered
100008	Open	InProcess	Cancelled	Closed	Closed
100009	Open				Open
100010	Open				Open

[Syndicate_Admin]

@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

[Syndicate_Admin]

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,

[Syndicate_Admin]

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

[Syndicate_Admin]

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)

[Syndicate_Admin]

@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 🤘

[Syndicate_Admin]

Hmm. ok.. not sure what wrong am I doing. but following the 2 steps..

I export this table from an Excel file, and then go to Advanced Editor and Copy the entire code your provided.

I also tried creating a custom column and pasted only the Result part of the code, but gave the below error.

[Syndicate_Admin]

Ok, So I was able to make the code work by replacing the " " with null as the code was looking for blanks while Power BI converted the blank values to null. So  now I have the latest status in the last column.

Now i have another complexity. There are more columns in the table and not just the Order# and Status. The Status column follows this convention.. Status_0_Name,Status_1_Name,Status_2_Name, etc.. So now it makes it more complex to first find all the Status columns which start with Status and end with Name and to find them in a table with many other columns which are not in any sequence. posting a sample here... 

How can I improvise this query to read only columns with Status*Name and get the latest status?

Order#	Customer#	Status0	Status0Name	Status1	Status1Name	Return Order	ReturnOrder#	Status2	Status2Name	Customer Country	Status3	Status3Name	Status4	Status4Name	OrderType	Order Date
100001	25008	21	Open	31	Cancelled	Yes	38375	41	Closed	DE	51	WIP	61	OnWay	10	7/31/2022
100002	28104	22	Open	32	InProcess			42	Delivered	FR	52	Closed	62	Closed	10	10/2/2022
100003	27734	23	Open	33		Yes	36035	43		US	53		63		20	8/8/2022
100004	26562	24	Dropped	34				44		CA	54		64		40	8/25/2022
100005	28097	25	Open	35	InProcess			45	Delivered	CA	55	WIP	65		30	10/1/2022
100006	25449	26	Open	36		Yes	38176	46		ES	56		66		30	9/9/2022
100007	26758	27	Open	37	InProcess			47	Delivered	MX	57		67		20	9/21/2022
100008	26648	28	Open	38	InProcess	Yes	38896	48	Cancelled	IR	58	Closed	68	OnWay	10	8/30/2022
100009	28998	29	Open	39	Closed	Yes	38941	49		UK	59		69		30	7/25/2022
100010	26273	30	Open	40	Rejected			50		DE	60		70		30	8/13/2022