Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 wan...
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

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

     

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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.

 

pager's avatar
pager
Frequent Visitor
9 years ago

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's avatar
    MarcelBeug
    Community Champion
    9 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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's avatar
      pager
      Frequent Visitor
      9 years ago

      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.

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

       

       

      • yash14vashistha's avatar
        yash14vashistha
        Helper III
        6 years ago

        Were you able to get the correct answer? I have the exact same issue.

        Any help would be appreciated.