Create 'Status' using existing columns

[ChrisBroome]

I need to be able to reflect the 'status' of each row in my table, but can't work out how best to do this, suggestions would be helpful!

my data looks like this;

ID	Ready for Dev	Ready for test	Sign Off	Done
1	23/10/2020	24/10/2020	02/11/2020	05/11/2020
2	03/10/2020
3	09/10/2020	27/10/2020
4
5	07/11/2020	09/11/2020

I want to create the concept of a 'status' using this information as follows;

1. Backlog - where an ID has no dates in any of these columns

2. Ready for dev - Where an ID has a date only in the 'ready for dev' column

3. In Progress - Where an ID has a date either the 'ready for test' or 'sign off' columns

4. Done - Where an ID has a date in the 'done' column

Can anyone advise on a way of doing this? Should this be a custom column, or a new table perhaps?

[AlB]

Hi @ChrisBroome 

You can just add a custom column in PQ. Place the following M code in a blank query to see the steps. Make sure the source table has nulls where there are no dates, since the code looks for the nulls.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1jc00DcyMDIAcUyQOAZG+oaGcI4pnBOrE61kBBJC1qkAxyBpY5C0JbLB5hhqQepMUHTCRE1Bus2RLbdE4kDVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Ready for Dev" = _t, #"Ready for test" = _t, #"Sign Off" = _t, Done = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Ready for Dev", "Ready for test", "Sign Off", "Done"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"ID", Int64.Type}, {"Ready for Dev", type date}, {"Ready for test", type date}, {"Sign Off", type date}, {"Done", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if ([Ready for Dev] = null and [Ready for test] = null and[Sign Off]= null and [Done]= null)  then "Backlog" else if [Ready for Dev]<>null and ([Ready for test] = null and[Sign Off]= null and [Done]= null) then "Ready for Dev" else if [Done]<> null then "Done" else if ([Ready for test]<>null or [Sign Off]<>null) then "In Progress" else null)
in
    #"Added Custom"

Note the code for the custom column if you enter it through the GUI is

= if ([Ready for Dev] = null and [Ready for test] = null and[Sign Off]= null and [Done]= null)  then "Backlog" else if [Ready for Dev]<>null and ([Ready for test] = null and[Sign Off]= null and [Done]= null) then "Ready for Dev" else if [Done]<> null then "Done" else if ([Ready for test]<>null or [Sign Off]<>null) then "In Progress" else null

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Fowmy · Accepted solution]

@ChrisBroome 

Add the following as a custom column in PQ:

=if [Done] <> null then "Done" else if
[Ready for Dev] = null and [Ready for test]=null and [Sign Off] = null and [Done]= null then "Backlog" else if
[Ready for Dev] <> null and [Ready for test]=null and [Sign Off] = null and [Done]= null then "Ready for Dev " else if 
[Ready for Dev] <> null or [Sign Off] <> null then "Work in Progress" else  null

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn