Help with a very Basic changing value

[yemara]

Hello,

I just found a mistake in my table after uploading it to POwer BI Desktop. I had 2 columns one with "Country" and other "Region".

Where portugal in the country column is by mistake coded as In Middle East & Africa in the region column. How to change it?

I went to Power Query and filtered portugal in the country and did replace value then it worked. But couldn't remove the filer. Can someone help...

[v-junyant-msft · Accepted solution]

Hi @yemara ,

Can you please attach a screenshot to show exactly where the problem is occurring? I'm having a hard time determining what the problem is just from the description.

Alternatively, I can provide you with a workaround. you can create a custom column to fix the Region column in Power Query using the following solution:

You can then delete the original Region column and use the newly created custom column as the Region.
Here is the M code in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsgvKilNT8xR0lHyzUxJyUlVcE0sLlGK1UGRckwrykxORBd1LS3KL0gFi4Z6o/Jd0ysLSrCY6ZtflJ+cnI9kZCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Region", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Country] = "portugal" then "Europe" else [Region])
in
    #"Added Custom"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dufoq3 · Accepted solution]

Hi @yemara, you can replace values with conditions (great article by @RickdeGroot you can find here)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvKilNT8xR0lHyzUxJyUlVcE0sLlGK1UGRckwrykxORBd1LS3KL0gFi4Z6o/Jd0ysLSrCY6ZtflJ+cnI9kZCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Region = _t]),
    Replace = Table.ReplaceValue(
    Source,
    each [Country], // y in (x,y,z)
    each "Europe",  // z in (x,y,z)
    (x,y,z)=> if Text.Contains(y, "portugal", Comparer.OrdinalIgnoreCase) then z else x, // x means do not change value (or change it to value itself)
    {"Region"} //here you can define in which columns should have beed replacement performed i.e. you can try change {"Region"} to {"Country", "Region"} or just to {"Conutry"}
)
in
    Replace

[v-junyant-msft · Accepted solution]

Hi @yemara ,

Can you please attach a screenshot to show exactly where the problem is occurring? I'm having a hard time determining what the problem is just from the description.

Alternatively, I can provide you with a workaround. you can create a custom column to fix the Region column in Power Query using the following solution:

You can then delete the original Region column and use the newly created custom column as the Region.
Here is the M code in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsgvKilNT8xR0lHyzUxJyUlVcE0sLlGK1UGRckwrykxORBd1LS3KL0gFi4Z6o/Jd0ysLSrCY6ZtflJ+cnI9kZCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Region", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Country] = "portugal" then "Europe" else [Region])
in
    #"Added Custom"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rubayatyasmin]

Hi, @yemara 

If you want to remove just the filter, click the "X" next to the filter step in the Applied Steps pane. This should remove only the filtering action and retain all other steps (including your replace value action).