Is it possible to filter data before direct query from SQL database in Power BI

Question

I am trying to build an app in Power BI. I am aware that Power BI has some trouble handling large dataset, I am afraid this might affect performance thus I'd like to filter the latest data only. Is it possible to filter data before direct query from SQL database in Power BI, as shown in the screenshot below? If not, what are some other good ways to filter data before direct query?

Update:

Tried to run SQL code:

let
    Source = Sql.Databases("myproject.database.windows.net", "MyDatabase", [Query="Select * from My_Table where Timestamp> '2021-01-01' "])
in
    Source

and

let
    Source = Sql.Databases("myproject.database.windows.net"),
    MyDatabase = Source{[Name="MyDatabase"]}[Data],
    dbo_My_Table = NiagaraDatabase{[Schema="dbo",Item="dbo_My_Table "]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_My_Table , each [Timestamp] > '2021-01-01')

in
    #"Filtered Rows"

and both returned error:

Details: "Microsoft SQL: Incorrect syntax near '='."

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2021-03-25T23:10:27

In DirectQuery mode the data stays in the database, and isn't loaded into Power BI. But sure, you can filter the table in DirectQuery so long as the filters can be "folded" into a source query. So either

let
    Source = Sql.Databases("."),
    AdventureWorksDW2017 = Source{[Name="AdventureWorksDW2017"]}[Data],
    dbo_FactInternetSales = AdventureWorksDW2017{[Schema="dbo",Item="FactInternetSales"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_FactInternetSales, each [DueDateKey] > 20110111)
in
    #"Filtered Rows"

or like this

let
    Source = Sql.Database(".", "adventureworksdw2017", [Query="Select * from FactInternetSales where DueDateKey > 10110111"])
in
    Source

Will work for DirectQuery. And even in Import mode this "Power BI has some trouble handling large datasets" only true for sufficiently large values of large. A few tens of millions of rows is not really a problem, except if you have a slow network connection or need frequent refreshes.

Is it possible to filter data before direct query from SQL database in Power BI

1 Answers