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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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!

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors