Forum Discussion

Jase68's avatar
Jase68
Helper I
6 years ago

Incremental Load from SharePoint list

Hi Power BI Experts,

I have a list in SharePoint which has 5 columns and 200 rows. 

For 3 of the 5 columns all 200 rows are populated with entries and these entries will never change. 

For 2 of the 5 columns all of the 200 rows will start as blank but could have data added to them every day.

 

I want to be to load the SharePoint list into Power BI every 4 hours and capture any rows where new data has been added and \ or existing data has been changed. 

So, if I look at my Power BI table I will be able to see the history of all the updates and additions made to the SharePoint list whilst the SharePoint list itself will only ever show the current status.

The mocked up sequence below shows my SharePoint list changing over 2 days and what I want to see in the Power BI table at the end of day 2.

Does anyone have a suggestion that could enable me to incrementatlly load the data in my Power BI table so that a full historicaal view is retained over time and no new updates are "lost".


SharePoint List - Day 1

IDStatusDate\Time
1001Open2020-03-24 12:00:00
1002Closed2020-03-24 08:00:00
1003  
1004  

 

Power BI Table - Day 1

IDStatusDate\Time
1001Open2020-03-24 12:00:00
1002Closed2020-03-24 08:00:00

 

SharePoint List - Day 2

IDStatusDate\Time
1001Open2020-03-24 12:00:00
1002Open2020-03-25 10:00:00
1003Open2020-03-25 07:00:00
1004  

 

Power BI Table - Day 2

IDStatusDate\Time
1001Open2020-03-24 12:00:00
1002Closed2020-03-24 08:00:00
1002Open2020-03-25 10:00:00
1003Open2020-03-25 07:00:00
Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    I do not believe incremental works with SharePoint at this time.
    • Jase68's avatar
      Jase68
      Helper I
      6 years ago

      Hi Greg,

      Thanks for the quick response. 

      So if I cannot do this with SharePoint could I replace my SharePoint source with an Excel file and implement this approach?

      • Jase68's avatar
        Jase68
        Helper I
        6 years ago

        To close this off.......

        The advice was that I was not able to do this in Power BI \ Sharepoint (given the functionality @ Mar 2020) so I managed to work around this by using Power Automate to grab any data which changed in my SharePoint list and then using the add new row step to push the entire row of data which includes the changed field(s) into a table within an Excel file stored on OneDrive for business and shared within my organisation.  

        I then connect Power BI to the table in the shared Excel file to get all the historical data changes as they are made. 

        I will need to think about how I manage the size of the Excel table over time but this approach appears to be working for now.