Forum Discussion

david2's avatar
david2
Icon for Helper I rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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
Icon for Helper I rankHelper 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