Forum Discussion
Replace Values is not working
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.
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.
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
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.
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!