Forum Discussion
Post PatronReplace Old name with New name
Hello,
This is probably an easy task for some of you.
In my attached file, I have two tables: a ‘Source’ table and a ‘List’ table.
I would like to replace the Name column with the text from the List table.
I do not want to use "Merge Queries" but rather a fonction such "Replace...."
For your information, my two tables actually contain 5,000 to 6,000 rows, and the Source table has about ten columns.
Thank you in advance.
Best regards
This is the same principle, but perhaps a bit simpler.
Note that it is not necessary to check for an invalid "Num".
Apparently, within the Replacer function, ListeNums{-1} is interpreted the same as ListeNums{-1}? which returns null. And a null within that Replacer function results in the original contents of the column being retained.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Num", Int64.Type}, {"Name", type text}}), ListeNums = List.Buffer(Table2[Num]), ListeNames = List.Buffer(Table2[Name]), Replace = Table.ReplaceValue( #"Changed Type", each [Name], each [Num], (x,y,z) as text => ListeNames{List.PositionOf(ListeNums,z)}, {"Name"}) in Replace
4 Replies
- Riny_vE
Helper I
Why not use Merge? Very basic and only by clicking in the UI
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Num", Int64.Type}, {"Name", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Num"}, Table2, {"Num"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Name"}, {"Name.1"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each [Name.1] ?? [Name]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Num", "Custom"}) in #"Removed Other Columns" 
MedericHello Riny_vE ,
Thank you for your reply.
Why not use ‘Merge Queries’?
Because I know how to use it and I want to avoid adding unnecessary steps.
I managed to find a solution using List.PositionOf that works in this case.
Thank you anyway.
Best regards.let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Num", Int64.Type}, {"Name", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [Name], each if List.PositionOf(Table2[Num],[Num])=-1 then [Name] else Table2[Name]{List.PositionOf(Table2[Num],[Num])} , Replacer.ReplaceText,{"Name"}) in #"Replaced Value"- ronrsnfld
Super User
This is the same principle, but perhaps a bit simpler.
Note that it is not necessary to check for an invalid "Num".
Apparently, within the Replacer function, ListeNums{-1} is interpreted the same as ListeNums{-1}? which returns null. And a null within that Replacer function results in the original contents of the column being retained.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Num", Int64.Type}, {"Name", type text}}), ListeNums = List.Buffer(Table2[Num]), ListeNames = List.Buffer(Table2[Name]), Replace = Table.ReplaceValue( #"Changed Type", each [Name], each [Num], (x,y,z) as text => ListeNames{List.PositionOf(ListeNums,z)}, {"Name"}) in Replace
