How to use Power Query to Combine Multiple Files that have different headings

thanks to one line of code from a gilreve blog it is so easy to make it super safe when you're consolidating multiple files if one of those files has different headings to the other files it'll bring them in i'll show you how straightforward it is let's go this is the scenario i want to consolidate these three files a b and c here they are a b and c okay file a is the first in the folder and if i consolidate these files then this will work fine these three measurement columns be brought in but any subsequent new columns measurement four five measurement six here they don't get brought in i'll show you that happening in a second and it's really one little tweak to your code to make it super safe so that all columns always get brought in okay let's take a look so i'll just start off with the standard get data from file from folder this is the way to consolidate multiple files and this is the folder i'm in desktop the different columns demo this this is the folder with the one i just showed you here okay this is this folder here weirdly power query doesn't actually show that the files in there at this stage you go open it's connecting to that folder and there we go we can see it's connecting and putting in file a b and c i go combine and transform it gives me a chance to have a look at the data tidied up it's sheet 1 from every file and there's sheet 1 but notice this bit first file that's file a in the folder and these are the three columns of file a click okay and brilliantly it's all done it is now consolidated file a file b file c i only had five or six rows of data in each file but the issue is this look all we've got is three columns measurement one two three there are no i can't scroll right there are no extra columns even though file b and c have got different columns so that the trouble really um is the sample file is fine the sample file just goes go to the first file okay navigate to the sheet and then push the headers up and there's no change type step so that's all good you know there's no issue there so that's what it's doing the problem is with this one if i if i go here and i go to the renamed column step okay or i'll go to the removed other columns step here's the three files and if i click in to here we have measurement one two three and if i click into file b we have measurement one four and five and in here we have measurement one two and six but the problem issue is this expand which is this clicking on this little icon basically okay when you do click on this icon it gives you the option to tick what you want to tick but what happens in this expanded step is it only expands the columns from the first file in fact it just expands the columns from the sample file which is the first file in your folder and this is the problem step okay this is the problem however a simple tweak to the code thanks to an article by gail revive i'll put a link to gil's post in the description below it shows you how easy it is to solve this okay so i'm going to go back up and click on this one i'm just going to rename it to make it easier for my formulas i'll call it um pre expand and this is the little trick from gill's post okay first step so we right click and you say drill down and it says it's about to wipe out the subsequent steps okay not a problem just go continue and what we have is now a list okay of tables measurement one two three measurement one four five and so on okay now i wanna get the list of um column names from here and this is where you have to write a little bit of code okay and i am going to read this because i can't remember it so you wrap this okay you wrap this in a list dot transform make sure you open the bracket okay list dot transform you refer to that and then you say each table dot column name open the bracket underscore to represent sort of each record and close the bracket and obviously i've typed something in wrong don't you love there we go the double table i knew i was gonna get caught by that okay enter so now we have a list there's the headings from the first table there's the headings from the second table and so on and then even clever a bit is to get the unique items you can do a list.union list dot union now i know that's a lot okay but that's it that is the trick that one formula that you just write down somewhere and save for the future and come back to it because i won't remember what that is in a you know a week's time press enter there's my list of names okay so these are my headings then i'm going to add an press fx and call and refer to the pre-expand step just to bring this back so pre there we go pre-expand enter i'll just call this ready to expand and then i'll click the expand button now i could wrap all this in one simple step but i like breaking it out into different elements i won't use the original column name and i'll click ok but so you know oh but it's got all these columns in well that's the thing that's the thing we replace we've got the list we need the headings so you just highlight everything in these curly brackets and you replace them with the heading step enter beautiful there we go it's now working that one little formula okay that list dot union list dot transform each table column name all right i know it's too much to remember but just write it down keep it somewhere save this file you know that's it brilliant then i could just you know click on these two columns right click unpivot other columns and i've got my measurement and i've got my value and i can load it if you do need to format these columns then you will need either to reference them which isn't great or there are a few techniques to you know reference change all of them to text or all of them to number um probably a video for another time so i hope you find that useful that's the really easy way i know it's not easy to know what that formula is but hopefully you know one line of code you can just keep that somewhere use it when you need it so thanks to gil revive always good blog posts and stuff from gil um you should definitely get his book um getting transformed data and again i'll put links to that in the blog below so if you enjoy this if you found it useful let me know tell people about this share this channel with people and i will catch you later you

Channel: Access Analytic

Views: 102,293

Keywords: Power Query, Excel, Power BI, Combine, Folders

Id: 09tvia_8ykI

Length: 9min 2sec (542 seconds)

Published: Sat Feb 05 2022