Forum Discussion
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 (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"})
With kudos to Replace Values in Power Query - Ultimate Guide - BI Gorilla
and (originally)
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"
4 Replies
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.
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 With kudos to Replace Values in Power Query - Ultimate Guide - BI Gorilla
and (originally)
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"
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