Forum Discussion

jaryszek's avatar
jaryszek
Icon for Super User rankSuper User
5 years ago
Solved

Importing all files from folder into workbook and create seperate queries from them

Hi Guys,

 

i want to import all files from folder and from each file create seperate query with known name.

So for example if file is called File1 i would like to import it from folder, call ImportFile1, if file is called File2 i would like to repeat step and so on. 
Can you please help?

Best,
Jacek

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Why create separate queries vs. appending your data together into one query/table?  In any case, I believe you will need to pre-create queries for as many files as you may have.  It could still be dynamic in that you would refernce the 1st, 2nd, 3rd, etc. record from the files query to split them into separate queries (e.g., filesquery{0} ).

     

    Pat

     

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Why create separate queries vs. appending your data together into one query/table?  In any case, I believe you will need to pre-create queries for as many files as you may have.  It could still be dynamic in that you would refernce the 1st, 2nd, 3rd, etc. record from the files query to split them into separate queries (e.g., filesquery{0} ).

     

    Pat

     

  • jaryszek's avatar
    jaryszek
    Icon for Super User rankSuper User
    5 years ago

    Hi mahoneypat 

     

    Thank you. 
    You mean to have one big table with all columns from all tables? I have about 15 tables. 

    How you would refer to them and create seperate queries? I mean this filesquery{0} - can you please provide any example?

    Best,
    Jacek


    • mahoneypat's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      I was assuming your files had the same columns.  If they are different, then I agree different tables are likely needed.  However, i am still confused why you would like to dynamically create queries/tables for each table.  Each might still need other transformations that you wouldn't be able to automate/predict.  Can you clarify?  Why not manually create your 15 tables?

      Pat

       

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi jaryszek ,

    If you want to import specific file contents from folders seperatly, you can try this:

    1. Connect to your folder in power query:

    = Folder.Files("C:\Users\xxx\xxx\xxx")

    You will get the whole folder fields like contents, Name etc.

    2. Create a custom function like this:

    (filename as text) =>
let
    Source = Table.SelectRows(#"your folder table",each [Name] = filename)
in
    Source

    You can enter the file name in this function to import yout sepcific file contents seperatly and extract the table fields in it.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.