Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dhivyabharathi
Regular Visitor

Scheduling Refresh Failed

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."

dhivyabharathi_0-1719552612263.png

 

Need help for fixing this error

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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

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

Please show the code and the error message.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors