Connect to Excel File

[chahineatallah]

Hello everyone

I started to use power bi, now i have one question, when i go to get data and then select excel file, and load it , it loads but it asks me which tables /sheets i need to load

what if my excel file is dynamic, like other people are adding tables/sheets, is there a way as in power query excel.currentworkbook function and it used to load all my sheets/tables automatically, i used to open my excel file ,then add blank query and write the formula excel.currentworkbook(), then it showed me all tables and loaded them automatically

is there a way in power bi to do same? like i dont want to manually select the tables 

[Jimmy801 · Accepted solution]

Hello @chahineatallah 

you can use this code to get every data from your file as combined table. However you have a problem when your are using tables, because this data is read twice, one tome from the sheet and one time from table. To avoid this you would need to define a rule for your workbooks to work only with tables or sheets. In this scenario you can filter the source step on the column "kind" what kind of data source you are accepting in your process

let
    Source = Excel.Workbook(File.Contents(YourFilePath), null, true),
    #"Added Custom" = Table.AddColumn(Source, "ContentTable", each if [Kind]="Sheet" then Table.PromoteHeaders([Data]) else if [Kind]="Table" then [Data] else #table({""},{{""}})),
    Combine=Table.Combine(#"Added Custom"[ContentTable])
in
    Combine

Copy paste this code to the advanced editor in a new blank query to see how the solution works. You have to insert the complete path of your files however. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[chahineatallah]

thanks jimmy, what i did actually, i pressed on the source in power bi and it showed me all my tables /sheets, i dont like codes specially m code as its very case sensitive lol

and then i filtered what i needed 

[ImkeF]

Hi @chahineatallah ,
this is only possible if you're going to return all the different elements in one query (it will basically use the from-folder-method then).

But if you want to create a single query for each Excel-element, then a dynamic approach is not possible.