none
Between Dates across two tables RRS feed

  • Question

  • Hi,

    I m new to Power Query and M, and I am having a but of trouble getting something to work.

    I have a table of data and a table with two dates in it. The two dates are for the reporting period I wish to filter. I will have several tables referencing this, but I am struggling to get the first one to work!

    I am trying to get a date field to lookup to the two dates and only return records between those two dates.

    I currently have:

    let

        RepDateSource = Excel.CurrentWorkbook(){[Name="ReportingDates"]}[Content],
        Rep_SDate = RepDateSource{0}[Start_Date],
        Rep_EDate = RepDateSource{0}[End_Date],

        Source = Excel.Workbook(File.Contents("S:\HR\HRMI\Reports\Monthly\Files\PIE\Current\PIE001.xlsx"), null, true),
        Table1_Table = Source{[Item="ReportingDates",Kind="Table"]}[Data],
        #"Added Custom" = Table.AddColumn(Table1_Table, "Check SDate", each Rep_SDate),
        #"Added Custom2" = Table.AddColumn(Table1_Table, "Check EDate", each Rep_EDate),

        PIE001_Sheet = Source{[Item="PIE001",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(PIE001_Sheet),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee No", Int64.Type}, {"Forename", type text}, {"Surname", type text}, {"Known As", type text}, {"Post No", Int64.Type}, {"Post Title", type text}, {"SBU_SSU", type text}, {"Department", type text}, {"Team", type text}, {"Band", type text}, {"Manager Ref", Int64.Type}, {"Manager Forename", type text}, {"Manager Surname", type text}, {"EXCO Ref", Int64.Type}, {"EXCO Forename", type text}, {"EXCO Surname", type text}, {"EXDIR Ref", Int64.Type}, {"EXDIR Forename", type text}, {"EXDIR Surname", type text}, {"Cost Centre", Int64.Type}, {"Gender", type text}, {"Employment Type", type text}, {"GEO Location", type text}, {"Site Location", type text}, {"Contract Hours", type number}, {"Job Hours", type number}, {"Post FTE", type number}, {"Fin FTE", type number}, {"LV Start Date", type date}, {"Continuous Service Date", type date}, {"Post Start Date", type date}, {"LOS Months", Int64.Type}, {"LOS Months Continuous", Int64.Type}, {"Leave Date", type date}, {"Role Progression", Int64.Type}, {"Manager Status", type text}, {"Hourly Rate", type number}, {"Actual Salary", type number}, {"FTE Salary", type number}, {"Date of Birth", type date}, {"Extract Date", type date}, {"End of Month", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Leave Date] >= Rep_SDate and [Leave Date] <= Rep_EDate)
    in
        #"Filtered Rows"

    But I get the following error:

    Expression.Error: We cannot apply operator < to types DateTime and Date.
    Details:
        Operator=<
        Left=01/04/2017 00:00:00
        Right=28/04/2017

    I have checked all of my date formats and they are all set to just date, so not sure where the date/time is coming from.

    If I change my variables back to dates ie #date(2017,4,1) etc... then it works. 

    So I am guessing I have something wrong in the above code around my variables?

    Can anyone help?

    Wednesday, May 10, 2017 10:57 AM

Answers

  • My suspicion is that your start and end dates are datetimes. 

    Try
    Rep_SDate = DateTime.Date(RepDateSource{0}[Start_Date]),

    and similar for end date.

    • Marked as answer by Richard Wyeth Wednesday, May 10, 2017 11:41 AM
    Wednesday, May 10, 2017 11:09 AM

All replies

  • My suspicion is that your start and end dates are datetimes. 

    Try
    Rep_SDate = DateTime.Date(RepDateSource{0}[Start_Date]),

    and similar for end date.

    • Marked as answer by Richard Wyeth Wednesday, May 10, 2017 11:41 AM
    Wednesday, May 10, 2017 11:09 AM
  • Amazing, that has worked.

    Always a more simple answer than I expect it to be.

    Thank you very much!

    Wednesday, May 10, 2017 11:42 AM