refresh to import limited rows instead of full table

[ajavaid]

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

[ashishrj]

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

[sbahugu2]

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]

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.

[konstantinos]

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

[Sacha]

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.

HTH

Sacha