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

lbendlin's avatar
lbendlin
Super User
2 years ago

What's the logic behind that column?

  • 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Dear Anonymous, 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
    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      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"