Forum Discussion

david2's avatar
david2
Helper I
9 years ago
Solved

Auto-renaming columns based on value in another table

Hi all,

 

I have connected PowerBI to our CRM software (Pipedrive). I pull data relating to our customers / deals from Pipedrive using a JSON API call. Each deal / customer is saved as an object with various (50+) attributes. The data is reflected in PBI as a row for each customer, with a column for each attribute (see table 2 in the simplified example in the pic below)

 

The attributes are saved in Pipedrive using a unique key with 24 random characters.  The columns in my table with deals/customers are these keys, which are obviously not very descriptive. I have another table in PBI which links the field key to a more descriptive name, like Customer Type (table 1 in pic below)

 

I would like to update the column names of table 2, using the values in table 1, so that i get something that looks like table 3. Anyone know if/how this is possible?

 

Thanks,

 

David

 

 

 

 

 

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi david2,

     

    After looking into the pbix file and JSON files you shared with me, I found several issues you may need to pay attention to.

     

    1. Use formula below in your Advanced Editor.

        #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",Table.ToRows(fields))
in
    #"Renamed Columns"

    Not

        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",Table.ToRows(fields))
in
    #"Renamed Columns"

    2. As a table cannot have two columns with the same column name, please make sure all values of "name" column in "fields" table are unique and not already exist in "deals" table.

     

    Here are the modified pbix file and JSON files for your reference.

     

    Regards

12 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi david2,

    I would like to update the column names of table 2, using the values in table 1, so that i get something that looks like table 3.

    This can be done within Power BI Query Editor using Power Query. See my sample below.

     

    Assume we have already loaded the following two tables into Power BI Desktop.

    Table1

    Table2

    1. Open Query Editor, drag the "fieldKey" column to the first column for Table1.

    2. Add the the following formula into Advanced Editor for Table2 and click Done.

        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",Table.ToRows(Table1))
in
    #"Renamed Columns"

    3. Click "Close&Apply", then the column names of Table2 will be updated using the values in Table1.

     

    Regards

    • david2's avatar
      david2
      Helper I
      9 years ago

      Hi v-ljerr-msft

       

      Thanks a lot for the quick reply! I got the query up and running after some editing, but i came across an issue i can't fix.

       

      Table1 from the example is called pipedriveFields in my dataset, Table2 is pipedriveDeals

       

      In the pics below you'll see that:

       

      1. The query is working, no syntax errors, with a few small edits made to your suggestion. Thanks!
      2. For some reason, the column "title" in pipedriveDeals can not be renamed as it was not found
      3. However, if you look at the columns in pipedriveDeals, there is in fact a column named "title"
      4. In addition, the table pipedriveFields contains a line with the fieldKey "title" which i would like to get translated to "Title" (obviously, this example is not that relevant to translate but for other columns its much more important)

       

      Somehow, the "title" row in pipedriveFields and the "title" column in pipedriveDeals do not match (and i assume the same holds true for other row/column combinations).

       

      Any idea why this happens?

       

      Thanks :-)

       

      1234

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi david2,

         

        Could you go and check if there is any blank spaces within the column name of  "title" in pipdriveDeals(like "title    ").

         

        Regards

  • HASSANNAZEER's avatar
    HASSANNAZEER
    Frequent Visitor
    8 years ago

    Need help in renaming Header names based on another table. 
    Could you please help ?