How to replace a value based on multiple conditions

[davidoz]

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.

[davidoz]

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?

[v-shex-msft · Accepted solution]

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

[davidoz]

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

[parry2k]

@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.⚡

[pranit828]

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])