Combine different Excels and sheets from sharepointFolder

[BMM27]

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:

Source = SharePoint.Files("sharepoint.", [ApiVersion = 15]),
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.

[Anonymous]

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.

[BMM27]

Hi, 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 · Accepted solution]

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