Forum Discussion

Agustin Louro's avatar
Agustin Louro
Copper Contributor
Jan 05, 2018

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!

  • Kashibaba's avatar
    Kashibaba
    Brass Contributor
    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 🙂
    • LDG_SKILLS's avatar
      LDG_SKILLS
      Copper Contributor
      The trim function does not do that.
      Trim will remove leading and trailing white spaces only.
      • Lorenzo's avatar
        Lorenzo
        Silver 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}})

Resources