Power query conditional replacement

[Anonymous]

Hello and thanks in advance for the help.

I'm trying to do some conditional replacement in power query and struggling with the syntax.

I have a customer field which is like "customer name (12345678)" which I would like to use as a condition for some text replacement where I can redact data for all other customers across a number of fields.

2 asks are

Replace the contents of a few fields with the word "Redacted" if the customer field is not = "Example Customer"

Where the contents of the customer field is not = "Example Customer" replace the value of the customer field with "Redacted" & the last 10 characters of the Customer field.

Here's the current code I have but I can't get it to work

#"Replaced Value" = Table.ReplaceValue(#"Added Custom3",each [CustomerCompanyName],each if not "Example Customer","Redacted",Replacer.ReplaceValue,{"Field 1", "Field 2", "Field 3"})

[Anonymous]

Hi @Anonymous,

Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

@lbendlin  thanks for the reply 🙂

This particular data is simply being loaded from an Excel / CSV file.  I've attached a short test data sample.  The picture below maybe explains what I'm trying to do more clearly.  Where the customer is not CustomerA I want to replace all of the values in a list of columns - but not all - here the yellow gets replaced with "Redacted".  And the customer names become "Redacted"& the last 10 characters (green and blue).

CustomerName(sensitiveData)	Product(OpenData)	SensitiveDataField1	SensitiveDataField2	SensitiveDataField3
Customer0000000001	Outlook	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000001	Excel	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000001	Windows	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000001	Power Bi	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000001	Outlook	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000001	Excel	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000001	Windows	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000001	Power Bi	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000002	Outlook	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000002	Excel	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000002	Windows	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000002	Power Bi	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000002	Outlook	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000002	Excel	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000002	Windows	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Power Bi	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Outlook	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Excel	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Windows	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Power Bi	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Outlook	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Excel	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Windows	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Power Bi	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Outlook	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Excel	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Windows	Sensitive text here	Sensitive text here	Sensitive text here
Customer0000000003	Power Bi	Sensitive text here	Sensitive text here	Sensitive text here

[lbendlin · Accepted solution]

With kudos to Replace Values in Power Query - Ultimate Guide - BI Gorilla

and (originally) 

 @ziying35 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS0ygAFDJR0l/9KSnPz8bCArODWvOLMksyxVoSS1okQhI7UolSTRWB0cVrhWJKfm0NKC8My8lPzyYlpaEZBfnlqk4JRJSztGo2JQRIUR7aPCiNZRYUT7qDAajYrBEhXGdIgKY9pHhTGto8J4NCpGo2I0KoZ+VMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"CustomerName(sensitiveData)" = _t, #"Product(OpenData)" = _t, SensitiveDataField1 = _t, SensitiveDataField2 = _t, SensitiveDataField3 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each [#"CustomerName(sensitiveData)"]<>"Customer0000000001",  "Redacted", (x,y,z)=> if y then z else x ,{"SensitiveDataField1","SensitiveDataField2","SensitiveDataField3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [#"CustomerName(sensitiveData)"]<>"Customer0000000001", each "Redacted" & Text.End([#"CustomerName(sensitiveData)"],10), (x,y,z)=> if y then z else x ,{"CustomerName(sensitiveData)"})
in
    #"Replaced Value1"

[lbendlin]

@Anonymous Where did "{"Field 1", "Field 2", "Field 3"}"  come from?

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) and show the expected outcome.