Forum Discussion
Import Multiple Tables from Excel in a Single Query
Hi Everyone
I am currently importing several tables from Excel Workbooks. And it is distributed in several sheets.
I have 3 different sheets:
- Volume
- Sales
- Units
Each sheet is a big horizontal (by month) table which is input by several people.
Currenty I am importing each sheet in different tables, converting it to BI format and the merge in a single table for use:
- month
- product
- Volume
- Sales
- Units
So currently I have to import 3 non-usable tables to get the usable one.
The next step is that I have to do the same thing for around 20 different Excel files. So I would have to import 60 non-usable tables to get 20 usable ones.
I'm looking for a way to import all 3 tables, process them and output a single table per workbook in one code so I don't have all that trash in the final model.
Thank you
Juan Diego
Hi juangomez ,
We can get data by folder to work around.
let Source = Folder.Files("D:\Case\20200212\testfolder"), #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Folder Path", "Attributes"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Excel.Workbook([Content],true)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}), #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Data", {"a", "b", "c"}, {"Custom.Data.a", "Custom.Data.b", "Custom.Data.c"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.Data",{"Content"}) in #"Removed Columns1"For more details, please refer to the third - party article.
6 Replies
Hi juangomez ,
We can get data by folder to work around.
let Source = Folder.Files("D:\Case\20200212\testfolder"), #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Folder Path", "Attributes"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Excel.Workbook([Content],true)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}), #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Data", {"a", "b", "c"}, {"Custom.Data.a", "Custom.Data.b", "Custom.Data.c"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.Data",{"Content"}) in #"Removed Columns1"For more details, please refer to the third - party article.
Hello
Thanks for your answer, will create the additional "File" table to import data.
One question, can this Folder.files be linked to One Drive folder the same as are the Excel files are linked and may be updated?
Juan Diego
Hello juangomez
you can create 3 function that take the complete file path as input and processes your 3 sheets.
Then you query a folder where all you 20 Files are stored and invoke your 3 custom function in 3 new columns for every file. After that you can combine every new column in a new query and use only this 3 queries for your datamodel so no trash nowhere 🙂
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
JimmyHello
I have been able to do the single function to process all the sheets, thanks.
I have a new question, the first parameter of the function is the complete file path as 'text'.
But then, it doesn't identify the data source (in 'Data Source Settings') only displaying ('Some data sources may not be listed because of hand-authored queries').
I believe this means that when I upload the report and try to (periodically) update from files in One Drive it won't process.
I think there must be another way to deliver that first parameter correctly as a source, but haven't been able to find it.
Appreciate you help
Juan Diego
Hello juangomez
your function should look like something like this
(CompletePath)=> let Source = Excel.Workbook(File.Contents(CompletePath......and when querying the the folder you can invoke it for every file like this
YourFunctionName([Path]&[Name])
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
