Forum Discussion
Agustin Louro
Jan 05, 2018Copper Contributor
Power Query adds an apostrophe to cells queried from an Excel table
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!
- KashibabaBrass Contributorin 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 🙂
- LDG_SKILLSCopper ContributorThe trim function does not do that.
Trim will remove leading and trailing white spaces only.- LorenzoSilver Contributor
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}})
- Guy_MartyCopper ContributorI'm annoyed with that trouble.