Power Query adds an apostrophe to cells queried from an Excel table

Copper Contributor

Excel 2010, Power Query version 2.48.4792.941 32-bit.


I have a table (call it Table_Source) in an Excel file (call it source.xlsm) that I query from another Excel file (call it destination.xlsm). The source table contains multiple columns, some formatted as text, some formatted as general.


In destination.xlsm, I use Power Query to query Table_Source. Some of the queried columns are written into destination.xlsm with a leading apostrophe in each cell, even though the columns are formatted as text. This wreaks havoc with some VBA code.


How do I get Power Query to not append the leading apostrophe?


Thank you!

4 Replies
I'm annoyed with that trouble.
in power query editor select column have these values then right click on column header and you will see several options . Go to Transform>Trim and there you go trim the column to get rid of these apostrophes :)
The trim function does not do that.
Trim will remove leading and trailing white spaces only.

Trim will remove leading and trailing white spaces only 

You can alter the generated step to remove leading and trailing apostrophe(s) (or other char)


Ex. step to Trim leading & trailing space(s):


Edit that step in the formula bar with:


= Table.TransformColumns(#"Changed Type",{{"A", each Text.Trim(_, "'"), type text}})