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:NameMember_StatusAddress_typeJimAAAASteveABAALisaAAABGregACABAmyACAA Code Location Table: CVTnametablenamecolumnnameMemberstatusCustomerFactMember_StatusaddressusedCustomerFactAddress_typeMemberstatusAccountFactAccount_Status Code Definition Table: CVTnameKeyDecriptionMemberstatusAAActiveMemberstatusABSuspendedMemberstatusACInactiveaddressusedAAHomeaddressusedABWork Desired Output (from Customer Table): NameMember_StatusAddress_typeJimActiveHomeSteveSuspendedHomeLisaActiveWorkGregInactiveWorkAmyInactiveHome Any help is much appreciated Thanks, Simon

Forum Discussion

New Member

4 years ago

Solved

Using a find and replace based on another table

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

Need Help

Shishir22

4 years ago

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.

4 Replies

amitchandak
Super User
4 years ago
Stex85 , New columns on the customer table

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

Add other in same way

Shishir22

Solution Sage

4 years ago

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
New Member
4 years ago
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
  Solution Sage
  4 years ago
  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"

Forum Discussion

Using a find and replace based on another table

4 Replies

Related content

Conditional Replace Using Outside table

replacing values based on another column using M

Replace existing image with another

Replace null values of one column to another column.

Use measures from another semantic model