Replace Values is not working

[Adamp1916]

I have a column that contains multiple different number codes which need to be replaced with custom entries.  For example 01234 gets replaced to A, 01235 gets replaced to B, etc.

There are about 60 different number codes that are replacing just fine, but there is one value that will NOT replace no matter what I do with it.

The number starts with a zero, and there also seems to be an apostrophe in the source data for all of these... not sure if that has something to do with it...  When I enter the value to replace, the string automatically deletes the leading zero.  I thought that might be the problem, but entering it manually into the string does not work.  You also cannot just enter an apostrophe into the string because that gives you a syntax error.

I've tried re-formatting the column as text or as a number or as a value first, replacing the apostrophe with nothing... none of this works.

I can't post screenshots of the data since there's proprietary information.  Why won't it just replace the value like it does for the 60 other values I've entered?

[Adamp1916]

Thanks..

I understand it's an exact match.  That's why it's frustrating when the replacement isn't working when it's matched exactly.  I tried formatting as text already, and I am very sure that there are not any hidden characters.  Just to be sure, I changed the column type to text, cleaned and trimmed it, and it still did not work.  The leading zero is in the string to be replaced.  It just simply will not replace this one value when it works completely fine for over 60 others, some of which have leading zeros.

[srlabhe]

How about adding the data in PBIX only and create separate table with Original Value and Replaced Value and then join it with original table ?

[Adamp1916]

This would likely work.  

Some of the values in the column also contain alpha characters, so I can't just change the type to "Whole Number" and just remove all leading zeros so the replacement string doesn't mess up.  I don't particularly care to include those, so I think I may just end up creating a custom column that only keeps numeric characters and then format as whole number.  That would remove all leading zeros and then the replacement should work since it wouldn't have to consider them anymore.

What's absurdly frustrating about this approach is now I need to adjust all of the replacement steps to run on the new column.

This all seems like an extremely convoluted way to just replace what I enter how I enter it, which excel does just fine in a matter of seconds.

Some of the Power BI functionality is really just odd...  Extremely basic and simple tasks should be just that... 

[srlabhe]

If you have the list of original values with its replacebale values in excel. Then just copy that -> goto Add Data and paste there.You rename the column headers as per you rneed

[Adamp1916]

The original values come from a sharepoint export.  Then I have a vba macro that runs to replace the values with what I need.  I don't want to have to export the data, then run the macro, then refresh power BI every day.  I just want power BI to replace the values.

I agree that using Excel is much easier.

[v-aatheeque]

Hi @Adamp1916 

In this scenario we suggest to provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[Adamp1916 · Accepted solution]

I found the problem.  It's the leading zeros.  Power BI can't handle them with Replace Values because it automatically removes them in the M string.  Even when you manually put them back in, it still doesn't correctly handle them.

I ended up just making a new column and doing this in Power Query to remove the non-numeric characters.

Text.Remove(
      [DT Number],      {
        Character.FromNumber(32) .. Character.FromNumber(47),
        Character.FromNumber(58) .. Character.FromNumber(255)
      }
    )

where [DT Number] is the original column.  Then I formatted the column as whole number to just remove the leading zeros from the values being replaced.  But, since Replace Values can't be entered as a non-numeric value when the column is formatted as whole number, you need to reformat again back to text.  Then I used the new column to replace all the values and updated the reference in each visual to the new column.

This solution works, but it is still absolutely ridiculous that you can't just replace a value with a leading zero directly.  Excel has been able to do that with no issues for literal decades.

[v-aatheeque]

Hi @Adamp1916 

Thank you for sharing the detailed steps you took and the workaround you implemented. You are absolutely right that Power BI automatically interprets numbers in text columns when operations like Replace Values are applied, which causes leading zeros to be dropped.

This is expected behaviour since Power Query attempts to data types where possible, unlike Excel which treats text replacement more literally.

If you feel your workaround fully resolves the issue, it would be very helpful if you could mark the response as Accepted Solution. This makes it easier for other community members facing similar challenges to find the right answer quickly.

We really appreciate your contribution to the community!

[rohit1991]

Hi @Adamp1916 

Replace Values is exact-match. That stubborn code almost always has a hidden character. The apostrophe you see is just Excel’s “stored as text” marker, not the real value. In Power Query do this:

1. Change the column type to Text.

2. Transform > Format > Clean, then Trim. Try Replace Values again.

3. If it still won’t match, normalize first: add a custom column
   NormalizedCode = Text.PadStart(Text.Select([Code], {"0".."9"}), 5, "0")
   Then run Replace Values on NormalizedCode.
   This fixes leading-zero issues and invisible characters, so the replace works.