Import/refresh data which is subset of table

[cuongle]

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.

[nikil]

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

[cuongle]

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

[nikil]

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

[uzairahm]

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?