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

Hi, Anonymous 

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    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
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Unfortunately, I have the same issue 

      • Anonymous's avatar
        Anonymous
        Not applicable
        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