Forum Discussion
Dynamic column name from its value
Watch this video in which "Source" is the name of the previous step in my query.
Unfortunately, while typing, it jumped from the second line to the first line, but it is the same "Source".
Hi Marcel,
I am also trying to do this for my report and am trying to follow along your explanation.
Can you clarify on "Dimension" being the previous step of the code? Looking at your first code in your reply, it looks like "Dimension" is the table in which the data is being pulled.
Also, can you elaborate on the "#changed type"? Not sure what how that would be generated automatically.
Thanks for your help!
Watch this video in which "Source" is the name of the previous step in my query.
Unfortunately, while typing, it jumped from the second line to the first line, but it is the same "Source".
When I rename columns it breaks the tables I have made. It says all of the columns in the table are invalid and I have to redrag the newly named columns to get it to work again. Is there a way to get around this?
Dynamically updating column names based on a mapping table could be super useful if only the output worked not just with flat Excel tables, but also with Power BI reports and Excel pivot tables!
Unfortunately, it doesn't seem so. Strangely enough, this limitation/bug reported in 2015 still seems to be the 'state-of-the-art':
"When you rename a column in the Query Editor of Power BI.., it implicitly triggers the column references in your reports to be updated. However, when you rename columns through some dynamic logic, this mechanism isn't triggered and the column references in reports are broken. ... this looks like a bug - you should send a frown..." [2015]
"Once the column name changes the report breaks because it's expecting the previous column name" [2017]
Does anyone know a good workaround to preserve column references in the visuals after applying such a bulk rename step?
Thanks, that video makes much more sense!
Would there be a way to utilize a Switch or IF function for the name based on a slicer? Hypothetically, if I had a slicer based on Seasons of the year, I would want my columns to reflect the names of the month in that season.
My knowledge is specifically with Power Query.
Otherwise I think you need to clarify your question for DAX/Report experts.
Typically a slicer is used to select data from table rows, not from columns.
So it sounds like a strange request to me, but if you think something should be possible, I think you'd better start a new topic.
Hi pager,
Would there be a way to utilize a Switch or IF function for the name based on a slicer? Hypothetically, if I had a slicer based on Seasons of the year, I would want my columns to reflect the names of the month in that season.
As far as I know, it is not possible to do it currently. :smileyhappy:
Regards
hi Pager .
i am exactly looking for this solution. when i select a month from slicer , i want to see my meaure "revenue" with the month name from slicer. something like "revenue June 2018 ".
were you able to get this working? if so please share?
Absolut same issue - would be good to have a solution in here.
In my example we can switch in a slicer between Money and Volumes. So if user is selection Money in column names should be (EUR) and if selected volumes it should be written (HL)....
Hi, MarcelBeug. I feel like I am really close to getting this to work, but I keep getting the following error:
Expression.Error: We expected a RenameOperations value.
Details:
ListHere is my code:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [_PublicationDate]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column22"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type date}}), #"Extracted Year" = Table.TransformColumns(#"Changed Type1",{{"Custom", Date.Year, Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Extracted Year",{{"Custom", "PublicationYear"}}), AddedCustom1 = Table.AddColumn(#"Renamed Columns", "Year1Calc", each [PublicationYear] + 1), #"Renamed Columns1" = Table.RenameColumns(AddedCustom1,{{"Year1Calc", AddedCustom1[Year1Calc]{1}}}) in #"Renamed Columns1"The first 5 steps give my my column value (the publication year +1). the Renamed columns1 step seems like it should work, but then it throws the error.
Any help would be most appreciated! I am reallys stuck and have been hacking away at this for a while.
Were you able to get the correct answer? I have the exact same issue.
Any help would be appreciated.
Hi MarcelBeug,
Thanks for a great solution. Here I have some additional requirements. I need to create a global application where we will have an actual data table. But we will have another table where we will have dynamic field names. e.g.
The data table (Table1) will have.
F1 F2 and F3 fields. These fields will be used in all the charts and Slicers.
And the other table (Table2) will have.
ID F1 F2 F3 ------Columns same as Data table(Table1) name
1 X Y Z ------- Dynamic field names as values for the above line for Dept 1
2 A B C------- Dynamic field names as values for the above line for Dept 2
So, whenever Departement 1 will use my application that time the field names will be X Y Z which will replace the Data Table(Table1) field names i.e. F1 F2 & F3
If Department 2 will use my application that time the field names will be A B C which will replace the same Data Table(Table1) field names i.e F1 F2 & F3.
These changes should be dynamically applied to all the created chart objects.
Need some idea on this issue.
Thanks a Ton in advance.
Regards,
SKD
This is a good solution but after changing the column name with the help of Power Query Formula Language (M), when I apply those chnages to dashboard the visuals were failed load. Infact, under column values it is showing error #This field can't be used since it is invalid.
Please help me with that.
