cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
finellj
Regular Visitor

Dynamic column name from its value

Hi

 

Is there any way to dynamically change column name from its values. I think it might be possible using advanced editor, but i'm not very good at writing M.

 

I have Dimension table and i want Dimension_name column to have its name dynamically from its values whitch is same in every row in this case.

 

I need this that i can use same template in every customer case and i dont have to change Dimension names every time i change data source.

 

There might be easier solutions and i welcome every solution you can give me.

 

thanks

 

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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".

 

Specializing in Power Query Formula Language (M)

View solution in original post

13 REPLIES 13
MarcelBeug
Community Champion
Community Champion

The easiest way is to create some base code in M by double clicking the column header and change the name in just something.

 

Then adjust the generated code, like:

 

= Table.RenameColumns(Dimension,{{"Dimension_Name", Dimension[Dimension_Name]{0}}})

 

In this code "Dimension"  is the name of your previous step in the code. If that is something else then you must use that step, e.g. if it would have been #"Changed Type", then:

= Table.RenameColumns(#"Changed Type",{{"Dimension_Name", #"Changed Type"[Dimension_Name]{0}}})

The first #"Changed Type"  is generated automatically, so you need to use this name in the adjusted formula.

 

Specializing in Power Query Formula Language (M)
pager
Frequent Visitor

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!

MarcelBeug
Community Champion
Community Champion

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".

 

Specializing in Power Query Formula Language (M)

View solution in original post

Anonymous
Not applicable

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

Anonymous
Not applicable

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:
         List

 Here 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.

Anonymous
Not applicable

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?

Anonymous
Not applicable

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?

pager
Frequent Visitor

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.

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 @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. Smiley Happy

 

Regards

MarcelBeug
Community Champion
Community Champion

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.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors