Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all -
Further to another issue yesterday around date formatting and null values I am struggling with the next step 🙄
I want to add a conditional column which looks at one text column and one date column and returns possible 3 values.
This is what I am trying to achieve -
If action_completed_flag is Y = null
else if action_target_date is before today = Action overdue
else if action_target_date is after today = Action current
2 issues
1 - cannot get any type of TODAY/LOCALNOW function to work at all
2 - error returns because of the null values in some of the action_target_date rows
HOWEVER - i have tested this on a set date (31/12/2022) using a date field which has no nulls and it works - so I know I must be on the right tracks but cannot make it work 😞
Heres what I've used to test it - sorry if its messy, total beginner here 🙂
= Table.AddColumn(#"Added Custom3", "Custom", each if [action_completed_flag] = "Y" then null else if [priority_date] < #datetime(2021, 12, 31, 0, 0, 0) then "Overdue " else if [priority_date] > #datetime(2021, 12, 31, 0, 0, 0) then "Current" else null)
Thanks!!!!
Solved! Go to Solution.
Hi @Googlecanthelp ,
Use the following to get the today first:
= Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow())
Then custom column:
= Table.AddColumn(#"Changed Type1", "Custom.1", each if [priority_date]=null then null else if[action_completed_flag]="Y" then null else if [priority_date]<[Custom] then "Overdue" else if [priority_date]>=[Custom] then "Current" else null)
All the content:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLSN9I3NASylWJ1opWckASNYILOIIaOUiSY44JQYWQMU+GKJGgKE3RD1uYO4YDZHths9kSzGaLNC0mbNzabfTBsjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, priority_date = _t, action_completed_flag = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"priority_date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [priority_date]=null then null else if[action_completed_flag]="Y" then null else if [priority_date]<[Custom] then "Overdue" else if [priority_date]>=[Custom] then "Current" else null)
in
#"Added Custom1"
Final output:
You could download my pbix file if you need.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @Googlecanthelp ,
Use the following to get the today first:
= Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow())
Then custom column:
= Table.AddColumn(#"Changed Type1", "Custom.1", each if [priority_date]=null then null else if[action_completed_flag]="Y" then null else if [priority_date]<[Custom] then "Overdue" else if [priority_date]>=[Custom] then "Current" else null)
All the content:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLSN9I3NASylWJ1opWckASNYILOIIaOUiSY44JQYWQMU+GKJGgKE3RD1uYO4YDZHths9kSzGaLNC0mbNzabfTBsjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, priority_date = _t, action_completed_flag = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"priority_date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [priority_date]=null then null else if[action_completed_flag]="Y" then null else if [priority_date]<[Custom] then "Overdue" else if [priority_date]>=[Custom] then "Current" else null)
in
#"Added Custom1"
Final output:
You could download my pbix file if you need.
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |