Using parameters to filter before import

[Anonymous]

Hi,

I am having the following problem.

I have a SQL database with data of different clients. Now my end goals is to make some kind of "template" dashboard in Power BI where I can switch between client data with a Parameter. The tabel has 1.2mil rows spread over 20 clients. 

Now when I import the data and manually filter (in the Power Query) the "ClientID" by the clientID that I want, Power BI imports only the data with that specific clientID. Lets say 100k rows.

But when I replace the manual filter by a parameter and fill in the same clientID as before Power BI imports 1.2mil rows and then filters the data. This makes my import allot slower. 

This is how my query looks like: 

let
Bron = Sql.Database("xxxxx", "xxxxxx"),
dbo_eol_receivablesbulk_r = Bron{[Schema="dbo",Item="eol_receivablesbulk_r"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_eol_receivablesbulk_r,{{"divisionownercompanynumber", type number}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [divisionownercompanynumber] = ClientID)
in
#"Filtered Rows1"

So my question is, can I work with parameters where I dont have to import all 1.2mil rows, just the filtered rows?

Thanks!

[Anonymous]

Hi @Anonymous ,

You can refer the contents in following links and use Query Parameters in SQL conditional clauses to filter records:

Dynamic Data Masking and Query Parameters

Pass parameter to SQL Queries statement using Power BI

Best Regards

Rena

[amitchandak]

@Anonymous , see if these two can help with the initial load. then set incremental load.

https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/

post 1.2 Mil load

Incremental Load
https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only
https://thinkaboutit.be/2020/02/how-do-i-implement-an-incremental-refresh-in-power-bi-free-or-pro/
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
https://www.nabler.com/articles/power-bi-data-refresh-and-scheduling/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
https://www.fourmoo.com/2020/03/11/how-to-configure-incremental-refreshing-in-power-bi-with-datekey-or-date-instead-of-the-default-datetime/

[Anonymous]

Hi @amitchandak,

Thanks for your help. i wonder if this is the solution. Because when I dont use parameters but just filter my data (without parameters) in Power Query, Power BI wont load all the data, just the filtered data.

I see how incremental Load could help with reducing te loading time, it doenst feel like a solution for my problem.