Forum Discussion

Abdelrhman_2020's avatar
Abdelrhman_2020
Frequent Visitor
6 years ago

Get Excels Files From a Folder Without Combining it

Hello,

I want to Get Excel Files from a Folder without Combining it to one table

I want to get each Sheet from the Workbooks as a Tables Seperatly

is there any

Need Help

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Abdelrhman_2020 

     

    Based on your description, I'd like to suggest your create a function in Power Query as below.

     

    (Path as text,ExcelName as text,SheetName as text)=> 
let
    Source = Folder.Files(Path),
    PathStep = Source{[#"Folder Path"=Path,Name=ExcelName]}[Content],
    #"Imported Excel" = Excel.Workbook(PathStep),
    SheetStep = #"Imported Excel"{[Item=SheetName,Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(SheetStep, [PromoteAllScalars=true])
in
   #"Promoted Headers"

     

     

    You may input a set of parameters and get a a corresponding query. After executing multiple times, you can get tables what you want.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Abdelrhman_2020's avatar
      Abdelrhman_2020
      Frequent Visitor
      6 years ago

      Thanks for your Reply 

      What should I fill in the Excel Name and Sheet Name

      as i have a Several Excel Workbooks in my Folder and I have a lot of Sheets in it