Power Query Multiple Files Not First Sheet

[room7]

I have a few files in a fold and want to run a power query with the third sheet. They are all labeled the same. Is this possible?

I have a power query setup for the first sheet and it works great.

 

[FranzV]

*Note: I have the Spanish language version of Excel and do my best to translate query steps myself. The exact words may vary, but I hope you get the idea.

You can do this by modifying the function that was built when you accessed the first sheet or by using a flexible function to extract sheets from Excel files. The first method could be done the following way:

Locate the function Transform Binary Element From... that should look something like:

= (#"Binary Sample Parameter" as binary) => let
        Source = Excel.Workbook(#"Binary Sample Parameter", null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
    in
        Sheet1_Sheet

Change
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
for
Sheet1_Sheet = Source{2}[Data]

It now extracts the third sheet from each file instead of the sheets with the name "Sheet1". Check your query to validate the results.

Now on to the second approach that I would definitely recommend over the first one. This solution requieres a custom function that extracts the desired sheet from each file. Let me explain the function first and then I'll show the code for the query that invokes it.

//fnImportSheet
(
    BinaryFile as binary, 
    optional SheetName as nullable text, 
    optional SheetIndex as nullable number,
    optional UseHeaders as nullable logical
) as table =>
let
    useHeaders = if UseHeaders is null then false else UseHeaders,
    sheetIndex = 
        if SheetName <> null 
        then null 
        else 
            if SheetIndex is null 
            then 0 
            else SheetIndex - 1,
    Source = Excel.Workbook( BinaryFile, useHeaders, false ),
    SheetsOnly = Table.SelectRows( Source, each [Kind] = "Sheet" ),
    Sheet = 
        if sheetIndex is null 
        then SheetsOnly{ [Item = SheetName] }[Data]
        else SheetsOnly{ sheetIndex }[Data]
in
        Sheet
/*
Designed to be invoked when adding a custom column.
*/

This function takes a Binary value as its first argument and you can optionally specify the name or position of the desired sheet and whether you want to use the firs row of data as headers. If you don't specify neither sheet name nor position, it will import the first sheet of each file.

Once you have added the function to your workbook queries you can use the following code to extract the desired sheet from each file in a folder. There are some extra steps that I normally use in similar situations, but you can tweak the code to your needs.

// GetSheetsFromFolder
let
    Source = 
        DummyFolder,
    KeepContentAndName = 
        Table.SelectColumns( Source, {"Name", "Content"} ) as table,
    RemoveFileExtension = 
        Table.TransformColumns( 
            KeepContentAndName, 
            {
                "Name", 
                each Text.Start( 
                    _, 
                    Text.PositionOf( _, ".", Occurrence.Last ) 
                ), 
                type text
            }
        ) as table,
    ImportSheets = 
        Table.TransformColumns( 
            RemoveFileExtension, 
            {"Content", each fnImportSheet( _, null, null, true), type table}
        ) as table,
    RenameFileNameColumn = 
        Table.RenameColumns( ImportSheets, {{"Name", "FileName"}} ) as table,
    ColumnNames = 
        List.Union( 
            List.Transform( 
                RenameFileNameColumn[Content], 
                each Table.ColumnNames( _ ) 
            ) 
        ) as list,
    NewColumnNames = 
        List.Transform( 
            ColumnNames, 
            each if _ = "FileName"  then "Imported." & _ else _ 
        ) as list,
    ExpandData = 
        Table.ExpandTableColumn( 
            RenameFileNameColumn, 
            "Content", 
            ColumnNames, 
            NewColumnNames 
        )
in
    ExpandData

• DummyFolder is a reference to the query where you loaded the desired folder.
• Usually I only want the file name so I discard other columns with KeepContentAndName and remove the file extensions in the next step.
• ImportSheets uses fnImportSheet to transform the [Content] column from the entire contents of the file to only the data of the specified sheet.
  • This is the key step. The rest is just extra code that may or may not suit your needs.
• I then rename the [Name] column to [FileName] because it is less likely that it will cause column name conflicts.
• I use the ColumnNames and NewColumnNames to dynamically extract the headers from each sheet and add "Imported." if there is a header called "FileName" to avoid naming conflicts.
• The last step uses the dynamic column names to extract all the data.

I hope it helps.