COMBINE MULTIPLE EXCEL FILES FROM DIFFERENT FOLDERS

[HSK25]

POWER BI GET DATA FROM MULTIPLE EXCEL FILES FROM DIFFERENT FOLDERS HAVING DIFFERENT SHEETS AND COLUMN NAMES

[danextian]

Hi @HSK25 

Hope these help:

https://youtu.be/AzMaf42DmM0 

https://youtu.be/zhCWiIwO8dQ 

[HSK25]

Thanks for a quick reply.

I am sorry i forgot to mention that all the files are in different SharePoint folders. different file names, different worksheet names and different column names. I am interested only in a few columns from each file.

[danextian]

Hi @HSK25 

In the tutorial,  I used a folder connection as it is a lot faster to connect to files locally stored but still the same concept. You just need to change the connector and instead of being able to directly specify the folder in the connection string, you will need to select  the folder by filtering the query.  After that you parse the binary files with xlsx extension as Excel workbooks. Below is a sample M script.

let
    Source = SharePoint.Files("https://mysharepointsite.sharepoint.com/sites/testsite/", [ApiVersion = 15]),
    #"Selected Real Estate Sample folder" = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://mysharepointsite.sharepoint.com/sites/testsite/Shared Documents/testfolder/Real Estate Sample/")),
    #"Selected xlsx only" = Table.SelectRows(#"Selected Real Estate Sample folder", each [Extension] = ".xlsx"),
    #"Removed Other Columns" = Table.SelectColumns(#"Selected xlsx only",{"Name", "Content"}),
    #"Parsed binary as Excel" = Table.AddColumn(#"Removed Other Columns", "Excel.Workbook", each Excel.Workbook([Content])),
    #"Expanded Excel.Workbook" = Table.ExpandTableColumn(#"Parsed binary as Excel", "Excel.Workbook", {"Data", "Item", "Kind"}, {"Excel.Workbook.Data", "Excel.Workbook.Item", "Excel.Workbook.Kind"}),
    #"Selected month worksheets only" = Table.SelectRows(#"Expanded Excel.Workbook", each ([Excel.Workbook.Kind] = "Sheet") and ([Excel.Workbook.Item] <> "2024")),
    #"Promoted the first row as headers" = Table.AddColumn(#"Selected month worksheets only", "Table.PromoteHeaders", each Table.PromoteHeaders([Excel.Workbook.Data], [PromoteAllScalars = true])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Promoted the first row as headers",{"Name", "Excel.Workbook.Item", "Table.PromoteHeaders"}),
    #"Selected a few columns only" = Table.ExpandTableColumn(#"Removed Other Columns1", "Table.PromoteHeaders", {"Serial Number", "Sale Amount", "Property Type", "Assessor Remarks"}, {"Serial Number", "Sale Amount", "Property Type", "Assessor Remarks"})
in
    #"Selected a few columns only"

Here is the link to the sample files I used which you need to upload to your own sharepoint account.

Replace this with your sharepoint site base url: https://mysharepointsite.sharepoint.com/sites/testsite/

And this with the folder path: https://mysharepointsite.sharepoint.com/sites/testsite/Shared Documents/testfolder/Real Estate Sample/