Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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

 

 

 

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors