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

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"