Power Query Date checks

[lukmtb08]

Hi community, 
I have the following columns. Could you please define the Power Query M code to create the column "Check Start"?

Actual Start	Planned Start	Check Start
null	null	0
null	2/2/2023 (could be any date before today, or today)	0
null	2/2/2023 (could be any date after today)	1
2/2/2024	2/2/2024	0
2/2/2024	1/2/2024	1
2/2/2024	null	0

[lukmtb08]

Thanks again! 🙂 

I still have an error, when Actual Start = null and Planned Start > Today. 

[v-jianpeng-msft]

Hi, @lukmtb08 

Thank you very much for your reply, I added this condition to the original M code, and the latest M code is below:

test = Table.AddColumn(#"Changed Type","Check start1",each 
    if [Actual Start] = null and [Planned Start] =null then 0
    else if [Actual Start] = null and [Planned Start]<= Date.From(DateTime.LocalNow()) then 1
    else if [Actual Start] > [Planned Start] and [Planned Start] <> null then 1
    else if [Actual Start] <> null and [Planned Start] = null then 0
    else if [Actual Start] = null and [Planned Start] > Date.From(DateTime.LocalNow()) then 0
    else 0
    )

[lbendlin]

What's the logic behind that column?

[lukmtb08]

If Actual Start > Planned Start: 1
If Actual Start = null and Planned Start <= Todays date: 1
Elso 0

[lbendlin]

Your explanation and your sample column don't seem to match

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSgVEGSrE6cDEjfSA0MDLGIW4KZBqCxaECJgg5E7geJAFDJCa6HML+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Actual Start" = _t, #"Planned Start" = _t, #"Check Start" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual Start", type date}, {"Planned Start", type date}, {"Check Start", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Check", each if ([Actual Start]??Date.From(DateTime.FixedLocalNow())) > ([Planned Start]??Date.From(DateTime.FixedLocalNow())) then 1 else 0)
in
    #"Added Custom"

[v-jianpeng-msft]

Hi, @lukmtb08 

Based on your description, I use the following Sample data:

I add a new column with the following M code:

    test = Table.AddColumn(#"Changed Type","Check start1",each 
    if [Actual Start] = null and [Planned Start] =null then 0
    else if [Actual Start] = null and [Planned Start]<= Date.From(DateTime.LocalNow()) then 1
    else if [Actual Start] > [Planned Start] and [Planned Start] <> null then 1
    else 0
    )

The execution result is as follows:

I have provided the PBIX file below, it would be great if it could help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukmtb08]

Dear @v-jianpeng-msft, thank you very much for supporting me - highly appreciated!
Generally it works, but for some rows I get the following error in my dataflow: 

Expression.Error: We cannot convert the value null to type Logical.

Details

Reason = Expression.Error

[v-jianpeng-msft]

Hi, @lukmtb08 

Thank you very much for your reply, I used the following data, you can copy and paste into Excel, then save your Excel, and set it up as below in Power BI Desktop:

Actual Start	Planned Start	Check Start
null	null	0
null	2/2/2023	0
null	2/2/2023	1
2/2/2024	2/2/2024	0
2/2/2024	1/2/2024	1
2/2/2024	null	0

[lukmtb08]

thanks! In the dataflow, I get for some rows the follwing error: 

Expression.Error: We cannot convert the value null to type Logical.

Details

Reason = Expression.Error

[v-jianpeng-msft]

Hi, @lukmtb08 

Thank you very much for your reply, this issue may be due to a missing condition. I rewrote an M code that returns 0 when the start of the plan is equal to empty and the actual start is not equal to empty:

 test = Table.AddColumn(#"Changed Type","Check start1",each 
    if [Actual Start] = null and [Planned Start] =null then 0
    else if [Actual Start] = null and [Planned Start]<= Date.From(DateTime.LocalNow()) then 1
    else if [Actual Start] > [Planned Start] and [Planned Start] <> null then 1
    else if [Actual Start] <> null and [Planned Start] = null then 0
    else 0
    )

[lukmtb08]

Unfortunately, I have the same issue 

[v-jianpeng-msft · Accepted solution]

Hi, @lukmtb08 

Thank you very much for your reply. Sorry for the late reply. Has your problem been solved now? Maybe you can try the following code:

test = Table.AddColumn(#"Changed Type","Check start1",each 
    if [Actual Start] = null and [Planned Start] =null then 0
    else if [Actual Start] = null and [Planned Start]<= Date.From(DateTime.LocalNow()) then 1
    else if [Actual Start] > [Planned Start] and [Planned Start] <> null then 1
    else if [Actual Start] <> null and [Planned Start] = null then 0
    else if [Actual Start] = null and [Planned Start] > Date.From(DateTime.LocalNow()) then 0
    else if [Actual Start] = null and [Planned Start] < Date.From(DateTime.LocalNow()) then 0
    else 0
    )

Best Regards

Jianpeng Li