Forum Discussion
Solved
PowerQuery - load first 10 rows of every excel sheet from multiple files
Hi, I have a SharePoint with multiple excel files and in every file there are multiple sheets. What I want to do, is to load first 10 rows from every sheet from every excel file at the same time....
yes, in addition to enumerating the files you also need enumerate all the sheets in each file. First create a list of all files and their sheets and then use a custom column to select the top 10 rows from each of the elements in the list. Then expand that custom column.
Hello, TSlowik ,
here's a complete solution.Just fill the parameters and then in query "Files" change first step to Sharepoint.Files, or keep it and test it for local files.
Here's link for my file:
first10RowsForEachSheetEachFile.pbixquick steps:
- create sample file operation
- get file
- select first 10 rows
- do that for each sheet
- get all files
- call function for all files
- expand it
- create sample file operation
Ahmedx
Super User
Super Userpls try this
let
// Step 1: Load all files from the specified folder.
from = Folder.Files("C:\Users\User\Desktop\ggg"),
// Step 2: Filter to include only ".xlsx" files that are not hidden.
filtr = Table.SelectRows(from, each [Extension] = ".xlsx" and [Attributes][Hidden] = false),
// Step 3: Select only the "Name" and "Folder Path" columns from the filtered table.
tbl = Table.SelectColumns(filtr, {"Name", "Folder Path"}),
// Step 4: Define a function (func) that will:
// a. Load each Excel workbook's content as a table using the full path.
// b. Access each sheet in the workbook and take only the first 10 rows.
// c. Combine these 10 rows from each sheet into one single table.
func = each [
a = Excel.Workbook(File.Contents([Folder Path] & [Name]), null, true)[Data],
b = Table.Combine(List.Transform(a, (x) => Table.FirstN(x, 10)))
][b],
// Step 5: Apply the function (func) to each file in the table and combine all results into a final table.
final = Table.Combine(Table.AddColumn(tbl, "tmp", func)[tmp])
in
final