Cannot apply operator to types Table and DateTime|Power Query|Excel Forum|My Online Training Hub

Hi Mynda

I am having a similar problem, the error is: " Expression.Error: We cannot apply operator < to types Table and DateTime"

The code is below.  I am trying to insert a dynamic date range filter into the query by using two "helper queries" called "StartDate" and "EndDate" (see the # FilteredRows3 line)  I have also ensured that these queries return DateTime type but to no success.

Your assistance will be appreciated.

Another question, how do we turn on the suggested syntax display in the Advanced Editor?

let
Source = Excel.Workbook(File.Contents("C:\Users\kmitchell\Desktop\MASTER Ticket sales summary 2018 - 2021 DECEMBER 2020_1.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Sheet1" and [Name] <> "Sheet3" and [Name] <> "Ticket detail 2017 2018" and [Name] <> "Ticket detail 2018 2019" and [Name] <> "Ticket sales summary" and [Name] <> "TICKET SUMMARY")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19", "Data.Column20", "Data.Column21", "Data.Column22", "Data.Column23", "Data.Column24", "Data.Column25", "Data.Column26", "Data.Column27", "Data.Column28", "Data.Column29", "Data.Column30", "Data.Column31", "Data.Column32", "Data.Column33", "Data.Column34"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([Data.Column1] <> null and [Data.Column1] <> "29 December 2020 - Didn’t have a lot of sales due to a quite day" and [Data.Column1] <> "31 December 2020 - Reason for low/no sales is tht we experienced computer /network technical difficulties at our customer care centre " and [Data.Column1] <> "APRIL" and [Data.Column1] <> "AVERAGE TICKET SALES" and [Data.Column1] <> "AVG TICKETS PER DAY" and [Data.Column1] <> "CP" and [Data.Column1] <> "DAILY COMBINED AVERAGE" and [Data.Column1] <> "Daily ticket sales" and [Data.Column1] <> "Date" and [Data.Column1] <> "Date " and [Data.Column1] <> "DATE " and [Data.Column1] <> "ETB" and [Data.Column1] <> "FEEDER " and [Data.Column1] <> "JUNE" and [Data.Column1] <> "LEGEND" and [Data.Column1] <> "MAY" and [Data.Column1] <> "MONTHLY SUMMARY" and [Data.Column1] <> "Monthly Totals" and [Data.Column1] <> "Notes" and [Data.Column1] <> "PREPARED BY…………………………………………………………….." and [Data.Column1] <> "REVENUE PER SERVICE TYPE" and [Data.Column1] <> "REVENUE SUMMARY FOR JUNE 2019" and [Data.Column1] <> "REVIEWED BY ………………………………………………………………" and [Data.Column1] <> "TOTAL" and [Data.Column1] <> "TOTAL Tickets" and [Data.Column1] <> "TRUNK") and ([Data.Column2] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Name", "Month"}, {"Data.Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type datetime}}),
#"Filtered Rows3" = Table.SelectRows(#"Changed Type", each [Date] >= StartDate and [Date] <= EndDate),

#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows3",{{"Data.Column2", "Centre"}, {"Data.Column3", "A"}, {"Data.Column4", "B"}, {"Data.Column5", "C"}, {"Data.Column6", "D"}, {"Data.Column7", "E"}, {"Data.Column8", "F"}, {"Data.Column9", "G"}, {"Data.Column10", "H"}, {"Data.Column11", "I"}, {"Data.Column12", "J"}, {"Data.Column13", "K"}, {"Data.Column14", "L"}, {"Data.Column15", "M"}, {"Data.Column16", "Sales Total"}, {"Data.Column17", "Discount"}, {"Data.Column18", "Net Sales"}, {"Data.Column19", "Total Tickets Sold"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Data.Column20"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Month", "Date", "Centre", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "Sales Total", "Discount", "Net Sales", "Total Tickets Sold"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Other Columns", each ([Sales Total] <> null)),
#"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows2",{{"Total Tickets Sold", "Ticket Sales Test"}, {"M", "Total Ticket Sales"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"Date", "Month", "Centre", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "Total Ticket Sales", "Sales Total", "Discount", "Net Sales", "Ticket Sales Test"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"A", type number}, {"B", type number}, {"C", type number}, {"D", type number}, {"E", type number}, {"F", type number}, {"G", type number}, {"H", type number}, {"I", type number}, {"J", type number}, {"K", type number}, {"L", type number}, {"Total Ticket Sales", type number}, {"Sales Total", type number}, {"Discount", type number}, {"Net Sales", type number}, {"Ticket Sales Test", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}, {"Centre", Order.Ascending}}),
#"Renamed Columns3" = Table.RenameColumns(#"Sorted Rows",{{"Net Sales", "Total Tickets Sold"}, {"Discount", "Net Sales"}, {"Sales Total", "Discount"}, {"Total Ticket Sales", "Sales Total"}})
in
#"Renamed Columns3"