Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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...

 

 

  • 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.

9 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      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's avatar
        Anonymous
        Not applicable

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      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. 

  • ImkeF's avatar
    ImkeF
    Community Champion

    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's avatar
    ImkeF
    Community Champion

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      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's avatar
    ImkeF
    Community Champion

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      I have already!:)