Refresh breaks due to COLUMN NAME CHANGES? 3 ways to fix changing column headers in Power BI!

just imagine you think you have built the perfect query and then one week later you run a refresh and it breaks because one column name in the data source changed how can we deal with this in this video you're gonna find out [Music] my name is boss from how to power bi if you're new to this channel and you're looking for ways to improve your power bi skills then make sure to hit that subscribe button now let's get started i connected to an excel file that has three columns country department and revenues but for the revenues we don't just have the revenues in the header we have total revenues week 43 so there's also a week indicator and let's say next week we're going to have the same three columns rather the header will change for the revenues because then we have week 44. so i'm gonna jump back to power bi and click on refresh preview and after running the refresh it returns an error and the reason is that some of the steps that i have in my query are referring to total revenues week 43 which is not there anymore because it's now week 44. so in this case specifically is the change type step so what to do now well let's have a look at option number one now i can go here to apply steps now let's first remove the change type step and also the promoted header step now at this point we have in the first row the headers but we are not going to use that one what we're going to do instead is manually write the other name for each column okay so here we're going to have exactly the same country country then we have department for the department column and now for column three where we usually would have total revenues week 44 we're just going to write revenues and now we can simply get rid of the first row by going to the home tab and then remove rows remove top rows and i want to remove the first one so now that we have removed the top row we can select all of the columns go to transform and then detect the data types and let's see if the solution still works when we update the column adder so i'm going to go back to my excel file i'm going to change week 44 back to 43. i'm going to save it and then i'm going to run the refresh in power bi and you see no problem now that worked however we have to name every single column and if you have a lot of columns this can be quite a hassle so instead of that i'm going to show you another solution where you don't have to rename every single column so the second method will enable us to basically say okay rename column three so we make a reference to the column number instead of the column name so here we are at the beginning again i connected to the excel file you see we have total revenues week 43 and the problem is we have the change type step because week 43 will turn into week 44 so i'm gonna get rid of that one first okay now different from the first option is that i'm gonna leave in the promoted header step so what we're gonna do next is to get an overview of all of the column adders and save it as a list okay so to do this you simply go over here to the formula bar now if you don't have the formula bar you first have to go to view and then turn on the formula bar now once you have that we're going to insert a new step now here we're going to use a function that's called table column names now make sure you use the intellisense feature and so by pressing ctrl space it also shows you the description so it returns the column names as a list okay so one column that has items so to select it press tab then we want to refer to the promoted address and step and the table that got returned at that point close the brackets and now you see we have a list that contains all of the column address country department total revenues week 43. if i want to refer to a specific item in that list now in this case that is going to be total revenues week 43 then you can go back to your formula bar and then make use of these curly brackets and then type in the row of the item number okay so here you probably think ah that's then row three so i'm going to type in a free however you see that returns an error because m and so the language of that power query uses starts from zero okay so i'm gonna have i have to type in here two to get the item that's on the third row okay so now we know how to get the header of a certain column how can we use this well first of all after doing this i have to insert another step and here i want to have the table that got returned for the promoted address step so just type in promote headers use the intellisense press enter you see here we have exactly the same as for the promoted header step and now we want to rename total revenues okay so here let's double click on that adder and rename it to revenues now that gives you the m code for changing the column headers okay now you see that it refers to total revenues week 43 and that is the part that we now need to replace with the code that we wrote before so let's jump back to that step which is still called custom one and here you see how we can get the header that we want to edit okay so i'm just gonna copy that without the equal sign then i'm gonna go back to my last tab and i'm going to replace total revenues week 43 with that code and also include the quotation marks just copy paste and you see it still works okay so now that we renamed the column header for column three which is then revenues we can change the data type over here so transform the tag data type and now i can jump back to my excel file and update the week number in the revenues header and run the refresh in power bi okay so here i'm going to update it to week 44 save the excel file i'm going to jump back to power bi and over here in the home tab i'm going to click the refresh button and see it still works now to clean up the query a little bit i can remove custom one custom two so custom one was just to show you how we could get a list and then we turn a certain item in that list so i'm going to remove that and custom two we also don't need anymore now what if in a data set we also have another column that has the week number in it how can we then update the query so that still works now here back in power bi you see that we have the total span week 44 column and what i want to do is rename it so i'm going to go to the renamed column step and override that one with expand and if you rename multiple columns let's say you would also rename department to tap and then insert then you see that it doesn't add an additional step but instead of that you have here the function table dot rename columns then refers back to the previous step and here you see that you have a list of lists so a list is always shown in between curly brackets so here you have the original column name and then the column name that you want to have and then for the second column exactly the same thing okay so how can we use that now well what we need is we want to copy this one over this list where it says total span week 44 and replace that with spam we're going to copy that i'm going to go back to my original renamed column step and then just after the closing brackets of the first one so make sure that you are in between i did two curly brackets i'm gonna add over here this list that i just copied from the other step and you see now i am also renaming this band column however we are specifically referring here to week 44 so instead of that i want to replace that with what we created before so now i'm gonna do exactly the same as what we did before so we have the function table.column names returns all of the column headers and then we want to have now before we wanted to have the third row but here for spans we want to have the fourth row okay so i'm just gonna copy that code over and replace this total span week 44 with the same code just update over here the item number that we want to retrieve with a three now the second rename column step i don't need anymore so i'm just going to get rid of that over here i also have to redo the change type step so i'm going to take all of my columns transform and then detect the data types and that's it so also if you have multiple columns you can use the same technique you just have to repeat the same process so you see that the second solution is a little bit more complicated however the upside is that you don't have to rename every single column okay however there's still one problem that we need to address and that is what if the revenues column might change from column three to column four or five well then your query will break again so we need to add a few more steps to our creamy to make it even more dynamic now let's go back to power bi and see when you refresh the query that you will get errors for the revenues column because it's not gone three anymore it's changed to column four and the span column changed into column five now what to do well first of all i'm gonna remove the change type step and i'm also then gonna go one step before renaming the columns okay and here i want to insert a new step in between and let's start off again with returning the column headers so over here table dot column names and then i'm referring to the promoted headers tab that gives me all of the headers so the next step that i'm going to create we'll find in this list the row number on which we have the item that contains revenues okay so i'm going to insert a new step again now just like before this returns us all of the address that we have so i'm going to rename this step to headers and now i want to figure out okay on which role do we have the item that contains revenues so let's insert a new step and here we're going to use a function that's called list dot transform returns a new list of values computed from this list now let's see how we can use that so let's select it by pressing tab okay now the list that we want to transform is the address list okay so here we just simply refer back to adders and now we need to have our transformation okay so here we want to basically go down on list and then every time check if it contains revenues now we can do this with statement each and then we can use the function text contains now returns whether the text contains a certain substring now open the bracket now here we want to check every time the value that's in that list and to refer to that value you simply use an underscore then comma and here we can type in revenues for the substring that we are looking for then we need two closing brackets one for the tax contains function and one for the list transform function you see it gives me now a list with the same number of rows but only with false and true now you see for every column header that does not contain revenues we have false but then we have over here the column added for the revenues where it returns true now the next thing that we can do then is figure out okay what is the position of true in that list so now we can go back to a formula and use another function that figures out on which row we have the true value okay now the function that we're going to use is called list and then position off now open bracket now what does it need first of all it needs a list well that's that's what we have here and then we can go all the way to the end comma and then the value that we are looking for which is true now make sure that you do not type it like this in between quotation marks but just type in true and then close the list position off function and you see it returns three which means it's in the fourth row okay so this function so this formula basically figures out what the column number is for the other that we want to change okay now we did it in two steps which we can of course also combine so i'm gonna go back to the headers tab and just copy over this formula then jump back and then replace headers and my reference to that list we have the actual formula that we have for that step so now we have a formula that figures out the position of the header that we want to change so which column it is and that's also reflected in the name of the applied steps so here custom one i'm going to rename it to header position so now that we have this let's insert a new step that returns again the promoted headers table so i'm gonna refer here to promoted alice and now we want to change the name for total revenues week 44 to graphics now that gives me the m code that i want to change now here the part that i want to change is total revenue is week 44. so we need over here the list of the headers and then between curly brackets the column number for which we want to change the name so that is three and then instead of hard coding this three we can refer to the step that contains the header position okay so let's type in here other position and you see it still works that would be even better if we take the code first for headers and copy that over to the renaming column step so instead of having year headers i just put in the formula that does the same and therefore i had a position i'm also going to then add a position step copy the forma then go to the last tab and then we place header position with that code and now we don't need these two steps anymore so we can get rid of headers as well as the header position step if response we can do exactly the same so we can just copy over the formula again and then we place total span week 44 with the formula that we created and the only thing that you then need to update is revenues to spend of course so the last solution is definitely the more difficult one however this one is only necessary if the golem changes in position or can change in position so i hope this helps you out in dealing with changing column matters in your data source if you have anything that's related to changing column handlers and that is not solved by this solution then let me know in the comment section below now if you like these tips and tricks then make sure to hit that subscribe button i hope to see you in the next video

Channel: How to Power BI

Views: 11,318

Rating: 5 out of 5

Keywords: power bi column name, column name power bi, column header power bi, renamining column names, column names change breaks query, refresh breaks power bi, power bi refresh breaks, power bi refresh, changing column headers power bi, rename column headers power bi, power bi rename column headers, power bi change column headers, column headers break, power bi column headers, power query refresh, power bi, power bi desktop, fix changing column headers, fix column names

Id: gJPA_Faotkk

Length: 16min 27sec (987 seconds)

Published: Sat Oct 24 2020