PowerQuery - Split a column of text with extra "

[potap]

Hi!

I have a question about some tricky csv files... Let's say that all my files have extra " and have no header. I want to combine them but I can't find a way to make it work correctly in PowerQuery. It doesn't split the string because of the " at the beginning and at the end. Excel has the Text Qualifier option in the Convert Text to Columns Wizard and with this, it works fine. I don't want to convert the files in Excel and then combine them in PowerQuery.


Here is what I am talking about :
"6/20/16,""ExpanseX"",110.95"
"6/20/16,""ExpanseY"",10.65"
"6/20/16,""ExpanseZ"",1000.00"


Let's say the header should be Date, Description, Amount

Thanks for the help!!!

 

[Ombir]

Please share the link to download your sample csv file.

 

[potap]

Link

 

[Ombir]

Type Value to find as " and left Replace as blank in Replace values in Get Transform tab of Power Query and then use Split column by delimiter comma.