cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
david2 Regular Visitor
Regular Visitor

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

 

tables.JPG

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Auto-renaming columns based on value in another table

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

8 REPLIES 8
v-ljerr-msft Super Contributor
Super Contributor

Re: Auto-renaming columns based on value in another table

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

t1.PNG

Table2

t2.PNG

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

reorderc.PNG

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"

ade.PNG

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

result.PNG

 

Regards

david2 Regular Visitor
Regular Visitor

Re: Auto-renaming columns based on value in another table

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 :-)

 

1.png12a.png23.JPG34.png4

v-ljerr-msft Super Contributor
Super Contributor

Re: Auto-renaming columns based on value in another table

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 Regular Visitor
Regular Visitor

Re: Auto-renaming columns based on value in another table

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 :-)

v-ljerr-msft Super Contributor
Super Contributor

Re: Auto-renaming columns based on value in another table

Hi @david2,


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?

 


I don't think there are any other conditions for the formula to work. I went to test it again on my side and it worked all fine for me. Could you share a sample pbix file which can reproduce the issue in your case? You can upload it to OneDrive or Dropbox, and send me the link in private message. Do mask sensitive data before uploading.Smiley Happy

 

Regards

v-ljerr-msft Super Contributor
Super Contributor

Re: Auto-renaming columns based on value in another table

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

david2 Regular Visitor
Regular Visitor

Re: Auto-renaming columns based on value in another table

@v-ljerr-msft you just made my day! Thanks!

HASSANNAZEER Frequent Visitor
Frequent Visitor

Re: Auto-renaming columns based on value in another table

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