Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power Query Date checks

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 n...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Anonymous 

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, Anonymous 

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.