Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisBroome
Frequent Visitor

Create 'Status' using existing columns

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;

 

IDReady for DevReady for testSign OffDone
123/10/202024/10/202002/11/202005/11/2020
203/10/2020   
309/10/202027/10/2020  
4    
507/11/202009/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?

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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 

SU18_powerbi_badge

Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors