Data refresh disabled for dynamic sharepoint excel files

[saileshdhital]

Hi,

I have the following query that is giving me an error "You can't schedule refresh for this dataset because the following data sources currently don't support refresh. Data source for Query1"
My data source sits in a folder under a sharepoint site. And I want the data source to be dynamic so that it takes the folder that has the latest quarter's data. For example, I have folders like "Q1 2023", "Q2 2023", "Q4 2023", and the query would take the latest file on the folder (Q4 2023 for today). Below is my code on the query editor:

let
Source = Excel.Workbook(Web.Contents("https://XXXX.sharepoint.com/sites/XXX/Shared%20Documents/"&
"Q"&Text.From(Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow()))-1) &" "& Text.From(Date.Year(DateTime.LocalNow()))&
"/Testing "&
"Q"&Text.From(Date.QuarterOfYear(DateTime.Date(DateTime.LocalNow()))-1)&" "
/*Following is the code to extract the last two digits of the Year. For example, 23 for 20223*/
&Text.End(Date.ToText(DateTime.Date(DateTime.LocalNow())),2)&" - XX.xlsx"), null, true),

[arvindsingh802 · Accepted solution]

Currently Power BI service does not support refresh from dynamic data source
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-source... 

To refresh dynamic data sources in Power BI Service, you can use Web.Contents() and Relative Path parameter in it, please refer:

1. Web.Contents(), M Functions And Dataset Refresh Errors In Power BI 
2. Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code