Managing changing column names in Power Query #10: (M)agic (M)ondays

so hello and good evening this is Reposado from crippled calm and today I have a really nice treat for you in power query if you ever had a sores that keep changing the column headers you know the name of the column and you know if that happens your refresh will break I am going to show you today a trick to avoid that so we will learn how to manage column headers where the name keeps on changing stay in tune for this trick okay so first of all I need to give proper credit to this solution because I found it in this power bi user group blog post and I found actually at the end that it was created by Lars Lars Shrivers Schriver sorry him saying your name totally wrong but he is actually the one that work out the solution I'm just going to show you I just used it and I thought it was absolutely brilliant so I'm going to show you what he did I would give you a link to his blog post and there you can download his sample is very similar to mine so you won't get lost but without any further delay let's start with the tutorial okay so here is an example I have a very simple table that has two columns one column that is called sales and a date and then another column that is actual days for the sale and as you can see this column has a header with a date so the let's say that the user likes to update it okay so every time the new sales files come the Heather will change with a new date so what I have done is I actually imported the excel file into power bi nothing strange there and as you can see it works perfectly and right now I want to change the header name to sales I don't want the users to see when the data was is not interested unfortunately isn't the Heather name so I just changed rename the column now what happens when the Heather name changes let's check that out so now as you can see we have another file and this is not for January itself for February so the Heather name changes let's do a refresh in power bi and see what will happen so we come here we do refresh preview it's good if we save the file just so we hit preview and as you can see it says the column name sales 2017 Oh 101 wasn't found so there is an error so what happened as you can see here when we rename the column it actually took the name of the column and with the name of the column change then of course we got this error so let's change it back you see you can see how this'll work and then we will do another test so now it change the name again we do a refresh and then everything is working again so what we want to do now is to make sure we capture this name in a dynamic way so here it doesn't say the action name but it just refers to a column so how do we do this we we will do is we go to the advanced editor and where to start doing this we have to do it after the headers have been promoted right so the first step is sewers and peak she is and then the headers have been promoted so this is the step where we have to capture the name we go again to the advanced editor and after promoted headers we are going to do the following dynamic name header for example and this is a function table column names now we have to call the step above we put it in there and come on let's try it out and see what that function does so here we have the dynamic had a name and as you can see give us a list of the names of our headers which is great but what we want is to pick the first one this is even if you say is here one for power query this is zero and this is wanted to pick the name what we need to do is go back to the editor going here and then we have to say that it is the first item on the list that we want to capture so the first item starts with zero that would be zero let's take out so if you can see now we have a function that refers to the first header of our list and you can do with all headers of course if I will put one here I will get the second one okay but now is the first one that we want and now when we go here to change types as you can see what this is doing is changing the field type the sadducees date and this is number and is referring to the horror coded one and we don't want that what we want now is this one so we are actually going to change instead of having the hard-coded one we are going to have a function that stores the header name and we're going to do exactly the same when we are renaming the column so here we remove that and we put that one and as you can see everything is working and nothing that's broken VFW's so now that we have the function we're going to go back to excel change the name to your refresh and hopefully it would work let's check that out okay so here we are in Excel we are going to give this a new name we save and now we go back to power bi and now that we are here let's do a refresh and see if it works nothing broke now did you change the name you can actually if you go back you can see that the name has changed so it actually took the new header but because it's not referring to a hard-coded name is actually working I actually wish that it was the you know the way that power query would actually read the column names it's a bit of a pain when that happens is quite often that refresh schedule refreshes or normal refreshes break just because something changed from the header name soon it's a bit of a pain but not a big thing but anyhow if there is something that you know is happening to your model now you have a solution now you know how to fix it so this is really good isn't it so thank you Lars amazing trick okay so this is all for today if you liked the video let me know by liking it or sharing it if you have any comments questions suggestions about the video let me know in the comment box or any of the social channels listed below and make sure you subscribe I publish power bi videos every Monday Wednesday and Friday and on Fridays we talk always about tax if you want to receive notifications of my future videos make sure you click the bell because the YouTube does not send you notifications anymore unless you activate them yourself okay so have a great night bye

Channel: Curbal

Views: 71,089

Keywords: power, power bi designer, curbal, excel bi, excel, powerbi, changing header names power query breaks, Managing changing column headers in Power Query, bi, Curbal.com, Power bi, power bi desktop, Curbal, cubal, power bi video tutorial

Id: yBJr0sAc-m4

Length: 8min 46sec (526 seconds)

Published: Mon Jul 10 2017