The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
Please follow the documentation. Use RelativePath and Query parameters. Web.Contents - PowerQuery M | Microsoft Learn
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.