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"

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