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

Thanks again! 🙂 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    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
    )