Using a find and replace based on another table

Hello Stex85 ,

I am assuming that you have 'N' number of columns like this and you need to add information.

Please follow below steps-

Step 1- Add custom column in Code Defination table using below code-

   #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[CVTname], [Key]}), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "MergeKey"}})
in
    #"Renamed Columns"

Step 2-

Add below transformation in your customer table-

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Member_Status", type text}, {"Address_type", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"Attribute"}, #"Code Location", {"columnname"}, "Code Location", JoinKind.LeftOuter),
    #"Expanded Code Location" = Table.ExpandTableColumn(#"Merged Queries", "Code Location", {"CVTname"}, {"Code Location.CVTname"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Code Location", "Custom", each [Code Location.CVTname]&[Value]),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Code Definition", {"MergeKey"}, "Code Definition", JoinKind.LeftOuter),
    #"Expanded Code Definition" = Table.ExpandTableColumn(#"Merged Queries1", "Code Definition", {"Decription"}, {"Code Definition.Decription"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Code Definition",{"Code Location.CVTname", "Custom", "Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Code Definition.Decription", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Attribute]), "Attribute", "Code Definition.Decription")
in
    #"Pivoted Column"

You should get desired result.

Please mark it as answer if it solves your issue. Kudos are also appreciated.