Limit data load in desktop

Hey ani_informa ,

you can use parameters to point the query inside Power Query to different views.

I created a parameter inside Power Query called "Viewname"

I created 2 different views inside my data source

• one called SmallView, this view returns just a fraction of the rows
• and the other one called LargeView, this one returns all the available rows.

Using the advanced editor I tweaked the query that the connection is using my parameter, this will look like this:

let
    Source = Sql.Database("nameoftheserver", "nameofthedatabase", [Query="SELECT * FROM " & Viewname])
in
    Source

Then I published the pbix file to the PBI service.

I checked the gateway connection of the dataset inside the workspace (Schedule Refresh --> Gateway connection), that everything is green:

Then I changed the parameter value that contains the name of the query that returns all the rows:

Done, now I can refresh the dataset from inside Power BI Service, and all the rows are used.

Please be aware, that every time, you "re-publish" the Power BI Desktop file to the service, you are overwriting the parameter, this means you have to adjust the parameter value once again.

Hopefully, this provides what you are looking for.

Regards,
Tom

hi  ani_informa 

You could try this two way:

1.Do some filter in power query first, then do modeling and create report. after you finish development for the report, go back to power query and remove the filter step in the right pane, now publish to service.

2. Do some filter in power query, but use parameter to filter the data and then after publish to the power service, then adjust the parameter in power bi service.

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

https://www.designmind.com/business-intelligence/power-bi-parameters-how-to-use-parameters/

Regards,

Lin

Yes. Just go into power query and select "keep top rows" followed by the number of your choice. Depending on your query and data source, it may or may not be efficient. If you are using a DB back end, try to do the filter as the first step in the query load.