PowerQuery - load first 10 rows of every excel sheet from multiple files

[TSlowik]

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. Number of sheets in these excels range from 5 to 10.

Based on other answers I tried to create a function which will load first 10 rows on the sample file, and use it on all the files. It only works partially, as it does not include all the sheets.

Is it a way to do it somehow? Thank you!

[lbendlin · Accepted solution]

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.

[Ahmedx]

pls 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

[vojtechsima · Accepted solution]

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.pbix

quick steps:

1. create sample file operation
   1. get file
   2. select first 10 rows
   3. do that for each sheet
2. get all files
3. call function for all files
4. expand it

[lbendlin · Accepted solution]

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.