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

Very Newbie Question - limiting result size - using SQL

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

1 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.

 

rajendran_0-1634652229083.png

 

View solution in original post

4 REPLIES 4
rajendran
Super User
Super User

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.

rajendran_0-1634651311358.png

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

rajendran_1-1634651573907.png

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

 

Thansk
Raj

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

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.

 

rajendran_0-1634652229083.png

 

Perfect - thanks Raj!

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors