Using a find and replace based on another table

[Stex85]

Hi all,

Apologies if this has been asked before I have a database which I access which has codes to replace data. As a reference it contains another table which has a list of the codes used and where they are used, and a separate table with what the codes mean. What I'm looking for is a way to replace the codes with their meanings. I've put some sample data below, which will hopefuly make it a bit clearer! Annoyingly none of the codes or the column names are unique!

Customer Table:

Name	Member_Status	Address_type
Jim	AA	AA
Steve	AB	AA
Lisa	AA	AB
Greg	AC	AB
Amy	AC	AA

Code Location Table:

CVTname	tablename	columnname
Memberstatus	CustomerFact	Member_Status
addressused	CustomerFact	Address_type
Memberstatus	AccountFact	Account_Status

Code Definition Table:

CVTname	Key	Decription
Memberstatus	AA	Active
Memberstatus	AB	Suspended
Memberstatus	AC	Inactive
addressused	AA	Home
addressused	AB	Work

Desired Output (from Customer Table):

Name	Member_Status	Address_type
Jim	Active	Home
Steve	Suspended	Home
Lisa	Active	Work
Greg	Inactive	Work
Amy	Inactive	Home

Any help is much appreciated

Thanks,

Simon

[Shishir22 · Accepted solution]

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.

[Stex85]

Hi, sorry, getting a token EOF expected error. Here's the code for the table

let
Source = Sql.Database("XXXXXXXXXXX", "xxxxxxxxxx"),

cvt_cvtValues = Source{[Schema="cvt",Item="cvtValues"]}[Data]

in
"code definition"

#"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"

[Shishir22]

Hello,

Please remove 

in

"code defination"

from above code. And use-

let
Source = Sql.Database("XXXXXXXXXXX", "xxxxxxxxxx"),

cvt_cvtValues = Source{[Schema="cvt",Item="cvtValues"]}[Data]

#"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"

[amitchandak]

@Stex85 , New columns on the customer table

maxx(filter(code, code[Key] = Customer[Member_Status]  && [CVTname] = "Memberstatus"), Code[Decription])

Add other in same way