locked
Dynamically rename column header RRS feed

  • Question

  • Hallo everyone,

    I am fighting with the following problem that sounds quite simple (and probably is quite simple if I knew the solution):

    I am exporting a simple table into Power Query that consists of two columns, Attribute Name and Attribute Value, e.g.

    Attribute Name      Attribute Value

    ------------------------------------

    Age                      25

    Age                      40

    Age                      22

    I can and need to make sure that the attribute name is the same on all rows. What I simply want to do reshape that table to show only one column whereas that column should "dynamically" get it's name from the first (or any row) in column Attribute Name, thus my table should finally simply look like:

    Age 

    ----

    25

    40

    22

    I know that pivoting the table does the trick but it has (at least in the Power BI Designer) the side-effect that reports that reference this "dynamic" column will break with the error "One ore more fields in this visualization have disappeared or are broken". If I manually rename columns in the query editor, the visual designer is smart enough just to replace the name.

    Is there any way to programmatically simulate the manual renaming?

    Thanks a lot in advance!

    Julian

     


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Wednesday, June 24, 2015 9:30 PM

Answers

  • Hi Julian,

    this looks like a bug - you should send a frown.

    Try this alternative:

    let
        Source = ...,
        NewName= if List.Count(List.Distinct( Source[AttributeName])) = 1 then List.First(Source[AttributeValue]) else "Wrong data in Attrib Name",
        Rename = Table.RenameColumns(Source,{{"AttributeValue", NewName}}),
        Remove = Table.RemoveColumns(Rename,{"AttributeName"})
    in
        Remove


    Imke

    • Proposed as answer by Michael Amadi Saturday, June 27, 2015 10:21 AM
    • Marked as answer by Julian Wissel Saturday, June 27, 2015 7:09 PM
    Saturday, June 27, 2015 6:47 AM
  • Hi Julian,

    When you rename a column in the Query view of the Power BI Designer Preview, 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. As pointed out by Imke, you can feed this issue back to the Power BI Team using the frown.

    In the meantime you may consider removing the invalid attribute name rows in one of the steps instead of renaming the column altogether. I have adapted the M code provided by Bill and Imke to reflect this approach:

    let
        Source = Table1,
        DemHeaders = Table.DemoteHeaders(Source),
        RemFirstRow = Table.Skip(DemHeaders,1),
        RemInvalidRows = Table.SelectRows(RemFirstRow, each ([Column1] = List.First(RemFirstRow[Column1]))),
        Finish = Table.FromColumns({RemInvalidRows[Column2]},{List.First(RemInvalidRows[Column1])}) 
    in
        Finish

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, June 27, 2015 10:21 AM

All replies

  • You can count number of values in the attribute name column (with functions such as Table.RowCount), and compare the result with count of all records in the table, if they are the same then you have similar value in all the records for attribute name.

    then you can fetch the very first value with functions like Table.FirstN , and use that value as the name of new column.

    you can select new column with the function Table.SelectColumns or Table.RenameColumns where you can even specify the new column name.


    My blog

    Wednesday, June 24, 2015 9:56 PM
  • Hi Julian,

    Try this code below if you want.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Finish = if List.Count(List.Distinct( Source[Attrib Name])) = 1 then Table.FromColumns({Source[Attrib Value]},{List.First(Source[Attrib Name])}) else "Wrong data in Attrib Name"
    in
        Finish

    Customize source for your needs.

    Regards

    Wednesday, June 24, 2015 10:28 PM
  • Hi Bill, hi Reza,

    Thank you very much for your answers! I managed to get the dynamic rename working as suggested by you however it still breaks any existing report that uses that "dynamic field". I do not know if that could work at all, originally I was optimistic because it does work when I manually rename any column.

    Does anyone know what to do to avoid this nasty "One ore more fields in this visualization have disappeared or are broke"-Error?

    Best regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Thursday, June 25, 2015 6:13 PM
  • Hi Julian,

    My english is poor so maybe i misunderstood your needs.
    If headers of your source table ( two columns table) can be different than in your example then propose this code below (independent from header names)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        DemHeaders = Table.DemoteHeaders(Source),
        RemFirstRow = Table.Skip(DemHeaders,1),
        Finish = if List.Count(List.Distinct( RemFirstRow[Column1])) = 1 then 
                    Table.FromColumns({RemFirstRow[Column2]},{List.First(RemFirstRow[Column1])}) 
                 else 
                    List.Combine({{"Wrong data - Column1"},List.Distinct( RemFirstRow[Column1])})
    in
        Finish

    Regards

    • Proposed as answer by Michael Amadi Saturday, June 27, 2015 10:21 AM
    Thursday, June 25, 2015 7:34 PM
  • Hi Bill,

    I tried your new solution. Unfortunately it still does not solve my problem. The problem is not directly in the Power Query part. That is working perfectly. The problem in the report pane of Power BI Designer. 

    This screenshot shows the error. It happens because the report designer seems to be unable to know that the query is just renaming a column - it appears to him that the column was just deleted. If I manually rename a column in Power Query (right clicking the header - rename), the report designer is smart enough to keep the column and just to show the right (new) name.

    Does it make sense?

    Regards

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Thursday, June 25, 2015 8:12 PM
  • Hi Julian,

    this looks like a bug - you should send a frown.

    Try this alternative:

    let
        Source = ...,
        NewName= if List.Count(List.Distinct( Source[AttributeName])) = 1 then List.First(Source[AttributeValue]) else "Wrong data in Attrib Name",
        Rename = Table.RenameColumns(Source,{{"AttributeValue", NewName}}),
        Remove = Table.RemoveColumns(Rename,{"AttributeName"})
    in
        Remove


    Imke

    • Proposed as answer by Michael Amadi Saturday, June 27, 2015 10:21 AM
    • Marked as answer by Julian Wissel Saturday, June 27, 2015 7:09 PM
    Saturday, June 27, 2015 6:47 AM
  • Hi Julian,

    When you rename a column in the Query view of the Power BI Designer Preview, 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. As pointed out by Imke, you can feed this issue back to the Power BI Team using the frown.

    In the meantime you may consider removing the invalid attribute name rows in one of the steps instead of renaming the column altogether. I have adapted the M code provided by Bill and Imke to reflect this approach:

    let
        Source = Table1,
        DemHeaders = Table.DemoteHeaders(Source),
        RemFirstRow = Table.Skip(DemHeaders,1),
        RemInvalidRows = Table.SelectRows(RemFirstRow, each ([Column1] = List.First(RemFirstRow[Column1]))),
        Finish = Table.FromColumns({RemInvalidRows[Column2]},{List.First(RemInvalidRows[Column1])}) 
    in
        Finish

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, June 27, 2015 10:21 AM
  • Thank you everyone for your helpful answers!

    I just sent a frown to Microsoft. Hopefully that bug will be fixed soon.

    Best regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Saturday, June 27, 2015 7:10 PM
  • I just sent a frown for the same thing.  Was trying to do the same thing.  thankfully found this thread.

    The following was the method I'd used to take a generic dimension / attribute table and split it into unique tables for each attributef

    let
        Source = #"Dimension Value",
        DimToUse = Record.Field(Table.First(#"General Ledger Setup"),"Shortcut Dimension 1 Code"),
        #"Filtered Rows" = Table.SelectRows(Source, each [Dimension Code] = DimToUse),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Dimension Code"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name",  DimToUse & " Name"}})
    in
        #"Renamed Columns"

    Thursday, June 30, 2016 5:01 PM
  • "When you rename a column in the Query view of the Power BI Designer Preview, 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. As pointed out by Imke, you can feed this issue back to the Power BI Team using the frown."

    Does anyone know if this bug has actually been fixed in Power BI Desktop?

    Based on a quick test, it doesn't seem so. Or maybe there's some workaround to keep column references in the visuals after applying a bulk rename step (using Table.RenameColumns with a mapping table, or Table.TransformColumnNames)?

    Sunday, December 30, 2018 10:50 PM