handel comma while importing csv file in power BI

[anmolmalviya05]

Hi Everyone, I'm using a CSV file as a data source for one of my report.

I have a adress column which contain data like ( city name state name) but in some rows the data is (city name, state name)

Due to the comma in between it's breaking the data and the data after the comma is moving into other columns ( One column ahead, i.e state name will move to the next column for that row)

How to deal this in power BI

[anmolmalviya05 · Accepted solution]

Hi Everyone, I resolved the issue using below approach:

1. Import the CSV File Using "Extract Table Using Examples":

   • While importing the file, use the "Extract table using examples" option.
   • Retain the initial columns as they are until the problematic column. Combine all remaining columns into a single column for the initial step.

2. Load Data into Power Query Editor:

   • Click OK and load the data into the Power Query editor.

3. Split the Problematic Column:

   • Select the combined column (e.g., Column 4).
   • Go to the Transform tab and choose Split Column by Delimiter.
   • Use a comma as the delimiter and specify the quote character to correctly handle values enclosed in double quotes.
   • Click OK to apply the changes.

This approach ensures that commas within quoted fields are treated correctly, preserving data integrity.

[Anonymous]

Hi @anmolmalviya05 ,

I create a .csv file and import it into Power BI desktop.

Then I go to Power Query and I use Split Column by three times.

Finally you can remove the columns you don't need.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anmolmalviya05]

Hi @Anonymous, Thanks for your response

I tried this but it's not working.

[Expiscornovus]

Hi @anmolmalviya05,

Normally that value should have double quotes around it and Power BI import should be able to handle that.

Below is an example to demonstrate.

Sample table in Excel

Saved as CSV UTF-8

When you open the save file in notepad you will see the value has quotes around it

If that is not the case you can use PowerShell to convert the existing file in the correct format:

import-csv ImportCities.csv | export-csv Converted_ImportCities.csv  -NoTypeInformation -Encoding UTF8

https://stackoverflow.com/questions/25237847/adding-double-quote-delimiters-into-csv-file

[anmolmalviya05]

Hi @Expiscornovus , Thanks for your response

I tried this but it's not working.

[Expiscornovus]

Does your original source file use quotes, have you double checked this?