get data from multiple Excel sheets in one file hosted in Sharepoint

[Manimalinc]

Hi Everyone

I hope you can help me here

so I have an excel file multilple worksheets( Matt, Anna, Carsten ,Dimitry etc etc )  and  all same data format   (person,date,booking Type,Product,Customer,Status etc ,etc )

I can grab all this in a simple single query in Powerbi, using "from Excel" however as its a local file  i cannot have automated refresh. 

let
Source = Excel.Workbook(File.Contents("C:\Users\Matt\Documents\Meetinginfo.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each [Name] = "Matt" or [Name] = "Anna" or [Name] = "Carsten" or [Name] = "Dimitry" ),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Column1", "Column10", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column10", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Agent", type text}, {"Date", type any}, {"booking type", type text}, {"customer", type text}, {"Partner", type text}, {"Requestor", type text}, {"status", type text}, {"comments", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Agent] <> null and [Agent] <> "Agent") and ([Date] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Anna", "Agent", "Language", "Date", "booking type", "Product", "customer", "Partner", "Requestor", "status", "comments"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}}),
in
#"Removed Columns"

So i save it in Sharepoint, but how do i now make the query filter by "the file" - I have to do this first right ? 

AND then the sheets 
I hope this makes sence. In essence i want a multi sheet excel file report that I can aut refresh ...

[Manimalinc]

I think I have solved it!!  Noting here for anyone else passing by .....

Once you filter on your specific file from the SharePoint folder , 

remove all columns except "Binary" 

(1)  Click "Combine files" button. 

in the "Combine files" pop out window

(2) Right click on the file  ( in my case called perameter2 ... Not sure why ... )

and select transform data

Now you have the same starting point as a local Excel file, and it will allow a scheduled refresh !!!

[ppm1 · Accepted solution]

Note that you also could have just clicked on the "Binay" hyperlink to drill into the Excel workbook (and not create all the extra stuff that comes from the Combine & Transform functionality.

Pat

[Manimalinc]

Hi Pat

now I see this also "Right click" on "Binary" and select drilldown !!

thanks - thats even quicker 

[Manimalinc]

Hi Pat 

Thanks but my issue is once I have filtered on the specific file, I do not seem to have the option to "filter" again on "sheets".

[ppm1]

You can use Web.Contents to get to a specific Excel file on SharePoint, but I usually just use the SharePoint Folder connector (uses the site URL) and navigate to the file that way. From there, your query steps would be the same.

Pat