Assigning a status to a process (Dax or PQ)

Power Query
1

Hi there,

There are about 164 “DMAIC Projects” where I need to answer the question visually “How many projects are in each process and how many days have they been there?”. I’ve looked quite a bit at challenge 6 (insurance complaints). Unfortunately, my data is a whole lot messier. So, I’ve simplified as per the image below.

What I need to do:
Is calculate the STATUS of each process for a project, that is:

A. Status=DONE, if a process has an end date
B. Status= IN PROGRESS, if a process does not have and end date AND it’s prior process does
C. Status= “”, if a process doe not have an end date AND it’s prior process does not have an end date either

Question 1:
Is this at all possible in Power Query (my first preference)?

Question 2:
I’m assuming the answer to question 1 above is… umm no. So, then could someone please help me with the Dax for (B) ? (I will then figure out the DAX for C and A is easy enough).

Question 3:
Assuming I have to go the Dax route, is there any limitations to now having a DAX measure as apposed to a nice calculated column?

3
31210×928 342 KB

Thanks in advance,
Michelledmaic_projects.xlsx (9.9 KB)

2

Hi @michellepace,

Copy this M code to a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBwVNJRMgRhE93c1EylWB2YoBEIG6AJGgMxEtcElWuK4DpBjTXXzSrNQxIDmWqBJgYy1BJNDGSyoQGaIMT8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Process = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Process", Int64.Type}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"AllRows", each _, type table [Project=nullable text, Process=nullable number, End Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        let
            myTable = Table.Buffer( [AllRows] ),
            SortRows = Table.Sort(myTable,{{"Process", Order.Ascending}}),
            AddStatus = Table.AddColumn(SortRows, "Status", each if [End Date] is date then "Done" else if SortRows{[Process]-2}[End Date] is date then "In Progress" else null, type text),
            AddDays = Table.AddColumn(AddStatus, "Days in Status", each if [Process] =1 then null else if [Status] = "Done" or [Status] = "In Progress" then 
                List.Count(
                    List.Select( 
                        //List.Difference( 
                            List.Dates(SortRows{[Process]-2}[End Date], Number.From( (if [End Date]=null then Date.From(DateTime.FixedLocalNow()) else [End Date]) - SortRows{[Process]-2}[End Date] ) +1, Duration.From(1)),
                        //List.Transform(HolidayList, Date.From )), 
                    each Date.DayOfWeek(_, 1) < 5 )
                ) else null, type number )
        in
            AddDays, type table [Project=nullable text, Process=nullable number, End Date=nullable date, Status = text, Days in Status = number])[[Custom]],
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Project", "Process", "End Date", "Status", "Days in Status"}, {"Project", "Process", "End Date", "Status", "Days in Status"})
in
    #"Expanded Custom"

.
Thought I would also look at the status duration. Although I can’t determine that for the first Status because there is no start date, for “In Progress” it counts the number of working days between the “previous status end date” and today. So at the moment sat and sun are excluded and both start- and end date are included in the “Days in Status” count.

It should generate a Query with this result.

image
image852×243 12 KB

I hope this is helpful.

3 Likes
3

Hi Melissa, thank you very much for your reply. I tried your query on my data and hit a glitch. I did not realise it, but projects are able to “skip process steps.” For example, look at project 163 below on process 2:

a
a856×292 14.1 KB

I think I need to make this easier (and loose some of my information). But easier nonetheless.

Question:
Is it possible to use PowerQuery to get to the answer of “For each project, what is the max. process which has an end date?” From there I’ll know the project is in the next process, or if max.process=5 then closed out. That is, I think I need to get to a result table like so:

Project | Max.Process with End Date | calculate current process | calculate days in current process

In SQL, I think it would be something like this to get to the above…
xx

4

Hi Michelle,

Sure I think we can work off the Max Process and keep all the data, give this a go.
I’m returning “Done” for skipped steps and “Days in Status” should return null

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBwVNJRMgRhE93c1EylWB2YoBEIG6AJGgMxEtcElWuK4DpBjTXXzSrNQxIDmWqBJgYy1BJNDGSyoQGaIMT8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Process = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Process", Int64.Type}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"AllRows", each _, type table [Project=nullable text, Process=nullable number, End Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        let
            myTable = [AllRows],
            SortRows = Table.Sort(myTable,{{"Process", Order.Ascending}}),
            AddIndex = Table.AddIndexColumn( SortRows, "Index", 0, 1 ),
            MaxProcess = List.Max( Table.SelectRows(SortRows, each [End Date] <>null )[Process] ),
            ListEndDates = Table.FillDown( AddIndex[[End Date]], {"End Date"} )[End Date],
            AddStatus = Table.AddColumn(AddIndex, "Status", each if [Process] <= MaxProcess then "Done" else if AddIndex{[Index]-1}[End Date] is date then "In Progress" else null, type text),
            AddDays = Table.AddColumn(AddStatus, "Days in Status", each if [Index] =0  or ( [Status] = "Done" and [End Date] =null ) then null else if [Status] = "Done" or [Status] = "In Progress" then 
                List.Count(
                    List.Select( 
                        //List.Difference( 
                            List.Dates(ListEndDates{[Index]-1}, Number.From( (if [End Date]=null then Date.From(DateTime.FixedLocalNow()) else [End Date]) - ListEndDates{[Index]-1} ) +1, Duration.From(1)),
                        //List.Transform(HolidayList, Date.From )), 
                    each Date.DayOfWeek(_, 1) < 5 )
                ) else null, type number )
        in
            AddDays, type table [Project=nullable text, Process=nullable number, End Date=nullable date, Status = text, Days in Status = number])[[Custom]],
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Project", "Process", "End Date", "Status", "Days in Status"}, {"Project", "Process", "End Date", "Status", "Days in Status"})
in
    #"Expanded Custom"

I hope this is helpful.

1 Like
5

Hi @michellepace, we’ve noticed that no response has been received from you since the 7th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

6

Hi Melissa, sorry for the delayed response. It never ceases to amaze me the extent of your programming skills - thank you !

In the end I needed a sustainable solution not just tied into a report, but into the actual model. I got some help from someone much better than I to do the same in SQL and then into a view.

But thank you nonetheless as you have once again taught me a lot by your reply.

Best wishes as always