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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BMM27
Frequent Visitor

Combine 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:

 

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.

 

1 ACCEPTED SOLUTION
BMM27
Frequent Visitor

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

 

View solution in original post

3 REPLIES 3
v-xiandat-msft
Community Support
Community Support

Hi @BMM27 ,

Below is my table:

vxiandatmsft_0-1713420300664.png

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.

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
Frequent Visitor

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.