Brilliant. Thank you. I like how you carefully explain what is going on in the code as you proceed so I don't just know how to do it, I understand what I'm doing! 🙂
Actualy you could fix the last problem by simple code. table.columnnames, from that grab the last row (remember PQ is 0 based) so use [new col]{2} to dynamicly set that column.
Chandeep, Suppose we had two tables from 2 sources . O The headers in both tables are named differently. So we have 6 names. But Data has to be appended in 3 columns. So first column has 6 distinct names & 2nd column has 3, with duplicates. If we append tables before dynamically changing headers, we get a "name already exits" error. Hence, change headers first & then append the tables. Just letting you know of using this technique when there are more than one table.
Thank you so much! and also because you explained that maybe something can go wrong when you create another step, it's so important, at least for me because I'm new in this learning
Awesome. The steps are explained very well. It works flawlessly in Powerbi but if we create a visual and then if we change the titles, the visual throws error in PowerBI. Any workaround will do good.
great work. I followed this step. It worl fine for few queries but now I am getting below errors Expression.Error: We expected a RenameOperations value. Details: [List]
Hi Chandeep, once again a terrific video! Thank you very much for this! Question: Can you also apply this to the types and maybe combine it? One column gives the current formatting and in the second column you write your desired formatting (e.g. Actual=Number, Target=Date) and PQ changes that afterwards? Best regards, mangix
Absolutely incredible explanation! Showing what basic way to do it, actual solution, and what to be cautious about when implementing the is the golden trifecta! Awesome.. Thanks! One question about it though - I implemented this solution in Power BI Power Query to generate dynamic column names in M Query. But as soon as the new column name is generated based on dynamic renaming, it breaks all subsequent visualizations. Is there a way you have noticed to avoid this problem?
@@GoodlyChandeep Understood, as long as the underlying schema object remains same, the subsequent references within model would remain intact. Thanks for the quick response, greatly appreciate it! 👍👍
Awesome, great video, thanks very much for this! hey could this be use in some way to replace multiple values from diferent columns to all of them? like if you want to translate the entire table with the google translate API. By making the headers a list first you can actually make it work to be multiple columns where to to apply the change, but for the columns from where you want the source value/text it can only be hardcoded and only one column, is there any way to make it work? has being quite a challenge now.
Hi Sir, Thanks for interesting video. Can You please make some video to make us understand list. List is still what is completely unclear in power query. Thanks.
Nice Explanation. But I am looking for the same functionality in the measures. So I am using multiple measures in a matrix and i want the header name for measures to change basis slicers. Is there any way?
The measures cannot be renamed based on slicers, nor can the headers of the table. But you can play smart and use some DAX measures to create dynamic name values based on slicers and stick them over your headers table. This will give you some idea - www.goodly.co.in/change-pivot-table-field-calculations-with-a-slicer/
Hi Chandeep, Awesome solution this was. I have a something similar ask. How can I change a pivot table dynamically. so user can select wether he wants to show the total sales by state, or by region, or by country.
Hello, is it possible to dynamically change column name based on a selected filter by user. For example: i will have two filters 1. en 2. fr So, column names should come in English and French based on user selection
Nice video, i have data with monthly values, one of the column name is going to change every month, like Apr Forecast becomes Apr Actuals. currently i am manually fixing the column name every month, How can i handle this scenario, please suggest
Hi - looks promising - will this technique work pulling data from tables (formatted the same) from multiple files from a folder? eg If I keep source Table headers the same (measure1, measure 2 etc) and then rename them on consolidation.
I really need some help with a dynamic query to replace values in multiple columns, based on a condition. I have seen solutions for the two, separately but not together.
Hello, I have a query similar to this. I have a table with different month columns of the previous year and the current year. So in my current table previous_01 is Jan 2020 and Current_01 is Jan 2021 and so on.What my customer wants that column should be dynamic and not static. So if we refresh data next year, the column name Previous_01 should change into Jan 2021 and not Jan 2020. Similarly, for column name Current_01 should change into Jan 2022 and not Jan 2021. Any idea how to achieve this?
Hi Cnadeep, I am able to change column name based on list value but it not working in dashboard, When i refreshed dashboard after changing in source file, it showing me error in visual ," corresponding field is invailid. Could you give me solution please.
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.
Can u just let me know how to change the resultant calculated (subtracted result of two columns) column names as dynamic based on the ID (for instance)all at once?
its good solution but won't work on table when you get data throug excel workbook or through folder .It prompt list ."Error: We expected a RenameOperations value" Please help
Hi Sir, thanks for the video. I'm getting this error: Expression.Error: We expected a RenameOperations value. Detalles: [List] Have you any idea to solve it?
Hi Sir, I solved it. The problem was that all my old column names were numbers, so the PQ read it like numbers. It is important to force the type of the old names and new names columns to "text", it solved it.
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
Brilliant. Thank you. I like how you carefully explain what is going on in the code as you proceed so I don't just know how to do it, I understand what I'm doing! 🙂
Thank you for sharing this. Dynamic columns is such a relief, especially for the data that keeps expanding horizontally or last x months/quarters etc.
Neat Trick. Good exercise.
I think I can use Table.ToRows instead of Table.Transpose and Table.ToColumns.
Thank you.
Dude. You cleared up several things for me after many years with PQ/BI. Many thanks.
Actualy you could fix the last problem by simple code. table.columnnames, from that grab the last row (remember PQ is 0 based) so use [new col]{2} to dynamicly set that column.
Thank you so much! After going through several solutions without success, your video really made it clear and it worked for my case!
Thank you for sharing this tip. Exactly what I needed!
Thank you for making this tutorial so clear.
Nice explanation. This sort of dynamic renaming is necessary to make queries robust.
Thank you so much! The best way to change columns name
Excellent content, will definitely be checking out your course!
Great video, mate - saved me a lot of time and adds a great level of functionality for the process improvement I'm working on.
Chandeep,
Suppose we had two tables from 2 sources . O
The headers in both tables are named differently. So we have 6 names.
But Data has to be appended in 3 columns.
So first column has 6 distinct names & 2nd column has 3, with duplicates.
If we append tables before dynamically changing headers, we get a "name already exits" error.
Hence, change headers first & then append the tables.
Just letting you know of using this technique when there are more than one table.
Crazy solution! Kudos to you Chandeep.
Thank you!
Thank you so much! and also because you explained that maybe something can go wrong when you create another step, it's so important, at least for me because I'm new in this learning
I can’t thank you enough! Great job and explained wonderfully!
Glad it was helpful! :)
Awesome. Looks like the trend is to learn power query and the m language for better results in data modeling. Thanks
Thanks a ton for this video, it worked for me. You are awesome Chandeep.
Glad it was helpful!
Clear and concise. Thanks for the video!
Glad it was helpful!
Absolutely helpful, thanks a lot 👌👌
Absolutely awesome solution. Saved me a lot of time. Thanks a lot for sharing!
This is pretty cool, works great. I had issues when trying to use it with a connection that references it's own output though.
Wonderful work!
Thank you very much for this help...
Nice , Thanks..
Thank you, this is the perfect solution I was looking for.
Glad it helped
Another amazing video ❤
Awesome. Thanks for the video. I have a question i tried picking column name based on parameter which does not works. any inputs would be appreciated
Awesome. The steps are explained very well.
It works flawlessly in Powerbi but if we create a visual and then if we change the titles, the visual throws error in PowerBI. Any workaround will do good.
Amazing
great video,but when I unpivot I dont get column names dynamically how to fix this?
Thanks for sharing ❤
Glad you like it !
Very helpful Thx.
great work. I followed this step. It worl fine for few queries but now I am getting below errors
Expression.Error: We expected a RenameOperations value.
Details:
[List]
Thank you so much
💚
Awsome!
Thanks!
Thanks 👍🏽
Great Video!
Glad you enjoyed it
No need to transpose the table because Table.ToRows will do the job. Give it a shot
Hi Chandeep, once again a terrific video! Thank you very much for this! Question: Can you also apply this to the types and maybe combine it? One column gives the current formatting and in the second column you write your desired formatting (e.g. Actual=Number, Target=Date) and PQ changes that afterwards?
Best regards,
mangix
Absolutely incredible explanation! Showing what basic way to do it, actual solution, and what to be cautious about when implementing the is the golden trifecta! Awesome.. Thanks!
One question about it though - I implemented this solution in Power BI Power Query to generate dynamic column names in M Query. But as soon as the new column name is generated based on dynamic renaming, it breaks all subsequent visualizations. Is there a way you have noticed to avoid this problem?
You rename the columns in such a way that your final column names don't change, else the model will break.
@@GoodlyChandeep Understood, as long as the underlying schema object remains same, the subsequent references within model would remain intact. Thanks for the quick response, greatly appreciate it! 👍👍
@@cooldudesheks Correct!
Awesome, great video, thanks very much for this! hey could this be use in some way to replace multiple values from diferent columns to all of them? like if you want to translate the entire table with the google translate API. By making the headers a list first you can actually make it work to be multiple columns where to to apply the change, but for the columns from where you want the source value/text it can only be hardcoded and only one column, is there any way to make it work? has being quite a challenge now.
Hey Great tips but I am getting error We expected a RenameOperations value. Not sure whats the issue :(
Hi Sir, Thanks for interesting video. Can You please make some video to make us understand list. List is still what is completely unclear in power query. Thanks.
In PQ when you right click on a column and choose drill down option - you get a list. try it
Great
Nice Explanation. But I am looking for the same functionality in the measures. So I am using multiple measures in a matrix and i want the header name for measures to change basis slicers. Is there any way?
The measures cannot be renamed based on slicers, nor can the headers of the table.
But you can play smart and use some DAX measures to create dynamic name values based on slicers and stick them over your headers table.
This will give you some idea - www.goodly.co.in/change-pivot-table-field-calculations-with-a-slicer/
Hi Chandeep, Awesome solution this was. I have a something similar ask. How can I change a pivot table dynamically. so user can select wether he wants to show the total sales by state, or by region, or by country.
See this - czcams.com/video/c0mPvzkf6i0/video.html&lc=UgwBE2rRVLGswcE5gxd4AaABAg
Hello, is it possible to dynamically change column name based on a selected filter by user. For example: i will have two filters 1. en 2. fr
So, column names should come in English and French based on user selection
And one more question, how can I make the column "Existing Col" also dynamic, that there always the table headers of the source table are shown?
Nice video, i have data with monthly values, one of the column name is going to change every month, like Apr Forecast becomes Apr Actuals. currently i am manually fixing the column name every month, How can i handle this scenario, please suggest
@Goodly sir why those which were already changed columns are not changing afterwards dynamically ?
Hi - looks promising - will this technique work pulling data from tables (formatted the same) from multiple files from a folder? eg If I keep source Table headers the same (measure1, measure 2 etc) and then rename them on consolidation.
Hi Chris, I haven't understood your question here. Can you elaborate?
I really need some help with a dynamic query to replace values in multiple columns, based on a condition. I have seen solutions for the two, separately but not together.
Hello, I have a query similar to this.
I have a table with different month columns of the previous year and the current year. So in my current table previous_01 is Jan 2020 and Current_01 is Jan 2021 and so on.What my customer wants that column should be dynamic and not static. So if we refresh data next year, the column name Previous_01 should change into Jan 2021 and not Jan 2020. Similarly, for column name Current_01 should change into Jan 2022 and not Jan 2021. Any idea how to achieve this?
Hi Cnadeep, I am able to change column name based on list value but it not working in dashboard, When i refreshed dashboard after changing in source file, it showing me error in visual ," corresponding field is invailid. Could you give me solution please.
I want to replace value in Column, Can I use this tricks?
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.
What If I want to add a new column which got added to my data table?
Can u just let me know how to change the resultant calculated (subtracted result of two columns) column names as dynamic based on the ID (for instance)all at once?
its good solution but won't work on table when you get data throug excel workbook or through folder .It prompt list ."Error: We expected a RenameOperations value" Please help
Why not do it from m code? Shouldn't that be quicker?
Sir only third column name is changing , other two are not changing . Why sir ??
I don't know but due to some reason in last step (Table.RenameColumns) I am getting error:
We expected a RenameOperations value.
details: List
Hey man, check to see if you have any null values in the columns. I was having the same issue and filtered out nulls and it works
You may have to change the type of the old names and new names columns to "text" before transposing it.
Hey! Have you found any solution? :) I tried both below but none of them works.
This is not working for power BI visuals
Suppose the column name changes in a Table card or matrix card , the whole dashboard falls
Yes I'm also facing same issue.. Did u get any solution for this?
Hey!
Expression.Error: We expected a RenameOperations value.
Details:
[List]
I've been getting this error..... can anyone help with this?
Hey! Have you found any solution? :)
Hey Michael!
As far as I remember I completed this task in some way😅
Will have to check it though!
@@tallalaazmi1287 thanks, looking forward hearing from you!
Hi Sir, thanks for the video. I'm getting this error: Expression.Error: We expected a RenameOperations value.
Detalles:
[List]
Have you any idea to solve it?
Hi Sir, I solved it. The problem was that all my old column names were numbers, so the PQ read it like numbers. It is important to force the type of the old names and new names columns to "text", it solved it.