Forum Discussion
How to replace a value based on multiple conditions
I have a data feed coming from an external system into PowerBI. Over a number of months, several users selected incorrect data in that system and now I need to clean it up in PowerBI because unfortunately it is too late to make the changes in that system. Data will be correct going forward but I need to change the historical info.
Here is what I am trying to do in words
If [JOB] = "DJ-229" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0033" in [NAME] to "ST-0033"
There are several other cleanup items but they are all very similar to the one above. Note that as you can see in the screenshot LS-0033 is not the full contents of the cell.
I've tried a few things but all have failed miserably. Any help would be appreciated.
HI davidoz,
You can try to add a custom column with the below if statement expressions to do replace value operations:
#"Added Custom" = Table.AddColumn(#"Changed Type","Replace", each if [PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator" then if [JOB] = "DJ-229" then "ST-0033" else if [JOB] = "DJ-50" then "ST-0014" else if [JOB] = "DJ-85" then "ST-0098" else [NAME] else [NAME] )Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL09KTM5W0lEKTi1ILEosyS8qVogpNTAwTlUwNDDQNbIwLSjOVAgGiiempyrAFQE1uHjpGhlZAhk+wbpA9cZKsToUG2hqADPPxIQa5lmYwswzNKSGeUaGVPawhakhsgtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PHASE_NAME = _t, TYPE = _t, JOB = _t, NAME = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PHASE_NAME", type text}, {"TYPE", type text}, {"JOB", type text}, {"NAME", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type","Replace", each if [PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator" then if [JOB] = "DJ-229" then "ST-0033" else if [JOB] = "DJ-50" then "ST-0014" else if [JOB] = "DJ-85" then "ST-0098" else [NAME] else [NAME] ) in #"Added Custom"Regards,
Xiaoxin Sheng
5 Replies
Hi davidoz
Please create a new column using the code below code and give it a try.
If (Table1[JOB] = "DJ-229" && Table1[PHASE_NAME] = "Flowback" && Table1[TYPE] = "Separators > 100-285psi Storage Separator" && Table1[NAME] = "LS-0033","ST-0033",Table1[NAME])davidoz add new custom column in Power Query, let's call it New Name
if [JOB] = "DJ-229" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" and [Name] = "LS-0033" then "ST-0033" else [Name]after the above column is added, remove the existing Name column and rename New Name to Name. Change the logic as you see fit.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
so the issue with the "create a new column" solutions is that I need to run this 3 or 4 times. And I also only need to change part of the name, not the entire name.
For example:
If [JOB] = "DJ-229" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0033" in [NAME] to "ST-0033"
If [JOB] = "DJ-50" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0044" in [NAME] to "ST-0014"
If [JOB] = "DJ-85" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0011" in [NAME] to "ST-0098"
Any ideas?
HI davidoz,
You can try to add a custom column with the below if statement expressions to do replace value operations:
#"Added Custom" = Table.AddColumn(#"Changed Type","Replace", each if [PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator" then if [JOB] = "DJ-229" then "ST-0033" else if [JOB] = "DJ-50" then "ST-0014" else if [JOB] = "DJ-85" then "ST-0098" else [NAME] else [NAME] )Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL09KTM5W0lEKTi1ILEosyS8qVogpNTAwTlUwNDDQNbIwLSjOVAgGiiempyrAFQE1uHjpGhlZAhk+wbpA9cZKsToUG2hqADPPxIQa5lmYwswzNKSGeUaGVPawhakhsgtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PHASE_NAME = _t, TYPE = _t, JOB = _t, NAME = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PHASE_NAME", type text}, {"TYPE", type text}, {"JOB", type text}, {"NAME", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type","Replace", each if [PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator" then if [JOB] = "DJ-229" then "ST-0033" else if [JOB] = "DJ-50" then "ST-0014" else if [JOB] = "DJ-85" then "ST-0098" else [NAME] else [NAME] ) in #"Added Custom"Regards,
Xiaoxin Sheng
Thank you. This is the solution I went with. It wasn't Ideal having to make a custom column but I couldn't find a way around that
