Very Newbie Question - limiting result size - using SQL

[Simebaby]

Hi All

Apologies as I'm a complete newbie with Power BI.

I'm conecting to an SQL server DB, and trying to build a query with a table with over 1M rows.

Performance is awful which is down to the server - I'm hoping I can:

1) Limit the result size whilst I actually configure the report

2) Connect using an SQL query initially to grab just the fields I need.

I haven't been able to find any guides to this - can anyone point me in the right direction?

TIA

Si

[Anonymous]

You can do these transformations on the Power Query Editor. 

1. Bring only needed columns to the model. In the Power Query editor, right click the unwanted columns and remove them.

2. Limit the data to the requirment. Filter the unwanted records. you can apply the filter in the column header.

3. Make sure to disable the Auto Date/Time in the options & settings. Otherwise, every date column is going to have a hidden table .

based on your data/ model, there are lot more options to optimize the model.

Thansk
Raj

[Simebaby]

Hi Raj

The issue I have is Power BI seems to want to load the entire dataset first - I was hoping for a way to restrict it before the first data grab takes place. I.e. the equivalent of a LIMIT clause in an SQL query.

TIA

Si

[Anonymous · Accepted solution]

When you use the Get data option, you can click the advanced option and place your query there. This query with filters will restrict the data before it brings the data to Power BI.

[Simebaby]

Perfect - thanks Raj!