CSV export erronously adding a leading apostrophe to negative numbers - bug resurfaced and solved

[computasian]

I'm using PowerBI Version: 2.142.928.0 64-bit (April 2025)

I have 2 CSV's with identical columns, some of which contain numbers, some of them negative.

When I do an append query to concatenate the two CSV's and the columns containing negative numbers are type TEXT, I see negative numbers showing up in the CSV string with the dreaded leading single apostrophe as follows:

...true,false,,,"","","","",1/24/2024,'-5

...true,false,11/27/2023,'-3,12/19/2023,11,"",""

When I change the data type of those columns to INTEGER, I see well behaved CSV strings with no weird leading apostrophes:

...false,false,,,"",,"",,1/24/2024,-5

...true,false,11/27/2023,-3,12/19/2023,11,"",

Not sure if there's something to be done here.

I wanted to document the weirdness of why/how that weird leading apostrophe was showing up (an error that seems to have been prevalent a few years ago) and how I fixed it, to perhaps prevent future generations of PBI'ers from gnashing their teeth. 

[v-priyankata · Accepted solution]

Hi @computasian ,

Thank you so much for sharing this, This kind of post is incredibly valuable to the community. Definitely consider turning this into a blog post so others can benefit from your experience more easily

Power BI Community Blog - Microsoft Fabric Community

Please consider Accepting as solution to help the other members find it more quickly.

Thanks.