cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JamesWinfield Frequent Visitor
Frequent Visitor

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 nameColumn nameOld valueNew value
Customer TableCustGroupAA-1
CustomerTableCustGroupBB-Cust
VendorTableVendGroupBB-Vend

 


ReplaceHeaders:

Table nameOld HeaderNew Header
CustomerTableCustAccCustomerAccountNumber
CustomerTableCustGroupCustomerGroup
VendorTableVendAccVendorAccountNumber
VendorTableVendGroupVendorGroup


The source tables look like this...

 

CustomerTable:

CustAccCustGroup
C001A
C002D
C003B
C004A


VendorTable:

VendAccVendGroup
V001A
V002C
V003B
V004B


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:

CustomerAccountNumberCustomerGroup
C001A-1
C002D
C003B-Cust
C004A-1


VendorTable - results:

VendorAccountNumberVendorGroup
V001A
V002C
V003B-Vend
V004B-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...

1 ACCEPTED SOLUTION

Accepted Solutions
jborro Member
Member

Re: Replace values and rename headers based on lookup table

Hi @JamesWinfield ,

 

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

 

 

 

View solution in original post

4 REPLIES 4
jborro Member
Member

Re: Replace values and rename headers based on lookup table

Hi @JamesWinfield 

 

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

JamesWinfield Frequent Visitor
Frequent Visitor

Re: Replace values and rename headers based on lookup table

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 

jborro Member
Member

Re: Replace values and rename headers based on lookup table

Hi @JamesWinfield ,

 

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

 

 

 

View solution in original post

JamesWinfield Frequent Visitor
Frequent Visitor

Re: Replace values and rename headers based on lookup table

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,753)