Forum Discussion

TimberlyneJohn's avatar
TimberlyneJohn
Frequent Visitor
1 year ago
Solved

Power Query not recognizing data type datetime

Even though the column is type datetime Power Query does not recognize it as such.  Any ideas on how to fix this?

Yes, I can import the table if I don't try to filter the CreateDate column.

let
Source = XXXXXX.Data(XXXXXXXXXXXXXXXXXX),
Contracted_Project__c = Source{[Name="Contracted_Project__c"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Contracted_Project__c,{{"CreatedDate", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type",
each [CreatedDate] >= Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-1))
)
in
#"Filtered Rows"

 

RESULT: Expression.Error: We cannot apply operator < to types Date and DateTime.
Details:
Operator=<
Left=1/1/2024
Right=1/16/2024 7:00:00 PM

Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    You are trying to filter a datetime column with a date. That is the cause of the error. Wrap the filter criteria in a DateTime.From() to fix this.

    #"Filtered Rows" = Table.SelectRows(#"Changed Type",
    each [CreatedDate] >= DateTime.From(Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-1))
    ))

2 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    1 year ago

    You are trying to filter a datetime column with a date. That is the cause of the error. Wrap the filter criteria in a DateTime.From() to fix this.

    #"Filtered Rows" = Table.SelectRows(#"Changed Type",
    each [CreatedDate] >= DateTime.From(Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),-1))
    ))