Second Parameter/Loop

Hi,

This can be done in PowerQuery/M. Given below is a step-by-step demo of the outputs at each stage. After that, I have given the PowerQuery/M code which you need to modify - mainly the first line. I have used "Folder.Files" function. You have to change the line to your Sharepoint source. Other than that, this should work without needing any major changes.

Please note that it is possible to achieve the same results using various other functions and a little bit of more complex code to optimize the process, but I have given it step-by-step based on our natural thought process for your understanding so that you would be able to modify it if required according to your needs.

Step 1: Fetch the contents of the Folder.

Step 2: Count the files.

Step 3:

Generate a list of file indices from 0 to (n-1) where n is the number of files in the folder.

Step 4:

Covert the list we got in Step 3 to a table.

Step 5:

Add the contents of the spreadsheets as binary using the file indices (0 to n-1) rather than predefined names.

Step 6: Add the file names.

Step 7: 

Add the field with the count of sheets in each of those files. In this example, all the 3 files have 3 sheets in them.

Step 8:

Add the list of sheet indices. (Just like we did for the files)

Step 9:

Expand the SheetIndices list.

Step 10:

Add the sheet names of each file.

Step 11:

Add the sheet contents

Step 12:

Filter the sheet names with the word "Data" in them.

Step 13:

Remove unnecessary columns.

That's it. Now the contents of each file and each sheet is added to this table as a "Table". If you expand the last column, you will have your results.

Given below is the PowerQuery/M Code for the same.

let
    Source = Folder.Files("C:\Users\Sreenath\Documents\PowerBIDataSource"),
    FileCount = Table.RowCount(Source),
    FileIndexList = List.Generate(()=>0,each _ < FileCount, each _ +1),
    FileIndexTable = Table.FromList(FileIndexList, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    FileAdditions = Table.AddColumn(FileIndexTable,"ContentBinary", each Source{Record.Field(_,"Column1")}[Content]),
    FileAdditions2 = Table.AddColumn(FileAdditions,"FileName", each Source{Record.Field(_,"Column1")}[Name]),
    AddSheetCount =
        Table.AddColumn(
            FileAdditions2,
            "SheetCount",
            each Table.RowCount(Excel.Workbook(Record.Field(_,"ContentBinary"),null,true))
        ),
    #"Changed Type" = Table.TransformColumnTypes(AddSheetCount,{{"SheetCount", Int64.Type}}),
    AddSheetIndices =
        Table.AddColumn(
            #"Changed Type",
            "SheetIndices",
            each let RC = Record.Field(_,"SheetCount") in List.Generate(()=>0,each _ < RC,each _ +1)
        ),
   ExpandIndicesColumn = Table.ExpandListColumn(AddSheetIndices, "SheetIndices"),
   RenameFileIndices = Table.RenameColumns(ExpandIndicesColumn,{{"Column1","FileIndex"}}),
   AddSheetNames = 
    Table.AddColumn(
        RenameFileIndices,
        "Sheet Name",
        each 
        
        let F = Excel.Workbook(Record.Field(_,"ContentBinary"),null,true), S=Record.Field(_,"SheetIndices") 
        in F{S}[Name]
        
        
    ),
   AddSheetContents = 
    Table.AddColumn(
        AddSheetNames,
        "Sheet Contents",
        each 
        let S2 =
        let F = Excel.Workbook(Record.Field(_,"ContentBinary"),null,true), S=Record.Field(_,"SheetIndices") 
        in F{S}[Data]
        in 
        Table.PromoteHeaders(S2, [PromoteAllScalars=true])
    ),
    FilterSheetsWithWord_Data_inIt = Table.SelectRows(AddSheetContents, each Text.Contains([Sheet Name], "Data")),
    RemoveUnnecessaryColuumns = Table.RemoveColumns(FilterSheetsWithWord_Data_inIt,{"ContentBinary","SheetCount"})
in
    RemoveUnnecessaryColuumns

Please remember to change this line 

Source = Folder.Files("C:\Users\Sreenath\Documents\PowerBIDataSource"),

to 

Source = SharePoint.Files("https://SHAREPOINTFOLDER", [ApiVersion = 15]),

 or whatever is your path to the directory/folder.