Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power BI

Hello   I have multiple data sources saved in SharePoint which is an excel sheet, and I want to combine multi excel sheet in one table.   for example, I have data for Sep, a sheet for Oct and oth...
smpa01's avatar
smpa01
Community Champion
6 years ago

Are you still having this issue? You can put all the excels in a sharepoint folder and bind the content of the folder (excels) to come up with a single table using power query.

 

e.g.  if your sharpoint URL is

https://xxx.sharepoint.com/teams/Analytics and you want to combine the contents of a folder called "Budget"

 

then

 

let
    Source = SharePoint.Files("https://xxx.sharepoint.com/teams/Analytics", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Budget")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data"}, {"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Expanded Custom", "Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
    #"Expanded Data"