Forum Discussion

AGo's avatar
AGo
Post Patron
6 years ago

Historical data

Hello,

 

is it possible in PowerBI (using Power Query) to append historical data not refreshing to a refreshing same-structured table?

After that I wouldn't be able to connect to the DB anymore, then I'd need to operate to the append table (maybe creating index) without PowerBI asking me to connect the historical data another time.

I know I could do this with DAX UNION function, but I'm asking specifically about Power Query.

It's 1 billion rows and it's very unpractical to refresh everytime I operate and so would be incremental refresh.

Thanks

 

Need Help
Tips and Tricks
Tutorial Requests

9 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    AGo You should be looking into incremental refresh. There are many posts on it and here is link to microsoft doc. And luckily recently incremental refresh is released in Power BI pro so you are covered to use it without premium.

     

    Would appreciate Kudos 🙂 if my solution helped.

    • AGo's avatar
      AGo
      Post Patron
      6 years ago

      I'm sorry but as I said I can't use incremental refresh because it has two big limits, it needs an active connection and it refreshes data everytime you operate. For example if I have a value column and I create a new column that returns [value]+1 it doesn't use stored data but pretends to refresh all the data from the start.

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        AGo well you have to make sure you know what columns you need. It is like traditionally building a warehouse and after 10 years of data, if you need to add new column, you have to backfill your warehouse and similarily, if you are adding new columns etc, ofcourse you have to update your dataset (and again full refresh will be one time) and then it will be inremental refresh. Not sure what  you expect the solution suppose to be if incremental refresh is not the right approach here.

         

        Also I'm not sure what you mean by active connection?? Data refresh required connection.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi AGo ,

     

    "is it possible in PowerBI (using Power Query) to append historical data not refreshing to a refreshing same-structured table?"

    According to your description, you seem to want to append historical data in a non-refresh way in "Edit Queries". 
    You can select "Home" -> "Append Queries" options to achieve it, but if you use "Direct Query" or "Live connection" mode, you can't use the feature.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.