Import Multiple Tables from Excel in a Single Query

[juangomez]

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

[v-frfei-msft · Accepted solution]

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.

https://www.howtoexcel.org/power-query/how-to-import-multiple-files-with-multiple-sheets-in-power-qu...

[juangomez]

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

[Jimmy801]

Hello @juangomez 

Onedrive can be queried by Web.Contents I think.

But is for sure possible.

Jimmy

[Jimmy801]

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

Jimmy

[juangomez]

Hello

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

[Jimmy801]

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