none
Error when trying to restrict query by Date/Time RRS feed

  • Question

  • I'm running the following Power Query

    let
        
        ParamTable = Excel.CurrentWorkbook(){[Name="tbl_Parameter"]}[Content],
        StartDateRow = Table.SelectRows(ParamTable, each ([Parameter]="Start Date")),  //Find row with Start Date, returns a table
        StartDate = StartDateRow{0}[Value],   //Only 1 row in Table so assume row 0 is the value I want
        EndDateRow = Table.SelectRows(ParamTable, each ([Parameter]="End Date")), 
        EndDate =  EndDateRow{0}[Value], 
        
        Source = Excel.CurrentWorkbook(){[Name="SAMPLE_Excerpt"]}[Content],
        #"Filtered Rows" = Table.SelectRows(Source, each [SAMPLED_DATE] >= StartDate and [SAMPLED_DATE] <= EndDate),
        #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"PRODUCT"}),
        Products = Table.SelectColumns(#"Removed Duplicates",{"PRODUCT"})
      
    in
        Products

    The query returns the correct list but always has an extra error row with the following error message

    Expression.Error: Cannot apply operator < to types DateTime and Number.
    Details:
        Operator=<
        Left=1/1/2015 12:00:00 AM
        Right=42013

    42013 corresponds to 1/9/2015 which is not a date entered anywhere in my query or spreadsheet.  I tried using the DATETIME#() function to convert a text string to a date but still get the same message.

    Can anyone provide an example of restricting a query by a date range pulled from a parameter table in Excel?


    Thursday, January 29, 2015 6:45 PM

Answers

  • To Excel, dates are just numbers with fancy formatting. The number 42013 represents the date "January 9, 2015". When we read data from Excel, we sometimes can't tell whether we should treat them as numbers or as dates. There are two different places that you're working with values from Excel that could be dates: when getting the parameters and when comparing against the SAMPLE_DATE column. Now that you've changed the former, the error is clearly coming from the latter. You should be able to work around this either by coercing the column type to date before doing the filter or by changing the filter to work with either type. The former is probably a better solution; the latter looks like this:

        Source = Excel.CurrentWorkbook(){[Name="SAMPLE_Excerpt"]}[Content],
       
    #"Filtered Rows" = Table.SelectRows(Source, each DateTime.From([SAMPLED_DATE]) >= StartDate and DateTime.From([SAMPLED_DATE]) <= EndDate),

    • Proposed as answer by Gil RavivMVP Sunday, February 1, 2015 10:13 PM
    • Marked as answer by mattj63 Monday, February 2, 2015 2:48 PM
    Friday, January 30, 2015 10:06 PM

All replies

  • Hi,

    I think this errors due to different date and date time format . So that, you can correct the formula line of StartDate and EndDate to below :

    StartDate = DateTime.From( StartDateRow{0}[Value] )

    EndDate = DateTime.From( EndDateRow{0} [Value] )

    I hope this to help you.

    Regards,

    Friday, January 30, 2015 2:03 AM
  • Thanks.  I still get the same error message.  I don't understand where the 42013 is coming from.
    Friday, January 30, 2015 9:50 PM
  • To Excel, dates are just numbers with fancy formatting. The number 42013 represents the date "January 9, 2015". When we read data from Excel, we sometimes can't tell whether we should treat them as numbers or as dates. There are two different places that you're working with values from Excel that could be dates: when getting the parameters and when comparing against the SAMPLE_DATE column. Now that you've changed the former, the error is clearly coming from the latter. You should be able to work around this either by coercing the column type to date before doing the filter or by changing the filter to work with either type. The former is probably a better solution; the latter looks like this:

        Source = Excel.CurrentWorkbook(){[Name="SAMPLE_Excerpt"]}[Content],
       
    #"Filtered Rows" = Table.SelectRows(Source, each DateTime.From([SAMPLED_DATE]) >= StartDate and DateTime.From([SAMPLED_DATE]) <= EndDate),

    • Proposed as answer by Gil RavivMVP Sunday, February 1, 2015 10:13 PM
    • Marked as answer by mattj63 Monday, February 2, 2015 2:48 PM
    Friday, January 30, 2015 10:06 PM
  • That worked. I didn't realize that the column was still of type "any" and just displaying as date-time.  Here's the code that works thanks to Curt and CDzung

    ParamTable = Excel.CurrentWorkbook(){[Name="tbl_Parameter"]}[Content],
        StartDateRow = Table.SelectRows(ParamTable, each ([Parameter]="Start Date")),  //Find row with Start Date, returns a table
        StartDate = DateTime.From(StartDateRow{0}[Value]),   //Only 1 row in Table so assume row 0 is the value I want
        EndDateRow = Table.SelectRows(ParamTable, each ([Parameter]="End Date")), 
        EndDate =  DateTime.From(EndDateRow{0}[Value]), 
        
        Source = Excel.CurrentWorkbook(){[Name="SAMPLE_Excerpt"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"SAMPLED_DATE", type datetime}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [SAMPLED_DATE] >= StartDate and [SAMPLED_DATE] <= EndDate),
    
    Thanks!

    • Marked as answer by mattj63 Monday, February 2, 2015 2:47 PM
    • Unmarked as answer by mattj63 Monday, February 2, 2015 2:48 PM
    Friday, January 30, 2015 11:41 PM