Replace values and rename headers based on lookup table

[Anonymous]

Hi there, 

I hope I am posting this in the correct place, it is my first time on this forum. 🙂

I would like to create a function which replaces values in a column based on a 'lookup' table and then rename the column headers based on a different 'lookup' table.

I have the following lookup tables:

1) ReplaceValues - This table states the values I would like to replace in each column/table.

2) ReplaceHeaders - This table states what I would like the headers renaming too for each table.

ReplaceValues:

Table name	Column name	Old value	New value
Customer Table	CustGroup	A	A-1
CustomerTable	CustGroup	B	B-Cust
VendorTable	VendGroup	B	B-Vend

ReplaceHeaders:

Table name	Old Header	New Header
CustomerTable	CustAcc	CustomerAccountNumber
CustomerTable	CustGroup	CustomerGroup
VendorTable	VendAcc	VendorAccountNumber
VendorTable	VendGroup	VendorGroup

The source tables look like this...

CustomerTable:

CustAcc	CustGroup
C001	A
C002	D
C003	B
C004	A

VendorTable:

VendAcc	VendGroup
V001	A
V002	C
V003	B
V004	B

I would like to write a function to first replace the values in the CustGroup column (CustomerTable) and VendGroup column (VendorTable) according to the data in the ReplaceValues lookup table.

And then rename the column headers in the CustomerTable and VendorTable according to the data in the ReplaceHeaders lookup table.

So I get a result which looks like this...

CustomerTable - results:

CustomerAccountNumber	CustomerGroup
C001	A-1
C002	D
C003	B-Cust
C004	A-1

VendorTable - results:

VendorAccountNumber	VendorGroup
V001	A
V002	C
V003	B-Vend
V004	B-Vend

I have tried to adapt various scripts I have found on the web, like in the link below, but had no luck. Any help would be greatly appreciated! 

Thank you in advance,

James

Link: https://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-i...

[Anonymous]

Hi @Anonymous 

Something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjYwMFTSUXJUitUBc4yAHBcYxxjIcYJxTCDKYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CustAcc = _t, CustGroup = _t]),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"CustAcc", type text}, {"CustGroup", type text}}),

    // Set variables
    mTableName = "CustomerTable",
    SourceTable = #"Change Type",

    // Replace values
    FilterValues = Table.SelectRows(ReplaceValues, each [Table name] = mTableName and List.Contains (Table.ColumnNames(SourceTable), [Column name])),
    #"Replaced Values" = List.Accumulate(Table.ToRecords(FilterValues), SourceTable, (s, a) => Table.ReplaceValue(s,a[Old value],a[New value],Replacer.ReplaceText,{a[Column name]})),
    

    // Replace headers
    FilterHeaders = Table.SelectRows(ReplaceHeaders, each [Table name] = mTableName and List.Contains (Table.ColumnNames(SourceTable), [Old Header])),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Values",List.Zip({FilterHeaders[Old Header], FilterHeaders[New Header]}))
in
    #"Renamed Columns"

Kind regards,

JB

[Anonymous]

Hi JB, 

Thanks for your reply 🙂 - very quick!

Unfortunately that didn't seem to work for me.

I have uploaded the Excel Workbook with all the tables/connections to Google Drive, if that helps!?

See Excel doc here - https://drive.google.com/open?id=1RIwZ03veLlx71PUy86_HaYbptKKy9VWS

Thanks for your help, really appreciate it. 

Cheers, 

James 

[Anonymous · Accepted solution]

Hi @Anonymous ,

in the sample data in the original post you have column headers in "sentence" case (i.e. "Old values"), but in the same file you shared, they are in "Capitalise Each Word" case (i.e. "Old Values"). Power BI is quite case-sensitive.

Please rename headers in your file to match the original post. 😁

I also sent you a link to your updated file, it contains the code that works with the column headers as they currently appear in the file (i.e. "Old Values").

Kind regards,

JB

[Harshini5]

Hi, Can you please share the solution here? I have a similar case. 

[Anonymous]

JB, thank you so much. This worked perfectly 🙂 Thank you! J