Forum Discussion

Mederic's avatar
Mederic
Icon for Post Patron rankPost Patron
8 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
    8 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

     

     

     

Mederic's avatar
Mederic
Icon for Post Patron rankPost Patron
8 months ago

Hello 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's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    8 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