Power query conditional replacement

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 (12...

Need Help

lbendlin

4 years ago

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"

Anonymous

Not applicable

4 years ago

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

Super User

4 years ago

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"

Forum Discussion

Power query conditional replacement

Related content

Replace value in Power Query based on condition

Conditional Replace Using Outside table

Replace value based on condition from other column

Table Replace Value multiple condition

Case insensitive replacement in Power Query