Forum Discussion

yemara's avatar
yemara
New Member
2 years ago
Solved

Help with a very Basic changing value

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    dufoq3
    2 years ago

    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

     

     

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    dufoq3
    Community Champion
    2 years ago

    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