Forum Discussion
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# | 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 |
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
- AntrikshSharmaCommunity 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 - vmatFrequent 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,
- AntrikshSharmaCommunity 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?
- vmatFrequent 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)