Replace values

[Lmendes]

Hello

I am struggling with two things to do with replacing values. 

https://www.dropbox.com/s/0j792l4jlkfsigr/Replace%20values.pbix?dl=0

https://www.dropbox.com/s/vvedv43zubsptxh/Replace%20value%20issue.xlsx?dl=0 

1. I need to do a conditional replace values. In the linked examples (one pbix file and one excel) i would like to say;

If {Q1,Q2,Q3} = "Not at all" then 0 else 1 - but I have no idea how to write this in 'm' language. 

2. I have data whereby the response is spelt wrongly or with CAPs in some answers and lowercase in others. Is there a way in m language the below

If {Do you agree with this rating?} = "Yes" or "yes" or "y" then 1 else 0

I have attached a sample file - I am not very well versed in m language so if you can make the solution user friendly it will be greatly appreciated!

Laura

[raja_pandian]

I've replaced the value from "Aptiv" to "Karnataka"

But for some reason, I can still see "Aptiv" , similar to these there are many other values didnt change. Not sure why it didnt change.

I used Advanced Editor to change, I have provided the below code for your reference.

#"Replaced Value82" = Table.ReplaceValue(#"Replaced Value81","Aptiv ","Karnataka",Replacer.ReplaceText,{"State/Province"}),

Also another state name "Andhra Pradesh" repeating twice, I have used trimmed and clean columns before replacing the value but still no changes. Can somebody provide some inputs on this please.

[dobregon]

it should work

in any case you have another option. is to create a dataset with 2 columns, nameold and namenew and then in dax create a new column like finalname that check the newname

[raja_pandian]

Thanks for your quick response. Appreciate your solution. However, For some reason, I can still see the same result.

[dobregon]

In powerBI you can do using the commands automatically.

In the query editor window you have the option in tab transform to "replace values". you need to do

- Select the column that you want to analyze
- Click on replace columns
- Write the condition

Maybe you need to do this for each condition that you have. Automatically the powerbi will write this in the M query.

Same for second point

[Lmendes]

Hi

Thanks so much for your advice. I am familiar with the replace values step unfortunately I have to do this for several columns and responses so it would take me quite a while. It would be much more efficent for me to be able to replace one value and use else for all others.

Thank you

Laura

[parry2k · Accepted solution]

@Lmendes solution is attached, check power query replace steps.

[Lmendes]

Brilliant thanks so much!

Just a pointer for learners (like me). I had to include [Q3] as [#"Q3"] and [Q2] as [#"Q2"] etc.... for it to work. But it now works like a dream 🙂