Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.