Refresh breaks due to COLUMN NAME CHANGES? 3 ways to fix changing column headers in Power BI!
HTML-код
- Опубликовано: 7 авг 2024
- 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 RUclips: 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 
Наука
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.
Ranta, thats great! All the best for you!
Thanks for you
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.
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.
Wow. It's brilliant and so simple when you explain it. Thank you!
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!
Thank you so much. It works to me. This problem made me headache everytime related dept change their data raw's format
Thank you so much for this amazing video brother, this has solved one of my project report with client. thank u thank uuu...
Great video, common problem and good solutions.
Thank you for the solution! Great video.
Very useful. This is exactly what I'm looking for!
Many thanks!! Solution #2 was just was I was needing.
Awesome, happy this helped Eddie!
Very Much sought for thing by me...thanks for posting...
Thank you. Used it effectively.
Excellent content, examples and presentation, very useful video. Thank you!
Thanks Jerry! Nice to hear that 😀
Thanks a million for such a great video and your time & effort mate, your teaching skills are just amazing..👌
Thank you boss! Appreciate this video very much!
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
Super amazing! Thanks for sharing this.
Glad you enjoyed it!
Thanks... was in need of this video.
Glad it was helpful Aditya!
Excellent!
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...
Thank you Dayve!!! 😀 haha I think many people here feel your pain
OK now I get it, option 1 is brilliant for my use!
loads of thanks BAS
Option 3 ,It´s the Best ...Thank´s Bro
👊🙂 thx
Thank you!
Thanks for sharing ❤
This is gold. Thanks man
happy to hear that! thx Tony!😀
Thank you so much! Does it work with fill values anytime a column name changes?
Great Teacher. Thank you mate!
Glad you liked it! thx for watching! 😊
THANK YOU SO MUCH!!! You are a true hero. This is brilliant.
always wanted to be a hero 😁 haha thanks Matthew
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 .
This was great. how about when merging tables how can I make the only one column dynamical
This saved my life. I was going crazy when I refreshed my PowerBI and it was breaking. Thanks very much :)
You are very welcome :) I am happy to hear it helped you solve it
This is awesome!!! Thanks you.
😎👊
You are really a magician👍👍; thanks for sharing
:):)
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...
Tome su buen like. ¡Me ayudó muy bien pa segunda solución!
gracias Diana!😀
clear and precisely explained. Well thought video. Thanks!
thank you Lawrence!! 😀
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?
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.
This is amazing. Thank you!
is there a way to replace table and column names in DAX at once ( Ex: sum(table1[column1]) -> sum (NewTable [NewColumn])
Danke!
Wow thanks a lot for the support!
prefect
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.
thank you!! 😃 will do, there is also a download link in the description below the video with the file and code.
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.
Hello! Will this work if it is Online Sharepoint List? Doesn’t seem to be working for me.
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?
where have you bought such a nice watch?
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?
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.
I used the last method, it seems like the text.Contains function is case sensitive and does not allow space ?
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 :)
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.
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..."
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?
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.
Hello, If you can help me how to handle the problem with adding new column
how about if the source is already a table (promote headers not needed in step)
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.
Thanks for your this training! My question is can you apply this also for a map (as source) with multiple csv files?
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
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
aaaah you mean folder ;) .. maybe this video helps ruclips.net/video/JAw4rrpc7vk/видео.htmlsi=HzineXQiBsrKu5_a
Still love this video
So old… 🙈
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?
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.
Great Solutions! I've just implemented them in my project. Thanks for the video.
Glad it helped 😃
A tip : you can watch movies at Flixzone. Been using them for watching all kinds of movies lately.
@Jase Zayden Definitely, have been using Flixzone} for years myself :D
@Jase Zayden Yea, been using flixzone} for months myself :)
@Jase Zayden Yea, have been using Flixzone} for since december myself :D
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
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.
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.
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
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
You should probably do this with two measures that filter your Jan Forecast column based on the date column of the record.
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
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.
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.
Oh my god u saved my life! Thanks for the info men u rock
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).
Hi. How would I handle the error if I am adding a new column in the XLSX file i am sourcing from?
can you describe the error that you get?
@@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."
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"
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
How to proceed without Promoted Headers?
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?
Jumping to the Query Editor without describing how to get there left me hanging! That's too important a step to leave out IMO.
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 ruclips.net/video/w4NHK_jizUw/видео.html
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.
List.Select(Headers,each Text.Contains(_,"Revenues")){0}
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?
Another solution:
= Table.RenameColumns(Source, List.Transform(Table.ColumnNames(Source), each {_,
Text.BeforeDelimiter(_, " Week ")}))
1st 👍
Thanks :D
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.
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.
😂uh .. no