Forum Discussion
Replace values and rename headers based on lookup table
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
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
5 Replies
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
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
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