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

Рет қаралды 78,202

Күн бұрын

A simple Power BI column name change in the data source can break a power query refresh. It can be really frustrating that just by renaming column names, power bi refresh breaks... In this video I show you 3 different ways to fix changing column headers in Power BI.
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:20 Refresh breaks on column name change
1:12 Option 1: Do not promote headers and rename all
2:33 Option 2: Use Table.ColumnNames
9:35 Option 3: When you do not know the column number
15:59 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on KZfaq: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX

Пікірлер: 123

@terihandrick2333 11 күн бұрын

I want to thank you for all the YT videos that you have created for PBI. You have helped me through so many hurdles. Thank you for giving your time and talent.

@rantaleksi7086 2 жыл бұрын

What I love most about your videos is that I basically learned like 5+ new things about M in just 16 minutes, and you explained them so well that I'm pretty sure I could just apply them anywhere after this.

@HowtoPowerBI 2 жыл бұрын

Ranta, thats great! All the best for you!

@bakoubak5489 Жыл бұрын

Thanks for you

@alfreduvnick Жыл бұрын

thank you for the idea, I did upgrade it so you create a column name list, keep it as a table, do all the edit query stuff you need, and at the end rename the columns back according to that table. this work amazingly with survey visualizations where you can change the words in question slightly and still keep all the visuals in the reports.

@SolidSnake59 2 жыл бұрын

Wow. It's brilliant and so simple when you explain it. Thank you!

@Uno707 Жыл бұрын

Definitely coming to your channel for all my PowerBi questions in the near future. I'm creating a dashboard to hopefully take on my companies Supply chain diagnostics, and this set of data was CRUCIAL!

@duongtrinh2487 3 жыл бұрын

Thank you so much. It works to me. This problem made me headache everytime related dept change their data raw's format

@anirudsaichenna3158 2 жыл бұрын

Great video, thank you for explaining it so well, outside this context I have a query, I have ".log" file with comma seperated data and without any column headers which I need to use as dataset to Power BI, and column count may change from time to time. Any suggestoin how to handle this situation. Apprecite your inputs here

@deepakgupta3192 2 жыл бұрын

Thank you so much for this amazing video brother, this has solved one of my project report with client. thank u thank uuu...

@rhys87 3 жыл бұрын

Great video, common problem and good solutions.

@jcoetz2008 2 жыл бұрын

Thank you for the solution! Great video.

@eddiem8248 2 жыл бұрын

Many thanks!! Solution #2 was just was I was needing.

@HowtoPowerBI 2 жыл бұрын

Awesome, happy this helped Eddie!

@jerrymigliaccio5137 2 жыл бұрын

Excellent content, examples and presentation, very useful video. Thank you!

@HowtoPowerBI 2 жыл бұрын

Thanks Jerry! Nice to hear that 😀

@Sisi-vp7xs 7 ай бұрын

Very useful. This is exactly what I'm looking for!

@KB-wb9ym 3 жыл бұрын

Thanks a million for such a great video and your time & effort mate, your teaching skills are just amazing..👌

@punit6490 3 жыл бұрын

Very Much sought for thing by me...thanks for posting...

@GopiNath-fp5ly 2 жыл бұрын

Thank you. Used it effectively.

@gabrielcoppolo8563 3 ай бұрын

Thank you so much! Does it work with fill values anytime a column name changes?

@adityaranjan6226 2 жыл бұрын

Thanks... was in need of this video.

@HowtoPowerBI 2 жыл бұрын

Glad it was helpful Aditya!

@jaymehta3320 3 жыл бұрын

Super amazing! Thanks for sharing this.

@HowtoPowerBI 3 жыл бұрын

Glad you enjoyed it!

@jobinthomas25 Жыл бұрын

Thank you boss! Appreciate this video very much!

@ddp2049 6 ай бұрын

This was great. how about when merging tables how can I make the only one column dynamical

@wesleyharper3253 Жыл бұрын

Thank you!

@davidferrick 2 жыл бұрын

Very nice... I often have DB admins changing column names more often than I feel they should which breaks my reports. An ounce of prevention...

@HowtoPowerBI 2 жыл бұрын

Thank you Dayve!!! 😀 haha I think many people here feel your pain

@anhnguyenduy6861 2 жыл бұрын

This is gold. Thanks man

@HowtoPowerBI 2 жыл бұрын

happy to hear that! thx Tony!😀

@unnikrishnansanthosh 2 жыл бұрын

loads of thanks BAS

@FRANKWHITE1996 Жыл бұрын

Thanks for sharing ❤

@jorstube 3 жыл бұрын

Option 3 ,It´s the Best ...Thank´s Bro

@HowtoPowerBI 3 жыл бұрын

👊🙂 thx

@RobinJerry-qp1ub 8 ай бұрын

Great Teacher. Thank you mate!

@HowtoPowerBI 8 ай бұрын

Glad you liked it! thx for watching! 😊

@hachanguhakalima1682 2 ай бұрын

This is awesome!!! Thanks you.

@HowtoPowerBI 2 ай бұрын

😎👊

@Blamson64 Жыл бұрын

What if I want my visualizations to reflect with what the new column header is? For example if I would want to know that it is week 44 inside the visualization itself? Do you need to re-create the visualization every time that header is updated?

@CANONinDEVIN 7 ай бұрын

OK now I get it, option 1 is brilliant for my use!

@alejandra1888 9 күн бұрын

Thank you! And what about if you have a project with its tables (transformed in Power Query), its visualizations and its metrics and you need to change the name of the columns, for example, from Spanish to English? What about all the steps of the transformations? and all the metrics? You need to change all the names manually? I have a serious problem...

@decentmendreams 3 жыл бұрын

Thank you so much. The topics are very relevant and your explanations are surgically precise .You do an amazing job of editing your videos. . The only input I have is try to keep the split screen all the time .

@vimukthiamarasena6264 2 жыл бұрын

is there a way to replace table and column names in DAX at once ( Ex: sum(table1[column1]) -> sum (NewTable [NewColumn])

@officesuperhero9611 3 жыл бұрын

Excellent!

@matthewbrierley88 3 жыл бұрын

THANK YOU SO MUCH!!! You are a true hero. This is brilliant.

@HowtoPowerBI 3 жыл бұрын

always wanted to be a hero 😁 haha thanks Matthew

@ratnoinamaew7822 Жыл бұрын

What about changing number of columns. Every time I refresh my data from the GL, the source data changes, so it may be 10 cols, then next refresh there are only 9 - Can PQ handle this and just import whatever number of columns each time just by refreshing the data?

@Oluwa_Sarah 2 жыл бұрын

Hello! Will this work if it is Online Sharepoint List? Doesn’t seem to be working for me.

@SpiritualMotto 3 жыл бұрын

I need help on changing column name on daily to filter, when i first did my filter on my source column name is [xxx 25/10/2020] and filtered with "5" next day my column name is changed to [xxx 26/10/2020] when i refresh, it shows error like column is not found. Note: i dont want it to rename, i need source header as it is in output as well. I tried using table.columnnames(source){1} to filter based on column index instead of static column name as [xxx 25/10/2020] however, after refresh, it shows table is empty. There is no error but data is not getting populated. I need support to fix this.

@tanmeesun3458 Жыл бұрын

What if the column names shows randomly with some shows capitalized and some are not, and some with extra spacing in between wording or spacing at the beginning or either at the end of the wording?

@kebincui 2 жыл бұрын

You are really a magician👍👍; thanks for sharing

@HowtoPowerBI 2 жыл бұрын

:):)

@BoudewijnBuil 8 ай бұрын

Thanks for your this training! My question is can you apply this also for a map (as source) with multiple csv files?

@HowtoPowerBI 8 ай бұрын

map as source? I guess you mean you have multiple csv files with data that you want to visualize in a map. Yes this would work

@BoudewijnBuil 8 ай бұрын

Besides that you make excellent video's, your response is also excellent! But my question is as follow; I have a map with multiple csv files. One csv file contains multiple columns, Column nr. 281, 282 and 283 changed from column name.... I see no promoted headers in the query? Can I find somewhere a video, how to solve this? I appreciate your efforts! @@HowtoPowerBI

@HowtoPowerBI 8 ай бұрын

aaaah you mean folder ;) .. maybe this video helps kzfaq.info/get/bejne/gKenZ9Wj1cmbpp8.htmlsi=HzineXQiBsrKu5_a

@razainthetube 2 жыл бұрын

When I encounter any column name change error, I simply go to the transform data, go back to the source step, refresh the data in PBI and click on the next steps (right Before change type). Delete change type and it works like a charm.. Am I missing anything here?

@lawrenceliew5812 3 жыл бұрын

clear and precisely explained. Well thought video. Thanks!

@HowtoPowerBI 3 жыл бұрын

thank you Lawrence!! 😀

@amfunnydog Жыл бұрын

I used the last method, it seems like the text.Contains function is case sensitive and does not allow space ?

@brunomason5128 2 жыл бұрын

I have an issue creating a dynamic unpivoted column of a select few of columns. First unpivot the column. Second I add the attribute column as a new Query. Third In the new Query area I create a conditional column that pulls select words from the attribute column. Fourth I create a index. Fifth I merge the Created new Query to the table. Finally I delete the original column bringing in the index and the two columns from the new query. I created the dynamic headers so new information is coming in if the headers change. But if I add new headers I want them to rearrange into the unpivoted column automatically. Thus going through the applied steps.

@dianamgdata 3 жыл бұрын

Tome su buen like. ¡Me ayudó muy bien pa segunda solución!

@HowtoPowerBI 3 жыл бұрын

gracias Diana!😀

@xrosnero Жыл бұрын

how about if the source is already a table (promote headers not needed in step)

@gambu4810 2 жыл бұрын

Bruhh create a patron, we have to start paying you for these videos. By the way I'm happy to give you my daughter, she's a pretty lady.

@imranisshack9828 2 жыл бұрын

This saved my life. I was going crazy when I refreshed my PowerBI and it was breaking. Thanks very much :)

@HowtoPowerBI 2 жыл бұрын

You are very welcome :) I am happy to hear it helped you solve it

@GordonShamway1984 11 ай бұрын

where have you bought such a nice watch?

@revsram Жыл бұрын

Hello, If you can help me how to handle the problem with adding new column

@vijayvizzu1 3 жыл бұрын

Nice, but what if i want to update the part of the header and want to update with the existing header. I meant to say that, Jan Forecast becomes Jan Actual when the month passed, we will change the forecast into actual, but the month name remains same

@ibrahimhaizel6856 Жыл бұрын

You should probably do this with two measures that filter your Jan Forecast column based on the date column of the record.

@Mtodea27 Жыл бұрын

What if I would like to add new columns in my Excel database? And how do I manage to import new data from my Excel database in Power Bi without having to create a new dashboard every week? Is it just by pressing the "refresh" button in Power Bi when the Excel database is updated?? Do you already have a video made for this type of problems? Thanks :)

@ExcelInstructor 2 жыл бұрын

Hello, Maybe u had similiar issue with PQ in excel, for unknown reason each time i use try... otherwise statement it works in PQ sometimes, the other times its either endless loading or a simple excel crash. any idea whats going on? Re-install did not solve the issue.

@abrajeedhan Жыл бұрын

I have new data coming in every month and I need to unpivot those columns which have new names with every data update. I cant keep a common name. I need the new column name unpivoted into rows. How do I solve this issue?

@erickknackstedt3131 3 жыл бұрын

This is amazing. Thank you!

@SpiritualMotto 3 жыл бұрын

I have a requirements to have same name from my source where it actually will change on monthly basis. Ex - October forecast data changes in next to October Actual and i had filter on this like equals to 1000. Its not working when it changes to actuals from forecast. Pls advise

@ibrahimhaizel6856 Жыл бұрын

This is a bad data design, you shouldn’t store data for more than one variable in each column. In your case the date and type dimensions. Just use 3 columns instead: The sales data in one column, the date in another and the type (actual/forecast) as a categorical column in the last. You could even make the categorical column dynamic by creating a measure instead which calculates based on the current date and the date of the record.

@bennylbkooi 8 ай бұрын

Hi there. Pardon my ignorance if you already have video for what I'm about to ask. My data source (CSV file) keeps having different columns, week to week. Columns of previous weeks retain, while having new columns of latest week. For illustration, as below: W4: W1 W2 W3 columns + W4 column W5: W1 W2 W3 W4 columns + W5 column W6: W1 W2 W3 W4 W5 columns + W6 column How should I handle it? I plan to use Table visual to display. Hope to hear from you and/or other followers. Thanks in advance.

@rewur615 Жыл бұрын

Danke!

@HowtoPowerBI Жыл бұрын

Wow thanks a lot for the support!

@seemachavan3790 Жыл бұрын

I have data start with 1st column 31-8-2022 , 2nd column 1-9-2022, till 30-9-2022...i hv renamed... But in visual how I will show date wise data

@ryanm803 Жыл бұрын

First of all, love the content! But I would like to point out that step 3 breaks the refresh... Text.Contains() is hard coded to search for "Revenues", but what if the column name is updated to say to "Profits" in the source? It breaks and can no longer find the position of the changed column.

@alicecommits Жыл бұрын

instead of hard coding one very specific column name string to look for, such as Revenues or Profit, maybe a condition or a pattern (like a regex) could be used. E.g. something that would search for "Revenue(s|'') OR Profit(s|''), case insensitive, with or without trailing characters, etc..."

@pravinjoshi72 3 жыл бұрын

I have a autogenerated user function which has column named CustCode. This function is called by a caller. Later when i change the CustCode to CustomerCode, the caller fails as field name in function is different than caller. I couldn't find the way to fix this issue. Please guide.

@pravinjoshi72 3 жыл бұрын

What seems to be working is...create a new function from sample code that is auto generated by power NI...and then caller will call this new function. However, still i haven't got the remedy to refresh the metadata when an existing function changes...and the caller, even after modifying the code, doesn't work with changed function definition. Please let me know how to do it.

@saraakeel6354 Жыл бұрын

prefect

@wesleyharper3253 Жыл бұрын

Still love this video

@HowtoPowerBI Жыл бұрын

So old… 🙈

@alizashaikh2109 Жыл бұрын

What if the column name changes every time and new column name is not standard as mentioned here as Revenues...Suppose the column name is 0+12 and next time we get it as 2+10. How to maintain this

@totvabe1 3 жыл бұрын

Incredible what we (better said 'you') can do with Power Query. Awesome! Thanks for the video. I'd appreciate if you allow to see the whole code at the end, in order to keep an screenshot just in case.

@HowtoPowerBI 3 жыл бұрын

thank you!! 😃 will do, there is also a download link in the description below the video with the file and code.

@skipa9906 3 жыл бұрын

Hi. How would I handle the error if I am adding a new column in the XLSX file i am sourcing from?

@HowtoPowerBI 3 жыл бұрын

can you describe the error that you get?

@skipa9906 3 жыл бұрын

@@HowtoPowerBI So when i add a new column to the data on my excel sheet and then refresh the model, I get the error "The column 'Column29' of the table wasn't found."

@carolshipley7903 Жыл бұрын

I have a good challenge for you. I transpose my data and then promote headers, but the issues is this. The number of rows that transpose (to become headers) can grow or shrink and the header will not be the same every time.

@Velisus Жыл бұрын

What if I have a column "Notes/Updates" and I want to change the column name to "Notes" In PowerBI, the dataset is still tracking "Notes/Updates" and returns an error How do I make the update to the Power BI dataset* and not to my source column? *Using the web version of Power BI (I don't have access to the desktop version).

@jelledaems808 3 жыл бұрын

Thank for this information, although not quite what I needed yet. In your example you still hard-code the column to Revenues. What if the target column name should be flexible? I have 1 dataset and the connection is a parameter. I publish the dataset to multiple workspace (one for each client). And each client has their own database connection. In a table I have values but the first column are the titles. When I load it in my development environment, it might say "department", so when I promote header it will say department just like I want it. But when I publish to the service and refresh based on a client database it will say that it cannot find "department" as in that client database the first column name is "service" and there is no "department". So I would like the column name to be "service" instead of "department"

@filipwinski4219 3 жыл бұрын

Great Solutions! I've just implemented them in my project. Thanks for the video.

@HowtoPowerBI 3 жыл бұрын

Glad it helped 😃

@jasezayden6383 3 жыл бұрын

A tip : you can watch movies at Flixzone. Been using them for watching all kinds of movies lately.

@judesamuel1357 3 жыл бұрын

@Jase Zayden Definitely, have been using Flixzone} for years myself :D

@eduardopayton7776 3 жыл бұрын

@Jase Zayden Yea, been using flixzone} for months myself :)

@ronanzaid2154 3 жыл бұрын

@Jase Zayden Yea, have been using Flixzone} for since december myself :D

@Graylinepartners 2 жыл бұрын

Great video. Very clear. Not a lot of fluff. And very useful. There seems to be a need for m language examples and good training on the youtube.

@GibranElVelas 3 жыл бұрын

Oh my god u saved my life! Thanks for the info men u rock

@Setiawan- 2 жыл бұрын

There are a simple way. ada cara yang lebih simple. saya sudah melakukannya. i have done with it. 1. anda mengubah nama kolom di excel 2. anda ubah nama kolom di tab Data 3. menuju ke menu tranform anda klik tool tip go to error. lalu ubah nama kolum yang diganti tadi 4. Refresh. selesai.

@AdnanAhmad7 3 жыл бұрын

In my case, I changed the source data column header on purpose and it ran into an error. Following your 1st method, I demoted headers to change the default column names on top but it somehow picked up the correct column names in the first row. I promoted the first row to headers again and it was all fine

@vartikathakur4273 2 жыл бұрын

How to proceed without Promoted Headers?

@DJPejsen 3 жыл бұрын

A great solution for renaming the colunnames Do you also have a solution if some of the headers have been spelled incorrect , so all values in List.Transform will be FALSE?

@peteadrian7743 2 жыл бұрын

Jumping to the Query Editor without describing how to get there left me hanging! That's too important a step to leave out IMO.

@HowtoPowerBI 2 жыл бұрын

Most videos on the channel assume already an understanding of the fundamentals. This video might help you to get an overview before diving into the focus topics kzfaq.info/get/bejne/rZp-e66Qz8_ehas.html

@henrytam217 2 жыл бұрын

List.Select(Headers,each Text.Contains(_,"Revenues")){0}

@Lottumracing Жыл бұрын

Awesome, especially the last one. Is it case sensitive or not? And is it possible to create a custom function of this to replace the header name, that can be reused?

@benrogers9092 2 жыл бұрын

Another solution: = Table.RenameColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.BeforeDelimiter(_, " Week ")}))

@mohdmuzammil3059 3 жыл бұрын

1st 👍

@HowtoPowerBI 3 жыл бұрын

Thanks :D

@mukeshved Жыл бұрын

Dear, i have "Appended" file. After uploading current week's data into master excel file and clicking on refresh on BI report, it is giving me "the key didn't match any rows in the table error" and new data is not getting updated in the report. Kindly support and advise on this issue please.

@transparent91 Жыл бұрын

This video sucks. There is an infinitely more simple and easy way of doing this and it takes like 10 seconds. 1. Open your table in power query 2. Expand the code field for the table 3. Replace the column name between "name" to "desiredName" in the code field. Error will show up, that's okay 4. Press Save & Load 5. Go to the actual table, replace the name to match your desired name in the column. 6. Refresh all 7. Done 8. If you have combined several tables into one combined table, you need to do the same for that one also.

@HowtoPowerBI Жыл бұрын

😂uh .. no