Forum Discussion

blitwak's avatar
blitwak
Icon for Helper I rankHelper I
3 years ago

Incremental Refresh is not working on Power BI Service

Hi-

My soure are 10,000 Excel Files on SharePoint.

Power BI takes 2 hours to refresh so I want to use Incremental Refresh.

I dont understand why it tooks too much time.

If Excel is on Folder /Current, it should refresh.

If Excel is on folder /Archive, it should archive it

 

I know Incremental refresh works best for structured, relational data sources like SQL Datababase but I found a work around to fix it creating a date time column:

If Excel is on Folder /Current, it create a column with current year.

If Excel is on folder /Archive, it create a column with last year.

If year(new column) = year(RangeStart) = year(RangeEnd), it refresh.

If year(new column) <> year(RangeStart), it archive it.

 

I try 4 days to make the first refresh on Power BI Service but it doesn't work. Always faills. 

Can you help me?

If I disable Incremental Refresh, it works.

 

Regards,

Need Help

4 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    3 years ago

    This is why it works best with a relational database. When you do the RangeStart/RangeEnd filter, it sends back a SQL query to the server to only return the dates in that range.

     

    When you run it against SharePoint, a CSV file, or Excel, it has to read all the data in then discard what it doesn't want. So for each of the 4 days you are doing, it is reading everything, then discarding all but one day, then repeats that 3 more times.

    • blitwak's avatar
      blitwak
      Icon for Helper I rankHelper I
      3 years ago

      I mean 4 days that I try to refresh data without success..

      I created a datetime column before using RangeStart/RangeEnd filter on that column and this column is based on the folder (path).

    • blitwak's avatar
      blitwak
      Icon for Helper I rankHelper I
      3 years ago

      I got the following error with different Excels files:

      DataSource.Error: <ccon>SharePoint: Request failed (https://test.sharepoint.com/sites/test/Shared Documents/file.xlsx/_api/contextinfo): 429. DataSourceKind = SharePoint. DataSourcePath = https://test.sharepoint.com/sites/X. SPRequestGuid = 1ee8c4a0-b06f-3000-e129-926f511f4f59. </ccon>. The exception was raised by the IDbCommand interface.

      It is strange because I already got this error and Power BI refresh successfully without changes in the Data, only refreshing again a few hours later. But after applying Incremental refresh, Power BI faills always with the same issue.

       

      M Code

      #"Added Custom2" = Table.AddColumn(#"Filtered Hidden Files1", "DateIncrementalRefreshMonthly", each if [Folder Path] = "https://test.sharepoint.com/sites/test/Shared Documents/General/Deals/" then #date(Date.Year(DateTime.Date(DateTime.LocalNow())),1,1) else #date(Date.Year(DateTime.Date(DateTime.LocalNow()))-1,1,1)),
      #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"DateIncrementalRefreshMonthly", type datetime}, {"Date modified", type datetime}}),
      #"Filtered Rows2" = Table.SelectRows(#"Changed Type3", each Date.Year([DateIncrementalRefreshMonthly]) = Date.Year(RangeStart) and Date.Year([DateIncrementalRefreshMonthly]) = Date.Year(RangeEnd)),


      I've already seen your link in YouTube and that was my work around to apply incremental refresh with files on OneDrive. (create a custome column before invoke custom function)