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....
Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    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 🤘

vmat's avatar
vmat
Frequent Visitor
3 years ago

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.

 

 

vmat's avatar
vmat
Frequent Visitor
3 years ago

AntrikshSharma 

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#Status0Status0NameStatus1Status1NameReturn OrderReturnOrder#Status2Status2NameCustomer CountryStatus3Status3NameStatus4Status4NameOrderTypeOrder Date
1000012500821Open31CancelledYes3837541ClosedDE51WIP61OnWay107/31/2022
1000022810422Open32InProcess  42DeliveredFR52Closed62Closed1010/2/2022
1000032773423Open33 Yes3603543 US53 63 208/8/2022
1000042656224Dropped34   44 CA54 64 408/25/2022
1000052809725Open35InProcess  45DeliveredCA55WIP65 3010/1/2022
1000062544926Open36 Yes3817646 ES56 66 309/9/2022
1000072675827Open37InProcess  47DeliveredMX57 67 209/21/2022
1000082664828Open38InProcessYes3889648CancelledIR58Closed68OnWay108/30/2022
1000092899829Open39ClosedYes3894149 UK59 69 307/25/2022
1000102627330Open40Rejected  50 DE60 70 308/13/2022