Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group