none
Inventory Snapshot Using Power Query (PowerBI)

    Question

  • Hey There

    Starting to use Power Query and Power BI very powerful stuff. What I am trying to achieve is a inventory snapshot that every morning it loads the data adds today date and then appends it to the data model without replacing the old data but just adding it the existing data. So every time time it is scheduled to refresh it loads the daily snapshot. I know this is possible using SSIS but is it possible using Power BI?

    Thanks!


    Saturday, February 15, 2014 5:24 PM

Answers

  • Sorry - Incremental refresh and adding to the data model with new data is not supported in Power Query at this time. Currently you will have to refresh the entire data every time you need to update and get the latest.

    This is something we hope to improve in the future.

    Thanks.

    Faisal Mohamood | Program Manager | Data Platform Group - Microsoft

    Monday, February 17, 2014 8:04 PM

All replies

  • Sorry - Incremental refresh and adding to the data model with new data is not supported in Power Query at this time. Currently you will have to refresh the entire data every time you need to update and get the latest.

    This is something we hope to improve in the future.

    Thanks.

    Faisal Mohamood | Program Manager | Data Platform Group - Microsoft

    Monday, February 17, 2014 8:04 PM
  • "Currently you will have to refresh the entire data every time you need to update and get the latest.

    "This is something we hope to improve in the future."

    That would be good. It seems that PQ has all the seeds in place to permit this sort of incremental updating...(1) you can compare columns, (2) you can create fixed datetime columns, (3) you can create a dimension with a key index column, (4) you can add a new row at a specific index, and so on. Just this one key piece missing. I tried faking out PQ by changing a database query to point to itself as an Excel table. It half worked. PQ didn't complain - just after manually editing the script, I was able to refresh the query in the editor and see all of the data rows. However, as soon as I clicked Apply & Close, about half of the records were permanently gone from the Excel table. A row of ellipses was put in place of the rest of the data :)

    Monday, February 17, 2014 11:34 PM
  • It's far from ideal, but I blogged about a solution to this problem last year:

    http://cwebbbi.wordpress.com/2013/05/13/accumulating-data-in-an-excel-table-using-data-explorer-and-powerpivot/

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, February 20, 2014 12:01 AM