Setting Scheduled update on Dynamic source query

[BMM27]

Hi, I've created a data source that retrieves files from a folder and performs actions based on the file name.
However, when I published it to the Power BI service, scheduled updates are disabled. Is there a way to resolve this? One solution might be to specify the path for each file needed and combine them all in the query.

Any thoughts?

Here is the code:

let
//Get the list of files from SharePoint
Source = SharePoint.Files("sharepointSite", [ApiVersion = 15]),
FolderUrl = "sharepointFolder",
//Filter the files to select only those starting with "A" or "B"
FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "A") or Text.StartsWith([Name], "B")),

//Define a function to load data from each Excel file
LoadData = (file as text) =>
let
// Fetch the binary content of the Excel file
ExcelContent = Web.Contents(FolderUrl & file),

// Load the Excel workbook
ExcelWorkbook = Excel.Workbook(ExcelContent),

// Determine the sheet name based on the file name
DataSheetName =
if Text.StartsWith(file, "C") then "D"
else if Text.StartsWith(file, "E") then "F"
else null,

// Extract data from the specified sheet
DataSheet = if DataSheetName <> null then ExcelWorkbook{[Item=DataSheetName, Kind="Sheet"]}[Data] else null,

// Skip the first row if data exists
SkipFirstRow = if DataSheet <> null then Table.Skip(DataSheet, 1) else null,

// Add a column to identify the source file
AddSourceColumn = Table.AddColumn(SkipFirstRow, "FileSource", each file, type text)
in
// Return the resulting table
AddSourceColumn,

// Apply the LoadData function to each file and combine the results
CombinedData = Table.Combine(List.Transform(FilteredFiles[Name], each LoadData(_))),
in
#"CombinedData"

[Anonymous]

Hi @BMM27 ,

Based on your problems, here are my answers.

Firstly I think you can consider using parameters to specify the file paths Instead of dynamically generating the file path within the query. It will help Power BI service to recognize and access these files. I think you can read this document: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

 If your SharePoint site is not accessible directly by the Power BI service, ensure that you have a data gateway installed and configured to facilitate the connection between Power BI service and your SharePoint site.

After publishing your report to the Power BI service, ensure that the dataset's credentials and settings are correctly configured to allow for scheduled refreshes. This includes setting up the correct authentication method for SharePoint. Maybe this document can help you: How to configure Power BI report scheduled refresh - Power BI | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BMM27 · Accepted solution]

Hi, here is my solution. For anyone who is going to have same trouble, here is the code.
This code connects to a sharepoint folder, search by name different files, and unify all files into one table.

let
// Source
Source = SharePoint.Files("https://SharepointSite", [ApiVersion = 15]),
FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "AAA") or Text.StartsWith([Name], "BBB") and [Folder Path] = "https://sharepointfolder"),

// Data process function
ProcessFile = (file as record) =>
let
name = Text.BeforeDelimiter(file[Name], ".xlsx"),
content = file[Content],
workbook = Excel.Workbook(content),

// Sheet names
sheetNames = Table.Column(workbook, "Item"),
origen = if List.Contains(sheetNames, "aa") then "AA" else if Text.StartsWith(name, "bb") then "BB" else "CC",
sheetName = if origen = "aa" then "AA" else if origen = "aa" then "BB" else "cc",

sheet = try workbook{[Item=sheetName, Kind="Sheet"]}[Data] otherwise null,
table = if sheet = null then null else Table.Skip(sheet, 1)
in
table,

// Process each file
ProcessedFiles = Table.AddColumn(FilteredFiles, "ProcessedContent", each ProcessFile(_)),

// Filter Null
NonNullTables = Table.SelectRows(ProcessedFiles, each [ProcessedContent] <> null),

// Expand and combine
ExpandedTables = Table.ExpandTableColumn(NonNullTables, "ProcessedContent", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),

#"Removed Columns" = Table.RemoveColumns(ExpandedTables,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})
in
#"Removed Columns"