Forum Discussion
Helper IIIConnect to Excel File
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
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 CombineCopy 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
3 Replies
- ImkeF
Community Champion
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.
Jimmy801Hello 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 CombineCopy 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
chahineatallahthanks 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
