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 other for Nov and I want to make them one table in power bi desktop.

1 Reply

  • 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"