Scheduling Refresh Failed

[dhivyabharathi]

I have tried the below code for getting data from sharepoint folder and did the follwing steps in mcode - calculated average for each files, combined each files, renaming columns,etc,..

MCode:

let
// Access SharePoint site and specify API version
Source = SharePoint.Contents("sharepoint folder link", [ApiVersion = 15]),

// Navigate through the folder structure to the target directory
#"SCORE Data Upload" = Source{[Name="SCORE Data Upload"]}[Content],
SCORE = #"SCORE Data Upload"{[Name="SCORE"]}[Content],

// Function to process each file
ProcessFile = (file as record) =>
let
// Load each table from file
TableFromXLSX = Excel.Workbook(Web.Contents(file[Folder Path] & file[Name]), null, true),
// Access the first worksheet in the workbook
FirstSheet = TableFromXLSX{[Item="SCORE", Kind="Sheet"]}[Data],
PromoteHeaders = Table.PromoteHeaders(FirstSheet, [PromoteAllScalars=true]),

// Remove certain columns (example columns "ColumnToRemove1", "ColumnToRemove2")
ColumnsToKeep = {"Region", "Country", "S", "C", "O", "R", "E", "SCORE Original","SCORE Adjusted","Difference - Original to Adjusted","Tier"},
FilteredColumns = Table.SelectColumns(PromoteHeaders, ColumnsToKeep),

// Filter certain values in the Region column (example to keep only non-null and non-zero)
FilteredRows = Table.SelectRows(FilteredColumns, each ([Region] <> null and [Region] <> 0)),

// Calculate average of desired columns
AverageS = List.Average(FilteredRows[S]),
AverageC = List.Average(FilteredRows[C]),
AverageO = List.Average(FilteredRows[O]),
AverageR = List.Average(FilteredRows[R]),
AverageE = List.Average(FilteredRows[E]),
AverageScoreOriginal = List.Average(FilteredRows[#"SCORE Original"]),

FileName = file[Name],

// Create a record for the average row
AverageRow = [Region="Average", Country="Average", S=AverageS, C=AverageC, O=AverageO, R=AverageR, E=AverageE, #"SCORE Original"=AverageScoreOriginal, #"SCORE Adjusted"=AverageScoreOriginal, #"Difference - Original to Adjusted"="0", Tier="High", DataSource = FileName],

// Append average row to the table
InsertAverageRow = Table.InsertRows(FilteredRows, Table.RowCount(FilteredRows), {AverageRow}),
AddDataSourceColumn = Table.AddColumn(InsertAverageRow, "DataSource", each FileName)
in
AddDataSourceColumn,

// Apply the ProcessFile function to each file in the SCORE folder
AddAverageRow = Table.AddColumn(SCORE, "Data", each ProcessFile(_)),
CombineTables = Table.Combine(AddAverageRow[Data]),

#"Renamed Columns" = Table.RenameColumns(CombineTables,{{"DataSource", "Data Category"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each true)
in
#"Filtered Rows"

After publishing the dashboard, when I try schedule refresh - I am getting the below error.

"This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources."

Need help for fixing this error

[lbendlin · Accepted solution]

Please follow the documentation. Use RelativePath and Query parameters. Web.Contents - PowerQuery M | Microsoft Learn

[dhivyabharathi]

when I try to reframe the code with RelativePath & Query Parameters. I am getting credentials failed error.

[lbendlin]

Please show the code and the error message.