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

 

 

 

 

 

 

 

 

Need Help
Tips and Tricks

4 Replies

  • 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