Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I have data source which is from folder, I choose the directory and I combine and transform data (at this stage, i can only choose 1 sheet)
Different excel sometimes have 2 excel sheets and 3 excel sheets. but the sheet names are consistent
Solved! Go to Solution.
you can load all the file by the below code (with comments for each step)
let
// Set the folder path
FolderPath = "C:\YourFolderPath\",
// Load all files from the folder
Source = Folder.Files(FolderPath),
// Filter to only include Excel files
ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx") or Text.EndsWith([Name], ".xls")),
// Add a column to extract data from each file
AddCustom = Table.AddColumn(ExcelFiles, "Data", each Excel.Workbook([Content], true)),
// Expand the Data column to get each sheet from each file
ExpandSheets = Table.ExpandTableColumn(AddCustom, "Data", {"Name", "Data"}, {"SheetName", "Data"}),
// If you want to filter by specific sheet names, uncomment and modify this line:
// FilterSheets = Table.SelectRows(ExpandSheets, each [SheetName] = "Sheet1" or [SheetName] = "Sheet2"),
// Expand the data in each sheet
ExpandData = Table.ExpandTableColumn(ExpandSheets, "Data", Table.ColumnNames(ExpandSheets[Data]{0})),
// Optional: Add a column to identify the source file name
AddSourceInfo = Table.AddColumn(ExpandData, "SourceFile", each [Name])
in
AddSourceInfo
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!
you can load all the file by the below code (with comments for each step)
let
// Set the folder path
FolderPath = "C:\YourFolderPath\",
// Load all files from the folder
Source = Folder.Files(FolderPath),
// Filter to only include Excel files
ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx") or Text.EndsWith([Name], ".xls")),
// Add a column to extract data from each file
AddCustom = Table.AddColumn(ExcelFiles, "Data", each Excel.Workbook([Content], true)),
// Expand the Data column to get each sheet from each file
ExpandSheets = Table.ExpandTableColumn(AddCustom, "Data", {"Name", "Data"}, {"SheetName", "Data"}),
// If you want to filter by specific sheet names, uncomment and modify this line:
// FilterSheets = Table.SelectRows(ExpandSheets, each [SheetName] = "Sheet1" or [SheetName] = "Sheet2"),
// Expand the data in each sheet
ExpandData = Table.ExpandTableColumn(ExpandSheets, "Data", Table.ColumnNames(ExpandSheets[Data]{0})),
// Optional: Add a column to identify the source file name
AddSourceInfo = Table.AddColumn(ExpandData, "SourceFile", each [Name])
in
AddSourceInfo
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!
where do i put this code? i put in power query it gives me as below ...
Go to the power query, then in the Home tab, pick Advance EDitor, in the oppened window, remove all the command and past the code I have provided.
in the code I have mention
C:\YourFolderPath\
as folder path, replace it by your own folder path
this helps a lot! any recommendation where i can learn more on advance editor?