Forum Discussion

juangomez's avatar
juangomez
Helper I
6 years ago
Solved

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 ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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-query/

     

juangomez's avatar
juangomez
Helper I
6 years ago

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's avatar
Jimmy801
Community Champion
6 years ago

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