• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query Get Files from a Folder

You are here: Home / Power Query / Power Query Get Files from a Folder
October 12, 2017 by Mynda Treacy

If you receive your data in separate daily, weekly or monthly files then before you can even begin analyzing the data, you need to consolidate it into one table.

In the past we may have written a Macro to automate this process, but if you didn’t have Macro/VBA skills then you were stuck doing it manually.

Nowadays we can automate this process with Power Query, and the best part is you don’t have to be a programmer to use it because there is a nice ribbon interface, so it’s point and click. And when you get the next period’s file you simply refresh Power Query and it grabs the new data and adds it to the consolidated table for you.

Once we’ve consolidated our files with Power Query we can then load the data into an Excel worksheet, or the Power Pivot data model in Excel or Power BI.

Let’s look at an example. I promise there is no programming required!

Download the Files

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a zip file including an Excel workbook with step by step instructions and the csv files you can use to practice this technique. It does not include the query demonstrated in this post because those files are on my PC.

Power Query Get Files from a Folder - The Data

In the folder below, you can see I have 7 CSV files (tip: you can also get Excel files and Text files from a folder):

Power Query Get Files from a Folder

Important Point: The folder should only contain files that you want to consolidate. That said, you can pick and choose the files you want to import, it’s just safer and cleaner to keep the folder dedicated to your source data files. Trust me.

In this example each CSV file contains 3 columns of data, as you can see below in the January 2017 file:

3 columns of data

Important Point: My files have 3 columns of data, but you can import more. However, each file must contain the same number of columns, with the same names in the same order. If your data is an export from another system, then this should be the case anyway.

Power Query Get Files from a Folder – Excel Steps

The Power Query location is slightly different in Excel 2010 and 2013 compared to Excel 2016, plus Microsoft are updating Excel 2016 all the time. I’ll point out menu differences where relevant, but be prepared for them to change in the future. In fact, some versions of Excel 2016 will appear slightly different to my screenshots, particularly if you’re not on an Office 365 license!

Step 1: Start the Query process.

Excel 2016: Data tab of the ribbon > Get Data > From File > From Folder:

Power Query Get Files from a Folder

Excel 2010/2013: Power Query tab of the ribbon > From File > From Folder:

start the query process 2

Power BI Desktop: Home tab of the ribbon > Get Data > More > File > Folder:

start the query process 3

Note: From here on the process is the same in all versions of Excel & Power BI.

Step 2: Specify the Folder

In the β€˜Folder’ dialog box browse to your folder location, or paste it in and click OK:

specify the folder

Step 3: Select Files

At the next dialog box you’ll see a list of the files in the folder. At this stage you can choose to Load right away, or Edit, or Combine & Edit, Combine & Load, or Combine & Load to (which allows you to specify where you want the data loaded).

I always choose β€˜Edit’, which is the same as Combine & Edit, but only requires one click. It’s the small wins that count 😉

I like to edit my query to make sure the data types have been set correctly, plus it gives me an opportunity to clean the data or add additional columns, and give the query a sensible name before loading it:

select files

Step 4: Expand Content

Clicking Edit in step 3 launches the Power Query Editor window and displays a list of the items in the folder:

expand content

Tip:Β Notice there are some useful columns included in this view that you might want to make use of. More on that another time.

In this window you can filter out any files you don’t need. I want them all so I’ll click the double down arrow on the Content column to extract the data from each of the CSV files:

filter out files

Step 5: Select Settings

Clicking the double down arrow on the Content column will launch the Combine Files dialog box where you can see a preview of the data and:

  1. Specify which file you want Power Query to use as the example file.
  2. Choose the File Origin; this helps Power Query understand what format the dates and currency values will be in etc.
  3. Choose the Delimiter
  4. And whether the first 200 rows will be an adequate sample size

select settings

Once you’re happy with the settings, go ahead and click OK

Step 6: Combine Files

Power Query gets the data from the files you selected in step 4 and consolidates them into one table:

combine files

On the left (image above) is a list of queries that were automatically generated by Power Query when you clicked the Ok button in step 5. There are two items in this list that are important to you:

  1. This is the sample file query. It’s this query that Power Query uses as a template for consolidating all of the files into the final query. This is the query to modify with any changes you want applied to all of the files in the folder.
  2. This is the final query that consolidates the files into one table. This is the query currently displayed in the preview. Modify this query with any final changes or additional columns you want to add etc. prior to loading the data into a worksheet or the Power Pivot data model. E.g. you might want to delete the Source.Name column if you don’t need it, plus it’s always a good idea to make sure the data types for the columns are correct.

On the right-hand side of the Query Editor window (image below) you’ll see the Query settings for the selected query:

  1. I recommend you replace the default query name to something more useful. Just type it in the Name field and press ENTER. I’ll call mine β€˜consolidated_data’.
  2. This is a list of the steps that have been applied to the data to reach the point you see in the preview.

query settings

Step 7: Load Data

When you’re happy with the data in the final query you can load the data.

Power BI Desktop: Home tab > Close & Apply:

load data 1

This will add the data to the Power BI Data Model (Power Pivot).

In Excel: Home tab > Close & Load:

load data 2

Close & Load will load the data to the default location, which is usually an Excel Table in a new worksheet in the Excel file containing the query.

If you want to choose a different location or load it to the Power Pivot data model then choose β€˜Close & Load To…’. This opens the Import Data dialog box (image below) with the defaults selected, which you can edit:

import data dialog

I’ll click OK and you can see the data has been inserted into a new sheet; β€˜Sheet1’ and formatted in an Excel table ready for me to use:

sheet1 data

Notice that the data goes up to July 2017.

Updating the Query with New Data

As you can see in the image above, the data we currently have is for the period January to July 2017. When August’s CSV file gets added to the folder:

updating the query with new data 1

Simply right-click any cell in the Table > Refresh:

updating the query with new data 2

Power Query goes to the folder, gets the new file (actually it gets all files again), runs them through the steps you set up to clean and transform the data and then adds it to your Excel Table:

updating the query with new data 3

I know, it sounds too easy, but honestly that’s all you need to do.

Note: If your data has been loaded to the Power Pivot data model then you can also right-click the query in the Queries & Connections pane in the right-hand side of your Excel window > Refresh (if the Queries & Connections pane isn’t visible you can enable it on the Data tab of the Ribbon):

Queries & Connections

Or click the Refresh All button on the Data tab of the ribbon (note; this will refresh everything!):

refresh all

In Power BI Desktop the Refresh button is on the Home tab:

refresh button

More Power Query

More Power Query tutorials on our Blog.

And if you want to get up to speed quickly, please check out my Power Query course and Power BI Course.

Get help with Power Query questions on our Excel Forum.

More Power Query Posts

power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
Category: Power Query
Previous Post:Label Excel Chart Min and Max
Next Post:Excel Date and Timeexcel date and time

Reader Interactions

Comments

  1. Vanderson Taborda Inacio

    January 22, 2023 at 8:46 am

    Hi Mynda, thanks for tutorial! It was very clearly and didactic.

    One question: It’s possible to import all files from a folder and load each file as a new query instead of combining all files?

    Best wishes!

    Reply
    • Catalin Bombea

      January 23, 2023 at 3:42 am

      Thank you for your feedback!
      Unfortunately no, Import From Folder is meant for combining files, not for splitting.

      Reply
  2. John Zani

    August 23, 2022 at 7:58 pm

    HI
    IF THE SAMPLE FILE YOU USE FOR AUTOMATING PQ IS NOT CONSISTENTLY THE SAME FOR EACH FILE (MONTHLY BANK STATEMENTS) IN THE FOLDER (IT OCCASIONALLY HAS TABLES ON DIFFERENT SHEETS IN EACH OF THE OTHER FILES) WHAT COULD I DO TO CORRECT THIS?

    Reply
    • Mynda Treacy

      August 24, 2022 at 11:14 am

      You could try this technique for getting multiple sheets from multiple files with Power Query.

      Reply
  3. Ray

    August 19, 2022 at 1:32 pm

    Combining the data files into one is extremely useful to my business reports at work. You provided the best explanation I have been searching for days. Thanks for helping people with good solutions which we never know. Please keep on your work.

    Reply
    • Mynda Treacy

      August 19, 2022 at 2:49 pm

      That’s awesome to hear, Ray! Glad I could help.

      Reply
  4. Ian Ripsher

    January 5, 2022 at 9:52 am

    Hi Mynda

    At Step 4, when you’re about to click on the double-down arrow at the top of the Content column to combine the files and create your Sample File query and final query, is there any way you can get it to bring across the metadata in the other columns there, e.g. file name, date accessed, etc? With my query I need to bring across the file name as it contains vital information on the period the data belongs to. Unfortunately this isn’t contained in the actual source data files, but only in the filenames! I think that you allude to this in your Tip (Tip: Notice there are some useful columns included in this view that you might want to make use of. More on that another time.)
    Please can you point me to where you cover this? Basically, I need to have a column in my final query that contains the source file name for each row of data.
    Many thanks
    Ian

    Reply
    • Mynda Treacy

      January 5, 2022 at 12:53 pm

      Hi Ian,

      You can use the technique covered here to keep the file name column when getting files from a folder.

      Mynda

      Reply
      • Ian Ripsher

        January 6, 2022 at 4:42 am

        Thank you so much, Mynda – that worked a treat!

        Reply
        • Mynda Treacy

          January 6, 2022 at 10:13 am

          Great to hear!

          Reply
  5. David Keen

    December 10, 2021 at 6:37 pm

    I get a message when I try to close and load -“Data could not be retrieved from the database. check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again.”
    There is no protection on these files or shares and if I look at the worksheet that it can produce I can see exactly what I want as the data. I can even copy it and paste it to the same file im trying to load to. Have you any ideas what I am doing wrong? Thanks Dave

    Reply
    • Catalin Bombea

      December 11, 2021 at 3:01 pm

      Hi David,
      Hard to see a reason without seeing a file. Are those files old xls or they are xlsx files? If they are xls, I suggest converting them to xlsx and try again.

      Reply
  6. Narasimhan S

    September 16, 2021 at 9:48 pm

    How to make the power query combine I7 to Y7 ( Header) with varying number of rows of all identical excel files ignoring empty rows?

    Reply
    • Mynda Treacy

      September 17, 2021 at 8:52 am

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  7. Archana

    September 7, 2021 at 10:54 pm

    Thanks for solution This works perfectly for me

    Reply
    • Mynda Treacy

      September 8, 2021 at 9:02 am

      Great to hear, Archana!

      Reply
  8. Ron

    August 13, 2021 at 3:21 am

    Hi there I have this issue when I go to
    β€œGet data” then β€œfrom file” β€”> β€œfrom folder” that no DOCUMENTS APPEAR. No excel files, no csv files, nothing at all?! Infact my β€œFrom folder” icon is white not brown like shown above.

    What is the cause of this, I’ve tried with excel files, csv files, everything but everything folder appears empty for both when I search for them?!

    Reply
    • Mynda Treacy

      August 13, 2021 at 10:48 am

      Hi Ron,

      The icon colour will indicate that you have a different version of Excel to the one in the screenshots, but that won’t be the issue.

      Please post your question on our Excel forum where you can also upload screenshots of the folder that contains the Excel/CSV files and the Power Query menu that shows that same folder empty, and we can help you further.

      Reply
  9. Ashley

    April 22, 2021 at 5:55 pm

    My after my step 3 I don’t get the result you get.

    Reply
    • Mynda Treacy

      April 22, 2021 at 8:16 pm

      Please post your question and screenshots on our forum where we can help you further and our answers can also help others: https://www.myonlinetraininghub.com/excel-forum

      Reply
      • sajil pulath

        April 29, 2021 at 8:41 am

        Hi, I have the same problem! I am not getting the options to select Combine/ Load/ Edit etc down in the window after selecting the folder… i searched for the answer in the forum but cant find. appreciated if you could provide the link for the solution.

        Reply
        • Mynda Treacy

          April 29, 2021 at 2:39 pm

          Hi Sajil,

          If you don’t see the same buttons as me it’s because you have a different version of Excel. Depending on how old your version is, you may not be able to use this technique, but you can use this technique to get multiple files from a folder instead.

          Mynda

          Reply
  10. Kurt Vincent

    December 28, 2020 at 10:33 pm

    Hi Mynda,

    it appears that the resutl of the “Folder.Files” function not only yields the files in the folder (given as first paramater of the function), but also files within any subfolder of that folder. In my case, this was an undesired effect and I looked for a way to suppress this. I didn’t find any (other than deleting the subfolders or their files … ), but I noticed the Folder.Files function has a second paramter (options), of which I can find no documentation.
    I suspect the solution to the “subfolder” issue may be found there, however, without docs it’s hard. Hoping you will be able to shed some light. And, BTW, best wishes for 2021 to the team, which I admire for their competence

    Reply
    • Catalin Bombea

      December 29, 2020 at 1:02 am

      Hi Kurt,
      Indeed, Options are not documented. A record is expected in that parameter, something like [Directory=false], but we do not know what options are accepted, nothing works at this moment.
      You can try a simple thing to avoid subfolders: normally you have:
      = Folder.Files(“C:\Your Folder Path”)
      Use path as a parameter, then you will be able to filter the Folder Path column:
      Path= “C:\Your Folder Path”
      Source = Folder.Files(Path)
      Files = Table.SelectRows(Source, each [Folder Path] = Path)

      Reply
      • Kurt Vincent

        January 6, 2021 at 10:21 pm

        Thanks a lot Catalin, that works !

        One minor remark : the value in the [Folder Path] field always seems to end with “\”.
        So, with resptect to your example, I had to use a statement like

        Files = Table.SelectRows(Source, each [Folder Path] = Path & “\”)

        Apart from that, great workaround !

        Reply
        • Catalin Bombea

          January 7, 2021 at 12:36 am

          You’re welcome, glad to hear you managed to make it work!

          Reply
  11. Carol

    December 3, 2020 at 10:16 pm

    HI,
    Hope you can help. I’m working in Office 2016 and using the Get Data to combine multiple csv files (117 files in total). The end result is a file with 490412 rows. This works fine when I run it on my laptop, however, a colleague needs to run the report and is logged on via a Terminal server desktop and every time they run the report it only pulls through 115 of the files. has anyone any idea why it might do this?

    Reply
    • Mynda Treacy

      December 4, 2020 at 3:10 pm

      Hi Carol,

      That sounds odd. I wonder if there’s something specific with the two files it doesn’t import. Are you able to isolate those files and test the query on those two files plus a few others. That way you can figure out if it’s the files that are the problem, or whether there is some limit that’s being hit so only 115 files are imported.

      Mynda

      Reply
  12. Tahj

    November 9, 2020 at 12:45 am

    Stack overflow errors…

    Is there a way to figure out what the errors are?

    Everything worked perfectly until I tried to refresh the query from folder path and then the stack overflow error occurs.

    My query has stripped/red bars just below the column headers and on the left side pane of power query it has the ⚠️ icons.

    Only thing I can think of is start over. But, all my hard data cleanup & transformation would have to be repeated again, correct? Or is there a way to use the applied steps to a new query?

    Thanks

    Reply
    • Philip Treacy

      November 9, 2020 at 9:12 am

      Hi Tahj,

      Without seeing your query and having access to the source data it is hard to say what the problem is.

      That said, there are a few things you can check.

      Firstly the yellow warning triangles beside each query name indicate that there’s a problem with that query – the error you are receiving.

      The striped red bars under the column header indicate that column has errors in it.

      If you see the word Error in a row of a column, you can click beside the word and at the bottom of the screen it will show you the error message.

      Your query is not destroyed/unusable, you can right click on the query name (left hand side of screen) and then click on duplicate to copy it.

      For any query that is generating errors, you can work your way back through the transformation steps starting at the bottom until you get to a step that does not cause an error. You know up to that point everything is working. Then with the next step that cause the error, you should get a message on screen telling you what happened.

      As I said without seeing your query and some screenshots of the messages on your screen it’s hard to troubleshoot.

      Please start a topic on our forum and attach your query (either the Excel workbook or PBIX file) with screenshots and some source data if possible.

      Regards

      Phil

      Reply
  13. Norma Dowell

    October 13, 2020 at 2:19 am

    I am building reports based on a data folder in Box that I need a larger group of people to be able to see. Using Power Query, I was able to get the data for the pivot tables I am building, but it is a set path to the data, meaning anyone else in my company cannot read it if it is not a web address.
    The Box share URL is not an absolute path so I cannot get the data using “From Web” either.
    Maybe I haven’t had enough coffee, but is there a solution to this?

    Reply
    • Mynda Treacy

      October 13, 2020 at 9:42 am

      Hi Norma, presumably you mean Dropbox. If so, please see this same Q&A on our forum for getting data from Dropbox with Power Query. Mynda

      Reply
  14. Raubeeluck

    October 7, 2020 at 6:51 pm

    Very Informative

    Reply
    • Philip Treacy

      October 7, 2020 at 8:00 pm

      Thanks πŸ™‚

      Reply
  15. Timothy

    September 9, 2020 at 1:30 am

    I am working on a project at work that requires me to gather dat from 01.01.18 to present. There is no difficulty in downloading the data into Excel in .xlsx or .csv format. I am comfortable with either format. When I download the data, I get everything from 01.01.18-present. This will be a perpetual project, and need to know if setting up files to import using Power Query, should I break up the original download into month by month files (01-18,02-18 etc.). I would seem logical to take the time and do because I will be updating on a monthly bases going forward, but is it necessary at the start? Is there an advantage to using .xlsx format or .csv format? Thank you in advance for your insight.

    Reply
    • Mynda Treacy

      September 9, 2020 at 8:53 am

      Hi Timothy, As long as your source files have the same column layout and headers it doesn’t matter if the earlier files contain a whole year’s worth of data or just monthly. The only benefit might be that it could be quicker for Power Query to refresh if it has less files to get, but I haven’t tested this theory. Mynda

      Reply
      • Timothy

        September 10, 2020 at 4:43 am

        Thank you! Is there any advantage to use .xlsx format or .csv format?

        Reply
        • Mynda Treacy

          September 10, 2020 at 9:29 am

          Not that I’ve tested. My gut feel is CSV files might be slightly quicker to refresh, but you’d have to test.

          Reply
  16. Nawaf H H AlTaher

    June 24, 2020 at 6:29 pm

    Thank you very much; very cool!

    Question please: After all I am getting error on some columns because the columns have text & numbers. e.g. A350141, ec3566331, P05920196 … etc.

    How and what do to extract CSV without getting errors?

    Thanks a lot!

    Reply
    • Mynda Treacy

      June 24, 2020 at 7:23 pm

      Hi Nawaf, you should set the data type for this column as text since it contains some text along with numbers, which means it can only be a text column.

      Mynda

      Reply
  17. Iris

    June 19, 2020 at 1:12 am

    Hi,
    I have followed all those steps until Step 6. I went on 1. and made the changes I would like applied each time to the Example file meaning : change the type on date columns, delete some columns I have no use for (I cannot select the columns that are in this extract when i extract it, need to do it in Excel)…
    I have a problem with 2. , which shows an error sign saying that one column doesn’t exist (it was the first column of the file, that I had to delete for data privacy).

    How can I correct this error ?

    Do I need to switch columns in Query as a first step (put a date column first and switch place with Customer name column) before deleting Customer name column ?

    Any help is deeply appreciated ! Thanks

    Reply
    • Catalin Bombea

      June 19, 2020 at 1:36 am

      Hi Iris,
      Please upload some sample files and your query file on our forum, will be much easier to provide a solution that will work for you.
      See you there.
      Catalin

      Reply
      • Iris

        June 19, 2020 at 1:50 am

        Hi Catalin,
        Sadly I cannot upload a document as these are business documents private to my company. I am not at liberty to share.

        However I can describe what happened and the “solution” I found.

        The changes I made in Step 6. 1. in “Transform Sample File…” , notably deleting a column or changing its name, weren’t reciprocated into the 2. “Other queries- C:/” part.

        The solution I found was to delete the columns also in the code of “C:/” function . I suppose I can also change manually the names of the initial columns, so that they are written in the exact same way as in “Transform Sample File” after the steps I made to change the names.

        But isn’t it supposed to update on its own ?

        Reply
        • Catalin Bombea

          June 19, 2020 at 4:03 am

          Hi Iris,
          I never recommend uploading sensitive data. But a sample file with a few rows of anonymized data can be provided.
          Anyway, if you think that you cannot prepare sample fake data, I’ll only be able to recommend a few generic solutions you can try.
          “Transform Sample File” you mentioned is a name used by power query automatic combiner, so I guess you used that tool to combine data. If the data structure is always the same, will not be a problem.
          But if the structure is even slightly changed, it will fail to update because all column names are hard typed into automatic queries.
          Use Table.ColumnNames function to expand data, instead of those hard typed lists, this will make your query more flexible.
          Basically, avoid the automatic combiner, it’s not flexible, build your own query.

          Reply
  18. Timothy

    April 25, 2020 at 5:08 am

    In this blog you used .csv files and in your blog “Import Multiple Files Containing Multiple Sheets with Power Query” I noticed you used xlsx files. Are there reasons for using one type verses the other? Is it an application bases decision? Our ERP system downloads data in “Unicode text” and after I tidy the data up I can save either cvs or .xlsx.

    Reply
    • Catalin Bombea

      April 25, 2020 at 12:49 pm

      Hi Timothy,
      Import data from folder works for text files as well, no need to convert them, they can be text/csv, excel files, xml files, json files. More, in Power BI the Import from folder can take data even from pdf files.

      Reply
      • Timothy

        April 28, 2020 at 5:38 am

        Thank you Catalin

        Reply
  19. Karl

    February 4, 2020 at 12:09 am

    I understand how to use get & transform. I love it. My question is: Is there a way to apply the same data transformation rules to multiple files with out combining those files into one.
    I have two csv files created daily and I combine those into one excel file with get and transform and it works great, but I then do the exact same thing the next day. I need each day’s data as it’s own file.
    Is there a way to apply the transformation rules as some sort of template?

    Reply
    • Catalin Bombea

      February 4, 2020 at 4:41 pm

      Hi Karl,
      Not sure way you need to separate them, usually new data needs to be appended to existing data, in order to include the newest data sets in reports. If you need each day data as a separate file with transformed data, store the daily files in subfolders, make a copy of the query file and just change the subfolder path to get data from another data set. You can set some naming standards for subfolder names that can be automated, for example subfolder name like “yyyy-dd-mm” can be automated if in your query file you define a new table with a cell using =NOW() formula, then take this cell into power query as part of the source files path.

      Reply
  20. Mike Rempel

    June 27, 2019 at 12:34 pm

    Mynda,

    I created a summary document from three separate workbooks and created a pivot table from that summary document. If I update the individual files and refresh the summary table, everything updates correctly. However, how do I send all of these to my client so that he can update the files and have the summary document properly update?

    Reply
    • Catalin Bombea

      June 27, 2019 at 1:57 pm

      Hi Mike,
      I would use a table to store the source folder path, and a small code in workbook_open event:
      Thisworkbook.worksheets(“Path”).Range(“a2”).Value=Thisworkbook.Path & “\Source Files” .
      Use this table to set dynamic folder path in your queries.
      Of course, the files folder should be in the same folder with the summary file.

      Reply
      • Mike Rempel

        June 28, 2019 at 3:49 am

        Catalin, I certainly need to improve my VBA skills. However, is it not possible to do the following: send the data files and the summary file to the client. Then that person can open the summary file, go to the Queries & Connections and choose to edit the combined files. Then choose the Source settings and change the location? I tried that but it didn’t work. Is something like this possible?

        Reply
        • Catalin Bombea

          June 28, 2019 at 2:38 pm

          Hi Mike,
          The solution I described will work for client too, if you send him the source files, client stores the data files into a folder named “Source Files”, the folder should be located in the same location with the summary file.
          In your queries, you can make the address dynamic if you replace the address for folder or file location with a dynamic parameter from a table in a sheet, like:
          FolderPath = Excel.CurrentWorkbook(){[Name=”Path”]}[Content]{0}[Folder Path], (this means that you need to have a table named Path, with 1 column “Folder Path”, with the path typed in cell 1 of that column.)
          Then, in query, instead of Folder.Files(“C:\Users\Mike\Source Files”), you will use : Folder.Files(FolderPath) (same with file addresses)
          Now, the query will take the address from the worksheet table, instead of being hard typed in query text.

          let
          Path = Excel.CurrentWorkbook(){[Name="Path"]}[Content]{0}[Folder Path],
          Source = Folder.Files(Path)
          in
          Source

          Reply
          • Peter B

            October 11, 2019 at 8:07 pm

            This is great, it’s been a real game-changer for me in making it so simple (and flexible) to change the location of the source folders…

          • Catalin Bombea

            October 12, 2019 at 1:29 pm

            Glad to hear that.
            You can always use our forum for better support, you can create your own topics there and upload sample files.
            Regards,
            Catalin

  21. Sterling

    June 27, 2019 at 5:41 am

    Mynda,

    How do you handle users sharing an Excel file where some of the queries use the Files from Folder option. The issue I currently see is the initial PowerQuery workbook was created on Bob’s computer and the files reside on OneDrive. Therefore, the file path to the folder is something like: C:\OneDrive\Users\Bob\Data. Everything works great for Bob, he can refresh the workbook with no problem. The problem is when he shares that file with someone on his team (Steve). Steve gets the file which has a number of PivotTables that are using data from queries that originate from Bob. Now, Steve has access to those same files, however, the same path on his computer is different: C:\OneDrive\Users\Steve\Data.

    How do you set up this scenario so that users can share the workbook and the queries that use the Folder Files option? I found a solution using Parameters for connecting to a single file, just not yet for this scenario. Many thanks for all your brilliant work and advice.

    Reply
    • Mynda Treacy

      June 27, 2019 at 6:08 am

      Hi Sterling,

      No easy way to do this because OneDrive file paths when generated with the CELL function (which is how you’d normally automate it) will be https://…. However, if the users’ file path will always be C:\OneDrive\Users\username\Data then you could use a drop down list for the user to choose their name and have it populate the ‘username’ in the file path using CONCATENATE.

      Mynda

      Reply
      • Sterling

        June 28, 2019 at 11:57 pm

        Thanks Mynda! I just need to figure out how to pass into the source string the updated file path based on a dropdown. I know how to setup the concatenate/dropdown piece on the Exce side, just how to pass that properly into the Source string for the powerquery.

        Reply
        • Mynda Treacy

          July 1, 2019 at 2:20 am

          I cover that in my Power Query course.

          Reply
  22. Abdul Rahman Ba Amer

    June 18, 2019 at 5:06 am

    Hello, thanks for your efforts.

    What if the file is outlook data file (extension : .pst) and I want to import list of all emails as if I want to get data from outlook exchange.

    Best regards

    Reply
    • Mynda Treacy

      June 18, 2019 at 9:07 am

      Hi Abdul,

      If your Outlook is Microsoft Exchange then you can connect to it as explained here, but I don’t think it can read the .pst files.

      Mynda

      Reply
  23. Christine Poole

    May 4, 2019 at 6:12 am

    Hi Mynda-
    I was wondering do you have to save the underlying files as CSV? or can you use .xls files?

    Reply
    • Mynda Treacy

      May 4, 2019 at 8:54 am

      Hi Christine,

      Yes, you can use .xls files with this technique too.

      Mynda

      Reply
  24. Pouriya

    March 3, 2019 at 7:33 pm

    Hi, Thanks a lot.
    That’s interesting.

    Reply
  25. Sylvain

    March 1, 2019 at 11:30 am

    Hi Mynda,

    I thought as much. Best to ask. I will exclude the list by doing a Text.length and filter to allow the query to run and then do another query to bring back that which is too long… what to do with these is another question I’ll have to ponder…

    Reply
  26. Sylvain

    March 1, 2019 at 10:35 am

    Hello,

    I have tried this and thank you for the completeness of visuals and text provided. A quick question – as I am getting an error after close & load. I am guessing that if the files in the folder sourced is more than the maximum character limit for file/folder names in XL that it will not load…? Would you have any suggestions as a workaround on this (apart from reducing the file name length – which could be tedious if there are many!)
    Thanks!

    Reply
    • Mynda Treacy

      March 1, 2019 at 11:10 am

      Hi Sylvain,

      I don’t know of a fix or workaround for file names that are too long, sorry.

      Mynda

      Reply
  27. Kevin

    January 15, 2019 at 10:33 pm

    Does this refresh button also add new workbooks that are added to the selected folder?
    Or does it only update the current workbooks that are selected before?

    Reply
    • Catalin Bombea

      January 16, 2019 at 12:18 am

      It will take all the existing files from that folder.
      All you have to do is to make sure they have the same headers structure, otherwise it might break the query.
      Cheers,
      Catalin

      Reply
  28. Frederico Pereira

    December 26, 2018 at 10:00 am

    Hi Mynda,

    Hypothetical scenario ahead.

    In this case, the August file is added to the folder and the table is updated. This only works as long the files have the same structure.

    What if the september file has a different column order, column name, new columns, etc?

    Thanks,
    Frederico

    Reply
    • Mynda Treacy

      December 26, 2018 at 7:03 pm

      Hi Frederico,

      Depending on the steps in your query, it’ll either return an error, or it will add the different columns rather than appending the data.

      Mynda

      Reply
  29. Hughes

    December 3, 2018 at 6:45 pm

    Hi,

    First of all I would like to thank you for the quality of your site and training. Much appreciated.

    I’ve a question.
    I would like to do the same thing in order to populate my dashboard. The only thing different is I do not want to combine files, I would like to delete existing and replace by the new data. In fact, each week, I receive a new file with the existing data, adding the new data from the last week and all weeks like this. Do you have a solution to use a folder and when a new file – with exactly the same structure – arrives in the folder, it’s delete and replace for my dashboard ?

    Thank you for your help.

    Reply
    • Mynda Treacy

      December 3, 2018 at 8:57 pm

      Hi Hughes,

      Power Query can’t delete data as such, but if you replace the file it is getting from the folder with the new file (using the same file name and column structure), then when you refresh the query it will pick up the new file and the old data will no longer be included in the query.

      Mynda

      Reply
      • Hughes

        December 3, 2018 at 10:33 pm

        I will try !
        Thank you for your swift answer. Again, you are doing an excellent job !

        Reply
        • Mynda Treacy

          December 3, 2018 at 10:45 pm

          πŸ™‚ you’re welcome!

          Reply
  30. Tahj

    November 8, 2018 at 3:08 pm

    Step 5: Select Settings

    The option to adjust settings did not appear for me? I was only able to select β€œok”.

    Also, for some when I β€œClose & Load” my query gives an error:
    [Expression.Error] The key didn’t match any rows in the table. Only one of the files actually loads with data.

    Reply
    • Mynda Treacy

      November 8, 2018 at 9:09 pm

      Hi Tahj,

      If you don’t see the options in step 5 then you have an old version of Power Query. You need to update Excel or download the Power Query add-in if you’re running Excel 2010.

      Mynda

      Reply
      • Tahj

        November 9, 2018 at 6:07 am

        Also, when pulling files from a folder. Does the saved file names have to be in the same format (i.e. purchases march, purchases february, etc) OR
        does the tables/data within the excel files have to be formatted with same column headers, in same order, and same headers, and tables with same naming prefix?
        That could have been my issue…
        I’m using excel 2010 and have the Power Query and Power Pivot add ins.
        Thank you

        Reply
        • Mynda Treacy

          November 9, 2018 at 8:57 am

          Hi Tahj,

          Great to see you’re giving Power Query a go. Yes, the file format need to be the same in each file (each file must contain the same number of columns, with the same names in the same order). That’s how Power Query knows which columns of data get appended to one another.

          Mynda

          Reply
  31. Joe Paredes

    June 13, 2018 at 2:33 pm

    Hello Mynda, I have a folder with several excel files extracted from SSRS. I am including the period within the file names so I can use the source column to split it and get period. Example FILENAME_20180430.xlsx I duplicate the column and leave only the date reference to create relationship to calendar table. Works great. The problem I am having is that when I try to add prior period data i.e. FILENAME_20180331.xlsx into folder when I refresh I receive the “We couldn’t refresh the connection because the column FILENAME_20180430.xlsx of the table was not found. I think the problem is that when I promote to header the first row it is pulling the source file name into the header so when a prior period is added it is using it as first sample file but the name is …0331…instead of …0430…so it kicks out an error. I have tried to tell it to use a specific file as sample file but when I add prior period file it gives me error. Any help is appreciated.

    Reply
    • Mynda Treacy

      June 13, 2018 at 4:30 pm

      Hi Joe,

      Are you promoting the first row as header in the ‘Transform Sample file…’ query or the final query under the ‘Other Queries’ folder? It should be done in the ‘Transform Sample file…’ query.

      Mynda

      Reply
      • Joe Paredes

        June 13, 2018 at 11:44 pm

        Hi Mynda,
        I transformed it on the final query. So can I go to sample query and edit it so it uses a specific sample file and not the first one. I have the power pivot model constructed even using CUBEMEMBER to fetch data so I would hate to rebuild and lose a lot of work.

        Thanks

        Reply
        • Mynda Treacy

          June 14, 2018 at 8:44 am

          Hi Joe,

          You should be able to remove the step from the final query and modify the sample. As long as you don’t change the column headings of the final query loaded into Power Pivot it should be fine. Just make a copy of your file to be on the safe side.

          Mynda

          Reply
  32. Vanessa Cuomo

    April 13, 2018 at 5:29 am

    Hello Mynda, I have a .csv column that contains commas. How do I keep from separating the data in that one column by commas? I use the load csv and delimit by commas.
    Thanks

    Reply
    • Mynda Treacy

      April 13, 2018 at 9:26 am

      Hi Vanessa,

      I’m not sure you can stop it separating one comma delimited and not the others, but you can join those columns back together once you import it and add a comma separator with the Merge Columns tool.

      Mynda

      Reply
  33. Vaughan

    February 26, 2018 at 2:08 pm

    Hi Mynda,

    Very useful as always.
    I’m using this to combine system generated csv downloads that are dropped into a folder by our ERP system.

    The problem is that the first three rows are populated with a report header and not the column headings. I’f I go through the folder and delete the first three rows bingo all is right with the World – but I’d prefer to eliminate this step if possible.

    Is there a way to skip the first three rows of each file?

    Thanks
    Vaughan

    Reply
    • Mynda Treacy

      February 26, 2018 at 2:35 pm

      Hi Vaughan,

      In the tutorial above see step 6.1; you can edit the ‘Transform Sample File…’ query. Then in the Applied Steps pane remove the ‘Promoted Headers’ step so you can fix those first 3 rows. The changes you make to this query get applied to all files you import.

      If you get stuck, please post your question on our Excel Forum, where you can also upload your sample files.

      Mynda

      Reply
  34. Casey Early

    November 25, 2017 at 2:43 am

    Hi Mynda

    Great post.

    I am trying to pull text files from a folder and would like to preserve the ‘Date created’ column as this will drive an accounting journal.

    However, when I click the double down arrow on the Content column it jumps past the ‘Combined Binaries’ step (which displays an icon) into an Imported Text step.

    Do I need to add some code to preserve the ‘Date created’ column?

    I don’t have the ‘Combine’ option on the initial screen so maybe my version of PQ (or excel) is the issue!

    Best wishes,
    Casey

    Reply
    • Mynda Treacy

      November 25, 2017 at 8:39 am

      Hi Casey,

      It sounds like you have an old version of Power Query. You just need to download the update and you should see the same interface as me.

      Mynda

      Reply
  35. MF

    October 15, 2017 at 5:52 pm

    HI Mynda,
    I have a problem… my problem is not about combining data from csv. files. My problem is about using the combined data in Pivot Table. The problem arises when I create a Pivot Table that connects to the combined Data (>1M rows of data). I’d got an error message saying something like “Problem in obtaining data”. I am using Excel 2010 (32 bit) with Power Query installed. Not sure if you have encountered this kind of problem? Appreciate your advice.

    Reply
    • Mynda Treacy

      October 15, 2017 at 6:53 pm

      Hi MF,

      Have you tried loading the data to Power Pivot and creating a PivotTable from there? That said, you need 64-bit Excel to handle 1M+ rows of data.

      Mynda

      Reply
      • MF

        October 15, 2017 at 8:12 pm

        Hi Mynda,

        Thanks for your prompt reply.

        I haven’t tried using Power Pivot to create the PivotTable as I guess I am the only person with Power Pivot installed in my company… believe it or not? haha.

        I also thought about the 32-bit issue… but again that’s something out of my control in office. πŸ™

        Reply
        • Mynda Treacy

          October 15, 2017 at 9:49 pm

          That’s a shame πŸ™

          Reply
  36. at

    October 13, 2017 at 11:25 am

    I have to go through combine and edit if I want to retain my file name – I then use power query to pull the first part of my file name as part of my data. If I go straight to edit, I lose the file (source) name. It’s not a big deal, but do you have any idea why that happens? I have excel 2016

    Reply
    • Mynda Treacy

      October 13, 2017 at 12:21 pm

      Hi Amelia,

      No idea why that would be other than maybe you have an old version of Excel 2016. Are you able to update it?

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x