SharePoint Document library metadata Power BI report and incremental refresh

[EC7865]

Hello, 

I am hoping someone can help with the following scenario. 

I have a SharePoint document library (not the main 'documents' one, another library created in the same site), with folders, subfolders and circa 3200 documents, 450 folders and circa 100 pieces of metadata (across 3 choice columns). Example below: 

I have created a Power BI desktop report which users can filter their documents by the metadata. This was done through the SharePoint List connector in Power BI desktop. Example below: 

I want to put in a refresh (or incremental refresh) to update the report when a) a new document is added to the library, b) metadata changes on the choice column for a document(s), and c) when the document is modified. 

I've looked into incremental refresh, but I know the query folding was breaking on the Power Query transform when I was putting the data together in Power BI desktop. I have dabbled with rangestart and rangeend dates, as well as using Dataflows in Power BI online. 

How do I get this report to do a refresh based on the criteria above? Through incremental refresh? Through Power Automate? DataFlows? 

Any help you can give on this would be gratefully received. I'm at a bit of a loss now as to where to go to next on this. 

[v-csrikanth]

Hi @EC7865 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit.
Feel free to reach out if you need any further assistance.

Thank you.

[v-csrikanth]

Hi @EC7865 
I wanted to check if you had a chance to try the suggested solution.
Please let us know if you need any further assistance.

Thanks,
Cheri Srikanth

[EC7865]

Thanks so much everyone who has replied to this thread, I will try these approaches out 😀

[Akash_Varuna]

Hi @EC7865 , You could try Incrimental Refresh or Dataflow for these please try these out 

• Enable Incremental Refresh: Set up RStart and REnd parameters in Power Query, filter rows by the Modified column, and configure incremental refresh in Power BI Service after publishing.

• Fix Query Folding: Simplify Power Query transformations to ensure query folding remains intact, and use the "View Native Query" option to verify folding.

• Use Dataflows: Move data ingestion to a Dataflow in Power BI Service, set up incremental refresh there, and connect your report to the Dataflow for better scalability.

• Leverage Power Automate: Trigger dataset refreshes in Power BI Service using Power Automate when documents are added, metadata is updated, or files are modified in SharePoint.
  If you have Azure Cloud then You can use Data Factory also store the metadata in Data Lake or SQL Database 
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance

[v-csrikanth · Accepted solution]

Hi @EC7865 
Instead of relying on incremental refresh, use Power Automate to trigger dataset refreshes only when needed, reducing unnecessary processing.

Steps to create PA flow:

1. • Use the “when a file is created or modified (properties only)” trigger from SharePoint.
   • Filter to check if it’s a new file, modified file, or metadata update.
   • Add the "Refresh a dataset" action to refresh only the required dataset.

Note: Refreshes only when required, instead of processing the entire dataset each time.

Power BI dataflows support query folding, using dataflows instead of direct Power BI queries can improve refresh performance.
Steps to create a Dataflow in Power BI Service:

• Connect to the SharePoint document lib using dataflows instead of BI Desktop.
• Perform transformations inside dataflows.
• Load into Power BI Dataset with Incremental Refresh.
       Since Dataflows support folding, Incremental Refresh works correctly.

Ref link: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-and-use-dataflows 

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[lbendlin]

You can try incremental refresh but since your source doesn't fold it will take exactly as long as a full refresh.