Auto-renaming columns based on value in another table

[david2]

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

[HASSANNAZEER]

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

[v-ljerr-msft]

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]

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]

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]

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]

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.

Regards

[v-ljerr-msft · Accepted solution]

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

[kdhasiuk]

Hi There,

I know this is an old post, but I am hoping you can help me.  I am getting the same error after creating my table of header names.  I am wondering how to fix once and for all.  I have tried multiple ways to do this, but with no luck so far.

Expression.Error: We expected a RenameOperations value.
Details:
[List]

[RobinNeven]

I know this is an old thread, but for me the trick was to convert the key field in Table1 (which in my case contains numbers) to text data type 🙂

[snowrider1799]

Hi @v-ljerr-msft ,

I have the same problem as stated above, however, I dont have all the columns name exists in "deals". Is there a way to select what is exists, and ignore those unavailable columns to be rename?

Thanks!

[soni27]

Yes; you can Ignore the Missing fields

= Table.RenameColumns(#"Removed Columns", Table.ToRows(fileds), MissingField.Ignore)

[david2]

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