Forum Discussion

Mederic's avatar
Mederic
Icon for Post Patron rankPost Patron
7 months ago
Solved

Replace 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 Li...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    7 months ago

    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

     

     

     

Riny_vE's avatar
Riny_vE
Icon for Helper I rankHelper I
7 months ago

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"