Power Query with dates

[Anonymous]

What do i have wrong here... trying to say if less than today

et
Source = Excel.Workbook(File.Contents("X:\DEPT\D10\Supply Chain NP.xlsx"), null, true),
CurrentDate = Date.From(DateTime.FixedLocalNow()),
#"Sabrina File_Sheet" = Source{[Item="Sabrina File",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sabrina File_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales Order #", type text}, {"PO", type text}, {"Delivery #", type text}, {"Load Number", type text}, {"SAP Create Date", type datetime}, {"BJ Create Date", type datetime}, {"Pick Location Name", type text}, {"Carrier Name", type text}, {"Transportation Mode", type text}, {"Equipment Type", type text}, {"Load Payable Total", type text}, {"Customer", type text}, {"Ship-to Number", type text}, {"Ship-to City", type text}, {"Ship-to State", type text}, {"Days to MABD", Int64.Type}, {"MABD", type date}, {"Days Late to Pick", Int64.Type}, {"Days Late to Drop", Int64.Type}, {"Pick Rescheduled or Not", type text}, {"Delivery Rescheduled or Not", type text}, {"Minutes Early/Late to Final Pick Appt", Int64.Type}, {"Minutes Early/Late to Final Drop Appt", Int64.Type}, {"Original Pickup Appt", type datetime}, {"Final Pickup Appt", type datetime}, {"Pickup Arrival", type datetime}, {"Pickup Departure", type datetime}, {"Original Delivery Appt", type datetime}, {"Final Delivery Appt", type datetime}, {"Delivery Arrival", type datetime}, {"Delivery Departure", type datetime}, {"PickedQ", type text}, {"Delivered", type text}, {"Load Pick Plan Date Start", type datetime}, {"Load Drop Plan Date Start", type datetime}, {"SCAC", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [MABD] > #date(2021, 12, 31)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if [SCAC] = "CPUP" and [Final Delivery Appt] = null then "Pick Required"

else if [SCAC]<> "CPUP" and [Final Delivery Appt]> CurrentDate and [Delivery Departure]<> null and [Pickup Departure] <> null then "Pickup Required"

else if [Final Pickup Appt] < CurrentDate and [Delivery Departure] and [Pickup Departure] <> null then "Pickup Appointment Missed"

else "Pickup Scheduled"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Pickup Required2"}})
in
#"Renamed Columns"

[Vijay_A_Verma]

You are comparing DateTime field type to Date field which is not permitted.

CurrentDate = Date.From(DateTime.FixedLocalNow()) --- This gives a date.

You are comparing [Final Delivery Appt] and [Final Pickup Appt] to CurrentDate and these fields are DateTime.

You can do following

1. Either you can convert all DateTime fields to Date type during Changed Type step

2. Or you can put following in your conditions

Date.From([Final Delivery Appt])> CurrentDate

Date.From([Final Pickup Appt]) < CurrentDate

[Anonymous]

I am very new to Mcode so i am not quite following.  When you say conditions is that in the if statement section?

[Vijay_A_Verma · Accepted solution]

Paste this code into your Advanced Editor. I put in Date.From at two places where Current date is being referred to

let
Source = Excel.Workbook(File.Contents("X:\DEPT\D10\Supply Chain NP.xlsx"), null, true),
CurrentDate = Date.From(DateTime.FixedLocalNow()),
#"Sabrina File_Sheet" = Source{[Item="Sabrina File",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sabrina File_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales Order #", type text}, {"PO", type text}, {"Delivery #", type text}, {"Load Number", type text}, {"SAP Create Date", type datetime}, {"BJ Create Date", type datetime}, {"Pick Location Name", type text}, {"Carrier Name", type text}, {"Transportation Mode", type text}, {"Equipment Type", type text}, {"Load Payable Total", type text}, {"Customer", type text}, {"Ship-to Number", type text}, {"Ship-to City", type text}, {"Ship-to State", type text}, {"Days to MABD", Int64.Type}, {"MABD", type date}, {"Days Late to Pick", Int64.Type}, {"Days Late to Drop", Int64.Type}, {"Pick Rescheduled or Not", type text}, {"Delivery Rescheduled or Not", type text}, {"Minutes Early/Late to Final Pick Appt", Int64.Type}, {"Minutes Early/Late to Final Drop Appt", Int64.Type}, {"Original Pickup Appt", type datetime}, {"Final Pickup Appt", type datetime}, {"Pickup Arrival", type datetime}, {"Pickup Departure", type datetime}, {"Original Delivery Appt", type datetime}, {"Final Delivery Appt", type datetime}, {"Delivery Arrival", type datetime}, {"Delivery Departure", type datetime}, {"PickedQ", type text}, {"Delivered", type text}, {"Load Pick Plan Date Start", type datetime}, {"Load Drop Plan Date Start", type datetime}, {"SCAC", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [MABD] > #date(2021, 12, 31)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if [SCAC] = "CPUP" and [Final Delivery Appt] = null then "Pick Required"

else if [SCAC]<> "CPUP" and Date.From([Final Delivery Appt])> CurrentDate and [Delivery Departure]<> null and [Pickup Departure] <> null then "Pickup Required"

else if Date.From([Final Pickup Appt]) < CurrentDate and [Delivery Departure] and [Pickup Departure] <> null then "Pickup Appointment Missed"

else "Pickup Scheduled"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Pickup Required2"}})
in
#"Renamed Columns"

[Anonymous]

Thank you for your help but i am still getting an error.  Is it maybe a format issue?  The Date field does have time as well.

[Vijay_A_Verma]

Please upload your Excel sheet with 2 to 3 line of data with all sensitive values changed to Onedrive / any other file host and paste the link here.