Forum Discussion
BMM27
Helper I
Helper IDataSource.Error List.Accumulate
Hi, I want to take data from different .xlsx on SharePoint with distinct variables. And on the LoadData step it brokes with this error: DataSource.Error: SharePoint: Request failed: The remote serve...
I think I have it!
For other people, this code:
Takes a sharepoint folder- Filter excels by name
- Depending on the File name takes different sheets
- Finally combine all data on one table.
Here is the code:
let FolderUrl = "sharepoint folder", //Get the list of files from SharePoint Source = SharePoint.Files("sharepoint", [ApiVersion = 15]), //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, "A") then "C" else if Text.StartsWith(file, "B") then "D" 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
lbendlin
Super User
Super Useras I said, instead of ExcelContent = SharePoint.Files(FolderUrl & file){0}[Content] you should use Table.AddColumn . There you can implement your sheet switching logic. After that you only need to expand the custom column and discard all unwanted fields.
BMM27Helper I
Helper II think I have it!
For other people, this code:
Takes a sharepoint folder
- Filter excels by name
- Depending on the File name takes different sheets
- Finally combine all data on one table.
Here is the code:
let
FolderUrl = "sharepoint folder",
//Get the list of files from SharePoint
Source = SharePoint.Files("sharepoint", [ApiVersion = 15]),
//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, "A") then "C"
else if Text.StartsWith(file, "B") then "D"
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