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

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

      • david2's avatar
        david2
        Helper I
        9 years ago

        Hi v-ljerr-msft

         

        There's no space in the "title" column in pipedriveDeals nor the row in the pipedriveFields table. I deleted this line from the table to see if the script works with another one of the columns, but unfortunately still get the same problem.

         

        I've also tried to make the column with keys in the pipedriveFields table a string, but that also doesnt work.

         

        What are the conditions for the Table.RenameColumns and Table.ToRows functions to work properly?

         

        • I assume the list of columns in pipedriveFields should exactly match the list of rows in the pipedriveFields table
        • The list of keys (that matches the column headers in pipedriveFields) should be in the first column of pipedriveFields
        • Anything else?

         

        Any other ideas on this one? Thanks :-)