Filter End of Month Date without loading all the table

[PowerBiNoviceEx]

I have a table with more than 3 million rows (daily data). I only need the end of month data. I was able to achive the filter creating and EndOfMonth column in one step and then filtering the Load_Date = EndOfMonth column.

Let
Source = Odbc.DataSource("dsn=Enterprise_DM", [HierarchicalNavigation=true]),
Enterprise_DM_Database = Source{[Name="Enterprise_DM",Kind="Database"]}[Data],
dbo_Schema = Enterprise_DM_Database{[Name="dbo",Kind="Schema"]}[Data],
SOFI_Servicing_Report_Table = dbo_Schema{[Name="SOFI_Servicing_Report",Kind="Table"]}[Data],
EOMColumn = Table.AddColumn(SOFI_Servicing_Report_Table, "EndOfMonth", each Date.EndOfMonth([ProcessingDate])),
#"Filtered Rows" = Table.SelectRows(EOMColumn , each [LOAD_DATE] = [EndOfMonth])
in
#"Filtered Rows"

The problem with this solution is that it takes very long to load becasue it first needs to load all the records of the table and then apply the filter. I tried doing the filter in one step:

let
Source = Odbc.DataSource("dsn=Enterprise_DM", [HierarchicalNavigation=true]),
Enterprise_DM_Database = Source{[Name="Enterprise_DM",Kind="Database"]}[Data],
dbo_Schema = Enterprise_DM_Database{[Name="dbo",Kind="Schema"]}[Data],
SOFI_Servicing_Report_Table = dbo_Schema{[Name="SOFI_Servicing_Report",Kind="Table"]}[Data],
#"Filtered Rows" = Table.SelectRows(SOFI_Servicing_Report , each [LOAD_DATE] = Date.EndOfMonth([ProcessingDate]))
in
#"Filtered Rows"

But I get this error message: "Expression.Error: A cyclic reference was encountered during evaluation."

Can this filter be achived in one step?

[PowerBiNoviceEx]

Thank you, that worked!

[ppm1 · Accepted solution]

You can do this by first creating of list of dates for each end of the month and then using List.Contains in your filter. This approaches folds back to your source and will be much faster.  Below is an example. In my case, I first create a list of dates from 1/1/2017 through 5 years. You can adapt the EOMList step to cover the expected date range of your data (go into the future so it keeps working), and then update your filter step with the List.Contains approach.

let
    EOMList = List.Transform({0..71}, each Date.EndOfMonth(Date.AddMonths(#date(2017,1,1), _))),
    Source = Sql.Database("localhost", "Flights"),
    dbo_Flights = Source{[Schema="dbo",Item="Flights"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Flights, each List.Contains(EOMList, [FlightDate]))
in
    #"Filtered Rows"

Pat