cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBiNoviceEx
New Member

Filter End of Month Date without loading all the table

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?

 

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

 

Microsoft Employee

View solution in original post

2 REPLIES 2
PowerBiNoviceEx
New Member

Thank you, that worked!

ppm1
Solution Sage
Solution Sage

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

 

Microsoft Employee

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors