How to reference list of lists in Table.RenameColumns function?

[Anonymous]

Table.RenameColumns takes two values -- the table you want to modify, and a list (or list of lists) containing the old and new column names. 

Example given in Power Query documentation:

Replace the column name "CustomerNum" with "CustomerID" and "PhoneNum" with "Phone" in the table.

Table.RenameColumns( Table.FromRecords({[CustomerNum = 1, Name = "Bob", PhoneNum = "123-4567"]}), { {"CustomerNum", "CustomerID"}, {"PhoneNum", "Phone"} } )

I have a list of lists in another query that I tried using in this function. However, I'm getting an error/the function is not accepting my list of lists. 

I created two codes that you can use to replicate the data and all my steps, which you can paste directly into the advanced editor to create a new query.

Code for the list of lists:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilHyz0nxS8xNNYxRUtIBcv1Sy2HcWB0keSNUeSN0eWNUeRA3NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OldNames = _t, NewNames = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OldNames", type text}, {"NewNames", type text}}),
#"merge old name and new name columns" = Table.CombineColumns(#"Changed Type",{"OldNames", "NewNames"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Old and New Names"),
#"create list of lists" = Table.ToRows(#"merge old name and new name columns")
in
#"create list of lists"

And here is a code which contains the table I am trying to apply the function to, and the failed result:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkksSTRU0gHTRlDaWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OldName1 = _t, OldName2 = _t, OldName3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OldName1", type text}, {"OldName2", type text}, {"OldName3", type text}}),
#"run table rename columns function" = Table.RenameColumns(#"Changed Type", #"list of lists")
in
#"run table rename columns function"

When I run the Table.RenameColumns function, I get the error:

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

But #"list of lists" is a list, and should be formatted exactly like the list of lists used in the documentation example I provided, no?

I appreciate any help that can be provided...

[ImkeF]

Hi @Anonymous ,
great!
So if this is solved, please mark my answer as solution. Thanks.

[Anonymous]

I have already!:)

[ImkeF]

Hi @Anonymous ,
if you post the query code I can take a look at it.

[Anonymous]

Hi, I've discovered the problem, a null value was being loaded into the nested list, which was throwing me the error. I appreciate your help!

[ImkeF]

Hi @Anonymous ,
usually the quotation marks are added automatically once you reference column with the strings in it.
Can you please share more specifically what the actual basis for your operation looks like?

[ImkeF · Accepted solution]

Hi @Anonymous ,
just omit the merge step and you should be good to go:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilHyz0nxS8xNNYxRUtIBcv1Sy2HcWB0keSNUeSN0eWNUeRA3NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OldNames = _t, NewNames = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OldNames", type text}, {"NewNames", type text}}),
#"create list of lists" = Table.ToRows(#"Changed Type")
in
#"create list of lists"

By merging the columns, you are creating one string that will then become a list with just one element.
But you need a list with 2 elements in it. So omitting the merge will just solve the issue.

[Anonymous]

Hi Imke -- 

The example I provided you worked after I made that adjustment in my query. But when I ran the same steps on my actual data, I am still getting the same error, even though I have a nested list: 

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

For some reason, my query doesn't seem to recognize that the query with my nested list is a list. 

[Anonymous]

ahh thank you! bonus question for you: do you know a quick way to wrap all strings in each cell in quotations? my actual dataset contains all the find and replace values but they are not wrapped in quotation marks, so they look like this: Oldval, Newval .. instead of this: "Oldval", "Newval"

[Anonymous]

oh I think I got confused and thought I needed to add quotation marks. I will test things out on my end.