Forum Discussion

cuongle's avatar
cuongle
Icon for Advocate II rankAdvocate II
10 years ago

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) ...
Need Help
Tips and Tricks
nikil's avatar
nikil
Icon for Resolver I rankResolver I
10 years ago

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's avatar
    cuongle
    Icon for Advocate II rankAdvocate II
    10 years ago

    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's avatar
      nikil
      Icon for Resolver I rankResolver I
      10 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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?