Renaming columns in PQ : We expected a RenameOperations value

[Aleksandra_MLT]

Hi guys, wonder if you could help, keep getting this error when trying to rename columns in DataFlow. Error comes on the last Rename step:

Same code works with other Flows/ entities. The code is below: 

let
  Source = PowerBI.Dataflows(),
  Workspace = Source{[workspaceName = WorkspaceSwitch]}[Data],
  Dataflow = Workspace{[dataflowName = "FP&L History"]}[Data],
  DataFlow = Dataflow{[entity = "(CY-4)"]}[Data],
  headers = Table.ColumnNames(DataFlow),
  #"Converted to table 1" = Table.FromList(headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Renamed columns" = Table.RenameColumns(#"Converted to table 1", {{"Column1", "ColumnName"}}),
  #"Added custom" = Table.AddColumn(#"Renamed columns", "Entity", each Dataflow[entity]),
  #"Expanded Custom" = Table.ExpandListColumn(#"Added custom", "Entity"),
  #"Added custom 1" = Table.AddColumn(#"Expanded Custom", "CompoundKey", each [Entity] & "-" & [ColumnName]),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Added custom 1", {{"ColumnName", type text}, {"Entity", type text}, {"CompoundKey", type text}}),
  #"Replaced value" = Table.ReplaceValue(#"Changed column type 1", "#(cr)#(lf)", " ", Replacer.ReplaceText, {"CompoundKey"}),
  #"Capitalized each word" = Table.TransformColumns(#"Replaced value", {{"CompoundKey", each Text.Proper(_), type nullable text}}),
  #"Replaced value1" = Table.ReplaceValue(#"Capitalized each word", " ", "", Replacer.ReplaceText, {"CompoundKey"}),
  #"Merged queries" = Table.NestedJoin(#"Replaced value1", {"CompoundKey"}, #"T0-Translation_Columns-LE", {"CompoundKey"}, "Ingested Columns List", JoinKind.LeftOuter),
  #"Expanded Ingested Columns List 1" = Table.ExpandTableColumn(#"Merged queries", "Ingested Columns List", {"New Column Name"}, {"Ingested Columns List.New Column Name"}),
  #"Filtered rows" = Table.SelectRows(#"Expanded Ingested Columns List 1", each [Entity] ="(CY-4)"),
  #"Removed other columns 1" = Table.SelectColumns(#"Filtered rows", {"ColumnName", "Ingested Columns List.New Column Name"}),
  #"Merged columns" = Table.CombineColumns(#"Removed other columns 1", {"ColumnName", "Ingested Columns List.New Column Name"}, Combiner.CombineTextByDelimiter(",", QuoteStyle.None), "Merged"),
  #"Added custom 4" = Table.AddColumn(#"Merged columns", "Custom5", each Text.Split([Merged], ",")),
  Navigation = #"Added custom 4"[Custom5],
  Rename = Table.RenameColumns(DataFlow, Navigation)
in
  Rename

[Anonymous]

I am experiencing the same issue despite being certain that my nested list was created properly and is being referenced properly...

[Syndicate_Admin]

The problem is both of your columns on list should be String types. 

[BA_Pete]

Hi @Aleksandra_MLT ,

It doesn't look as though your final Rename step is providing the arguments as required by the function.

Per MS Docs:

It doesn't appear as though you've provided a list of {"nameFrom", "nameTo"} pairs. I can't really tell from the code exactly what your Navigation step is resolving to, but I'm guessing it's not a compatible list format for this function.

Also, FWIW, the thing you did with the Dataflow/DataFlow steps with the change in capitalisation to keep the same word is pretty bad form. Try to use more unique names to assist with future reading/debugging. 🙂

Pete

[Aleksandra_MLT]

Thanks, @BA_Pete ,

Navigation step outcome as follows: 

So per MS documentation : "A replacement operation renames consists of a list of two values, the old column name and new column name, provided in a list."

So still do not get where the issue resides.. 

[BA_Pete]

Fair enough, looks fine.

The only thing I can think that might cause this particular error would be if your list pairs were not all being formed correctly, i.e. if one of the pair values evaluated to null/integer/special character somehow.

Have you tried clicking on every one of your nested lists in your Navigation step to ensure that each one includes two valid values?

Another issue sometimes occurs when you have more pair values in your lists than columns in the table (or the old names don't match), but I don't think that would throw this error.

Pete

[vhatp]

I was getting this error as long as my renames list contained null within some of the pairs, e.g. {"oldNameX",null}. As this was a consequence of List.Zipping two lists of uneqal lenghts, the solution was to trim the lists to same size. After, the error was gone - even though the renames list was in fact shorter than the count of columns to be renamed. The rest simply stayed unrenamed, without any error.