topic Re: Assemble equal data after using the function Table.ReplaceValue in DAX Commands and Tips

Assemble equal data after using the function Table.ReplaceValue

Anonymous — Thu, 06 Jan 2022 14:23:59 GMT

Hello,

In my dashboard I wrote a formula in Power Query to replace values according to two conditions like the example below:

if it is an orange in France, then replace the orange by an apple.

The formula is :

Table.ReplaceValue(#"Added Custom 1", each [Fruits], each if [Country] = "France" and [Fruits] = "Orange" then "Apple" else [Fruits], Replacer.ReplaceText,{"Fruits"})

However when I then create my chart and a drop down list of fruits, "Apple" appears twice instead of once. I checked, "Apple" is spelled the same way and there is no extra space.

Do you know how to solve this problem?

Thank you very much for your help,

Léonie

Re: Assemble equal data after using the function Table.ReplaceValue

amitchandak — Thu, 06 Jan 2022 14:31:31 GMT

Anonymous , There is a clean option in the transform tab in power query, use that on this field and check

Re: Assemble equal data after using the function Table.ReplaceValue

Anonymous — Fri, 07 Jan 2022 14:23:22 GMT

Hello amitchandak, I tried this solution by clicking on "Transform > Clean" but it didn't work. Do you have another idea ?

Re: Assemble equal data after using the function Table.ReplaceValue

amitchandak — Fri, 07 Jan 2022 15:00:17 GMT

Anonymous ,
Can you a sample pbix after removing sensitive data.

Re: Assemble equal data after using the function Table.ReplaceValue

Anonymous — Fri, 07 Jan 2022 16:03:00 GMT

Unfortunaltly I can't, all data are sensitive

Re: Assemble equal data after using the function Table.ReplaceValue

bcdobbs — Sun, 09 Jan 2022 08:23:27 GMT

Have you tried using trim as well as clean?

Failing that add a new step at end of power query to Replace "Apple" by "Test". Identify any rows that still have something that looks like Apple left which might give a clue.

Re: Assemble equal data after using the function Table.ReplaceValue

Anonymous — Tue, 11 Jan 2022 06:55:08 GMT

Hi Anonymous ,

In addition to bcdobbs 's reply, please try Trim function in Power Query.

Here I have a test by add space front or behind "Apple". We can see there are three Apples which look like the same in Filter.

I find Clean function couldn't remove them. Try trim, it will work.

Or find all different "Apples" by filter , copy them and compare them to find the difference.

For reference: Trim Vs Clean in Power BI and Power Query

Best Regards,
Rico Zhou

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