Power Query: Connect to Files in a List | Excel Off The Grid

power query has a really powerful feature that lets us combine all the files in a folder but what if the files that we want to connect to are not contained in a single folder what if they're all over the network we can't connect to an entire server and just filter down to the files that we want instead we need to find out how we can connect to the files based on a list and that's what we're looking at in this video so if you're ready let's get started okay let's suggest that we want to combine four files you can see them here in this table each of those files has a different folder path so it might be in 2022 p12 or 2023 P1 Etc now there may be lots of other files in these folder paths so therefore we can't connect to an entire folder just because there's too much stuff in there now let's just take a look at the first file here's an example here it's a trial balance file and it contains account code an account and some value information and all the other files are exactly the same so therefore it's fine for us to combine them you'll also notice that this has a tab called December and that's because it's the December trial balance each of our workbooks has a different tab name in each workbook so therefore in our table we've got the file path but also the name of the sheet that we want to connect to let's start by loading this table into Power query so I click on the table and then from data I'll go to from table slash range so here's our table loaded into Power query we have the file path the sheet name and the date column let me just change that to a date to data type and I will replace that current step next we want to get the code that helps us to connect to a single workbook so to do that I'll right click in the queries pane go to new query file and then Excel workbook I'll navigate to the place where one of those files is saved I'll select it and then click import in the Navigator window I'll select the name of the worksheet and then click ok now that this data is loaded let's make the transformations to this workbook just going to remove the previous two steps then I'll come up to remove rows remove top rows and then to four in there and click ok next I can promote the headers that's apply the data types automatically I'm happy to stick with those I've got a nil at the bottom so I'll just filter that out now in your scenario your Transformations will be different the key point is that we've got a single workbook that is now laid out exactly as we want the data because now I can go up to view and advanced editor and I'm going to select all of the code and copy it and I'll click done to close that and then I'll head up into my list of files so here's my source files query and I'm going to click add column and then custom column in my custom column I'll call this data and in the formula I'm just going to paste in the code that we copied previously now there's a few amendments that we need to make to this code first of all we've got the hard-coded file path well we don't want that we want whatever the file path is in our file path column so I'll delete that and then double click on file path in the available columns list I'm going to do the same with the item name so that's now replaced all of the parameters that were used to determine what data we want to connect to and when I click ok you'll see I now have a column called data and inside each of those I have a table that connects to that relevant file I can now expand the column I don't want to use the original column names prefix so I'll uncheck that and then click OK fantastic and that is now loaded all of the data from all of those worksheets so there's December's January February and March let's just change these data types for this example I'm happy to change them to the data types that they were previously so a whole number for account code text for account and value can be a decimal number we don't need our individual workbook query anymore so I'll select that and press delete yes I want to delete that query and then I can go to file closing load two let's load that as a table on a new worksheet and then click ok fantastic we've now got that loaded into Excel and if we were to add a new row into this table it would then add that data into our final query as well well that's it that's how we can use power query to combine all the files based on a list now if you would like to take your power query skills to the next level why not check out our training academy where we have a power query course to get you up to speed with everything in power query to learn more head over to exceloffthegrid.com forward slash Academy thanks for watching and I'll catch you next time

Channel: Excel Off The Grid

Views: 5,590

Keywords:

Id: 23nulGrMBAM

Length: 5min 50sec (350 seconds)

Published: Thu Jul 06 2023