Forum Discussion
Need help with Looping to get latest status
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 🤘
Community Championvmat That step is auto generated because I used the Enter Data option in Power BI, did you try the code with your data?
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)
- AntrikshSharma
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 🤘
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.
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