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