Forum Discussion
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
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 :-)
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.:smileyhappy:
Regards
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 you just made my day! Thanks!
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!
Yes; you can Ignore the Missing fields
= Table.RenameColumns(#"Removed Columns", Table.ToRows(fileds), MissingField.Ignore)
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]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 🙂
