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
cuongle
Advocate II
Advocate II

Import/refresh data which is subset of table

I use Import Data with Personal Gateway Scheduler to refresh data, but I don't want to have data of whole table, just subset of table based on conditions.

 

Example:

 

I have Country (Dimension) and InvoiceDetail (Fact) table. I just would like to import/refresh all invoice detail rows belong to UK.

 

One approach that I see is using database view in order to apply conditions and let powerbi connect to that view instead of table. It is not straight approach, though. That's why I would like know how Power Bi deals with this case in simpler way.

 

 

 

 

 

 

 

 

4 REPLIES 4
nikil
Resolver I
Resolver I

Power BI service does not provide an option to use parameters while refreshing data. You have to manage this in the data modeling layer. When a refresh is performed, complete dataset is refreshed. In your example, Country (Dimension) and InvoiceDetail (Fact) table is completely refreshed. 

Now if you want only UK data in InvoiceDetail table refreshed and non UK data to be static, create two queries, one for UK and the other for non UK. Here make sure only UK data gets updated (using SQL parameters etc). Append both these queries to create InvoiceDetail query.

 

Hope this helps.

 

--nikil

 

 

Hi @nikil,

 

Thanks for the answer, what I meant is I don't want to have non UK data in  powerbi file at beginning when importing as well as refreshing.

 

Please could you elaborate more how to create query to to refresh only UK data

Depending on the data source you have two options:

  1. Create a query that imports only UK data (e.g. writing SQL statement where clause)
  2. If you cannot filter only UK data in the source query, import all the data into query editor and then filter to include only UK data

 

Once the desktop is published, on scheduled refresh it will import only UK data.

 

Thanks

--nikil

I have a case that my fact table is a weeklysnapshot table consisting of billion of records so powerbi desktop errors out (timeout expired) whle importing records so therefore want to get somehow subset of data for development purposes and the right filters are applied in power query i assume the problem would go away but how to get subset of data for the first time without using sql query as a source query?

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.

Top Solution Authors