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

refresh to import limited rows instead of full table

I want your assistance as i am stuck in a situation. Actually i have started using Excel Power Pivot and the problem i am facing is that i have connected to a table which has got 11 million rows and every time i refresh, all the 11 million rows got imported instead of just the news rows.

 

your assistance will be highly appriciated

1 ACCEPTED SOLUTION
andre
Memorable Member
Memorable Member

If you know SQL, then you can re-write the query and use something like TOP 10000 syntax in it to make sure that you don't load the entire data set every time.

 

If you don't, you should probably consider not loading data directly into Power Pivot, but rather go through Power Query.  Power Query allows you to massage and shape your data without knowing much SQL at all.  One of the things you could do is to use Keep Rows step in PQ and then specifying how many rows you would like to keep from the original dataset.

 

If can also order a column first if you want the first N or last N rows, or you can also apply filers by Right-clicking on a column that you would like to filter and selecting the fitler value.

View solution in original post

6 REPLIES 6
ashishrj
Power Participant
Power Participant

Even apart from the solutions mentioned above, you can pull data using Power Query and put filter condition to pull only the latest data on some date column.

For eg. Column: Created Date / Modified Date

             Filter    : In the Previous / Last X Days where X = 30 or any number

                           Is in day : Today / Yesterday or This Month

Here Power Query will only pull data into Power Pivot for the given filter criteria. Hope this helps!

andre
Memorable Member
Memorable Member

If you know SQL, then you can re-write the query and use something like TOP 10000 syntax in it to make sure that you don't load the entire data set every time.

 

If you don't, you should probably consider not loading data directly into Power Pivot, but rather go through Power Query.  Power Query allows you to massage and shape your data without knowing much SQL at all.  One of the things you could do is to use Keep Rows step in PQ and then specifying how many rows you would like to keep from the original dataset.

 

If can also order a column first if you want the first N or last N rows, or you can also apply filers by Right-clicking on a column that you would like to filter and selecting the fitler value.

This thread is very helpful. Although I have a situaltion : can we refrehs a datasource on visual filtering.

 

Like I have a date slicer and when user select a date range it pass those parameters (start and edn date) to sql query and reload the table instead of filtering on loaded data.?

not sure if this is doable in powerbi

dearwatson
Responsive Resident
Responsive Resident

To expand slightly on the SQL Query method

 

When you run through the SQL table import wizard in Power Pivot there is an option to import the data with a SQL Query, just enter the query in the box provided and it will bring in only a subset of the data, if you need help with SQL queries check out w3schools.com

 

If you are using power Query there is an option to add a query to the bottom of the authentication page, expand the SQL Query box and enter your query to pull the data you need.

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

Assuming that you asking about only refresh proccess & not filter/import data as correctly answered above by @dearwatson @SachaPowerPivot Data Engine will always fetch and refresh the entire source/tables and not only the new rows. Either if you use a query for uploading a data subset (assuming that you don't need all the data for analysis), this way you will have visible data only from the subset and next time you refresh it will refresh the whole subset and not only the new records..

 

https://msdn.microsoft.com/en-us/library/gg399164(v=sql.110).aspx

Konstantinos Ioannou
Sacha
Resolver I
Resolver I

In Power Pivot you have a couple of options.

 

1) Either select to write a query when going through the Table Import Wizard

2) Select your 11 million row table from the list of tables but ensure you 'Preview and Filter' prior to 'Finishing' the Table Import Wizard.

 

Capture.PNG

 

HTH

Sacha

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors