Forum Discussion
Helper ICombine different Excels and sheets from sharepointFolder
Hi, I'm trying to combine data from different Excel files and sheets from a SharePoint folder.
I'm attempting to accomplish this using a single query, but I'm encountering difficulties.
Here's an example of what I'm trying to achieve:
- If the Excel file name starts with "A", I want to extract data from sheet "B".
- If the Excel file name starts with "D", I want to extract data from sheet "C".
- Additionally, for the first Excel file, I want to use the first row as headers, and for subsequent files, I want to delete the first row.
- Finally, I want to combine all the data into one table.
Here are the initial steps that are working:
FilteredFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "A") or Text.StartsWith([Name], "D"))
Before proceeding with these steps, I need to implement the conditions I've described. I've tried various approaches, but I haven't been successful.
I would appreciate any assistance with this matter.
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
3 Replies
Hi BMM27 ,
Below is my table:
The following M code will help you:
let Source = Excel.Workbook(File.Contents("C:\Users\Administrator\Desktop\Project Files\" & [Project]&".xlsx"), null, true), #"SheetData" = if Text.StartsWith(Source, "A") then FileContent{[Item="B",Kind="Sheet"]}[Data] else if Text.StartsWith(Source, "D") then FileContent{[Item="C",Kind="Sheet"]}[Data] else null, #"AdjustedData" = if Source = List.First(FilteredFiles[Name]) then Table.PromoteHeaders(SheetData, [PromoteAllScalars=true]) else Table.Skip(SheetData,1) in #"AdjustedData"Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BMM27Hi, thanks for the response. Could you clarify how [project] will be changed, or does this code imply that it will select all projects? Also, I'm a bit confused about how FilteredFiles is used later in the code.
The following code snippet may provide context. It should be placed below the FilteredFiles step, but it's currently not functioning as expected:
FilteredFiles is a table where each row represents an Excel file with a content type of Binary.LoadData = (file) =>
let
ExcelContent = SharePoint.Files(FolderUrl & file){0}[Content],ExcelWorkbook = Excel.Workbook(ExcelContent),
DataSheet =
if Text.StartsWith(file, "A") then
ExcelWorkbook{[Item="B", Kind="Sheet"]}[Data]
else if Text.StartsWith(file, "D") then
ExcelWorkbook{[Item="C", Kind="Sheet"]}[Data]
else
null,SkipFirstRow = if DataSheet <> null then Table.Skip(DataSheet, 1) else null,
AddSourceColumn = Table.AddColumn(SkipFirstRow, "FileSource", each file, type text)
in
AddSourceColumn,
CombinedData = Table.FromRecords(List.Transform(FilteredFiles[Name], each LoadData(_)))
in
CombinedData
Thanks!- BMM27
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
