Forum Discussion

ALRUYOYO's avatar
ALRUYOYO
Icon for Advocate I rankAdvocate I
9 years ago

How to write quotation marks to get a single quotation mark when exported to csv?

Hey, I am struggling to find a solution maybe someone can help. In power BI I see the result I need where text is inside SINGLE quotation marks and separated by a comma, but when I export it ...
Need Help
Show and Tell
Tips and Tricks
Reid_Havens's avatar
Reid_Havens
Icon for Most Valuable Professional rankMost Valuable Professional
9 years ago

Hi there,

 

Quotes can definitely be tricky! In Power Query and DAX the escape character for a quotation mark inside a string is represented as ""(two quotation marks).

 

For example, a string containing a single quotation mark would be written as:

 

""""

 

If you were to do a concatenated column as you described above it would look like something below:

 

Calc Column = """" & Table1[Column1] & """,""" & Table1[Column2] & """,""" & Table1[Column3] & """,""" & Table1[Column4] & """,""" & Table1[Column5] & """"

 

Which would produce this result: ("Combining","Data","Can","Be","Fun")

 


There's also a great question & post about this topic here. Let me know if this helps!

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Reid_Havens,

     

    I think ALRUYOYO has that mastered.  The problem they are having is when using the Export to CSV feature on the visuals, they get all the extra """" characters.

     

    I had a look to see if there were some Escape characters or an ASCII function he could use to minimise the noise, but there doesn't seem to be one.  We have a CODE() function that will provide the ASCII value of a character, but nothing the otherway that jumps out at me.

    • Reid_Havens's avatar
      Reid_Havens
      Icon for Most Valuable Professional rankMost Valuable Professional
      9 years ago

      Ah good clarification, thanks Phil. The export to csv definitely adds an extra layer of logic that needs to be accounted for. It's a great question, I'll noodle on this a bit since now I'm curious if there's a way to do this without too much heavy lifting.

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        If the objective is to extract data out of Power BI, then here is one option

         

        http://powerbi-pro.com/export-data-to-sql/

         

        It uses an R Visual to export to an SQL DB, but you can probably modify it to write to a flat file as an option.  An R visual will export the data relevant to the currently selected filters too.