Power QueryExercises

CSV quirks in Power Query: line breaks, quotes and delimiters inside values

Troubleshooting · By Tahir Nas · Examples run in our simulator · Updated

10 min read

When a CSV import in Power Query has broken rows or values in the wrong columns, the cause is usually how quotes are read. With QuoteStyle.Csv, a value in double quotes can contain commas and line breaks and still stay in one cell; with QuoteStyle.None, every line break ends the row, even inside quotes, so one note with a line break splits a row in two. Check the Source step's options first, then the delimiter, the column count and the encoding.

How Power Query reads a CSV

A CSV import is a call to Csv.Document. The step the editor generates looks something like this:

let
    Source = Csv.Document(File.Contents("C:\Data\orders.csv"), [Delimiter = ",", Columns = 3, Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
in
    Promoted
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

Every option in that record changes how the file is split. Microsoft's reference lists them (Csv.Document, Microsoft Learn):

OptionWhat it controls
DelimiterThe character between values. Default ",".
ColumnsHow many columns to return. Fewer than the file has: extra columns are ignored. More: the extra ones are null.
EncodingThe text encoding. Default 65001 (UTF-8).
QuoteStyleWhether quoted line breaks are part of the value (QuoteStyle.Csv) or end the row (QuoteStyle.None).
CsvStyleWhether quotes count only straight after a delimiter (the default) or anywhere in a field.

Csv.Document also accepts text, which is why every example below runs in the browser with the file contents written inline. The lesson Import CSV files: delimiters, encodings and headers covers the basics of the import dialog. If the file holds codes such as account numbers, set those columns to Text so they keep their leading zeros.

Line breaks inside a value

Free-text fields such as notes, addresses and descriptions often contain line breaks. A well-formed CSV wraps those values in double quotes. In this file the second order's note runs over two lines, and the first order's note contains a comma:

let
    Csv = Text.Combine({"ID,Item,Note", "1,Kite,""Red, large""", "2,Reel,""Ships in two parts#(lf)Box 1 of 2""", "3,Line,Spare"}, "#(cr)#(lf)"),
    Rows = Csv.Document(Csv, [Delimiter = ",", QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Rows)
in
    Promoted

Result

IDItemNote
1KiteRed, large
2ReelShips in two parts Box 1 of 2
3LineSpare

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

With QuoteStyle.Csv, quoted line breaks are treated as part of the data, not as the end of the row (Csv.Document, Microsoft Learn). The result has three rows and three columns. Order 1's note is "Red, large", and order 2's note holds both lines in one cell.

With QuoteStyle.None, Microsoft's reference says all line breaks end the current row, even when they occur inside a quoted value. For this file that means order 2 stops at "Ships in two parts", and "Box 1 of 2" becomes a row of its own, with nothing in the other columns. The table gains a row, the ID column holds a fragment of text, and every count and lookup after it is off by one. The post on finding where rows go missing shows how to count rows step by step when totals don't add up.

In the editor you don't need to type the option. Microsoft's connector guide describes a Line breaks dropdown on the Source step's settings: Ignore quoted line breaks keeps the quoted text in one cell, and Apply all line breaks starts a new row at every line break (Text/CSV connector, Microsoft Learn). If your rows split at notes or addresses, open the Source step's gear icon and check that setting first.

Keep the line break, or flatten it?

Once the value is in one cell, you can decide whether the line break belongs there. Excel shows it inside the cell, which is fine for addresses but awkward for a report column. To flatten it, replace the carriage return and line feed characters:

let
    Csv = Text.Combine({"ID,Item,Note", "1,Kite,""Ships in two parts#(lf)Box 1 of 2""", "2,Reel,Plain"}, "#(cr)#(lf)"),
    Promoted = Table.PromoteHeaders(Csv.Document(Csv, [Delimiter = ",", QuoteStyle = QuoteStyle.Csv])),
    Flattened = Table.TransformColumns(Promoted, {{"Note", each Text.Replace(Text.Replace(_, "#(cr)", ""), "#(lf)", " "), type text}})
in
    Flattened

Result

IDItemNote
1KiteShips in two parts Box 1 of 2
2ReelPlain

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Order 1's note becomes "Ships in two parts Box 1 of 2" on one line. #(cr) and #(lf) are M's escapes for the carriage return and line feed characters; the lesson on identifiers, quoted names, escapes and comments lists the others.

Commas and quotes inside a value

A comma in a value only stays in one cell if the value is quoted. When the system that produced the file didn't quote it, no option can tell a real delimiter from a comma in the text:

let
    Csv = Text.Combine({"ID,Item,Note", "1,Kite,Red, large", "2,Reel,Plain"}, "#(cr)#(lf)"),
    Promoted = Table.PromoteHeaders(Csv.Document(Csv, [Delimiter = ",", QuoteStyle = QuoteStyle.Csv]))
in
    Promoted

Result

IDItemNoteColumn4
1KiteRed large
2ReelPlainnull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Row 1 gets an extra Column4 holding " large", and Note holds only "Red". There are three ways out, best first:

  1. Ask for the export again with text values quoted, or with a delimiter that never appears in the data.
  2. If you can choose the delimiter, a pipe or a tab is far less likely to appear in text than a comma (see the example after this list).
  3. If neither is possible and only one column can contain commas, rebuild that column: import each line as a single column, split on the first few delimiters only, and keep the rest of the line as the free-text value. The exercise import a CSV whose descriptions contain commas practises this.

The same data with a pipe between values needs no quotes at all:

let
    Csv = Text.Combine({"ID|Item|Note", "1|Kite|Red, large", "2|Reel|Plain"}, "#(cr)#(lf)"),
    Promoted = Table.PromoteHeaders(Csv.Document(Csv, [Delimiter = "|"]))
in
    Promoted

Result

IDItemNote
1KiteRed, large
2ReelPlain

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Three columns, and order 1's note is "Red, large".

Quotes inside a quoted value are written twice. With QuoteStyle.Csv, nested quotes are indicated by two quote characters, as the QuoteStyle reference (Microsoft Learn) puts it:

let
    Csv = Text.Combine({"ID,Item,Note", "1,Kite,""Says """"best"""" seller""", "2,Reel,Plain"}, "#(cr)#(lf)"),
    Promoted = Table.PromoteHeaders(Csv.Document(Csv, [Delimiter = ",", QuoteStyle = QuoteStyle.Csv]))
in
    Promoted

Result

IDItemNote
1KiteSays "best" seller
2ReelPlain

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The note for order 1 reads Says "best" seller. (In the M text, every quote is doubled once more because the CSV itself is written inside an M string.) A quote that isn't doubled, or a quoted value that is never closed, leaves the parser unsure where the value ends, and the rows after it can shift. When a file goes wrong from one row onwards, look for a stray quote in the row just before.

The wrong delimiter

Files exported with European regional settings often use a semicolon between values, because the comma is the decimal separator. Reading one with a comma delimiter splits every price at its decimal comma:

let
    Csv = Text.Combine({"ID;Item;Price", "1;Kite;12,50", "2;Reel;8,00"}, "#(cr)#(lf)"),
    Promoted = Table.PromoteHeaders(Csv.Document(Csv, [Delimiter = ","]))
in
    Promoted

Result

ID;Item;PriceColumn2
1;Kite;1250
2;Reel;800

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The header becomes one column called ID;Item;Price, with "1;Kite;12" in it and "50" pushed into Column2. Set the semicolon delimiter, then convert the price with a locale that reads the decimal comma:

let
    Csv = Text.Combine({"ID;Item;Price", "1;Kite;12,50", "2;Reel;8,00"}, "#(cr)#(lf)"),
    Promoted = Table.PromoteHeaders(Csv.Document(Csv, [Delimiter = ";"])),
    Typed = Table.TransformColumnTypes(Promoted, {{"Price", type number}}, "de-DE")
in
    Typed

Result

IDItemPrice
1Kite12.5
2Reel8

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The prices are 12.5 and 8.

The connector guide says Power Query tries to work out the delimiter of a text file, and offers colon, comma, equals sign, semicolon, space, tab, a custom string or fixed width. Detection can guess wrong, so check the delimiter in the Source step rather than assuming it. Bank exports are a common source of semicolons and decimal commas; turning a bank statement CSV into ledger rows works through one. The guide Change type using locale covers the type step, and there's one exercise for each half: import a semicolon-separated stock file and read amounts written with a decimal comma.

The fixed column count

The Columns option is easy to miss. When it's set, Csv.Document returns exactly that many columns. Fewer columns than the file has means the extra ones are ignored:

let
    Csv = Text.Combine({"ID,Item,Note", "1,Kite,Red, large", "2,Reel,Plain"}, "#(cr)#(lf)"),
    Promoted = Table.PromoteHeaders(Csv.Document(Csv, [Delimiter = ",", Columns = 3]))
in
    Promoted

Result

IDItemNote
1KiteRed
2ReelPlain

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The stray " large" from the unquoted comma is gone without any error. A real fourth column added to the export would vanish the same way. Microsoft's connector guide warns about this in Power BI Desktop: the import generates a Columns step based on the column count at the time, and a later refresh doesn't include any columns beyond that count. If the source might gain columns, remove the Columns field from the Source step so the count comes from the file. For columns that are renamed rather than added, see handling changing column headers. The lesson Dynamic steps that survive new columns covers the steps that follow.

Encoding: accents that turn into symbols

If names such as "Zoë" or "Müller" arrive as odd symbols, the file was read with the wrong encoding. The connector guide notes that the character set isn't inferred, and UTF-8 is only inferred when the file starts with a UTF-8 byte order mark. Choose the right option in the File Origin dropdown, which sets Encoding in the Source step (65001 is UTF-8). The glossary entry on encoding explains the term.

A checklist for a broken CSV import

SymptomFirst thing to check
More rows than expected, text fragments in the first columnQuoteStyle or Line breaks setting
An extra Column4 on some rowsAn unquoted comma in a text value
One wide column with semicolons in itDelimiter should be ;
Prices split into two columnsComma delimiter on a decimal-comma file
A new column in the source never appearsColumns count in the Source step
Accented letters garbledEncoding / File Origin
Everything wrong from one row onwardsA stray or unclosed quote just before

Frequently asked questions

Why does Excel open the file correctly when Power Query doesn't?

They are different readers with different settings. Compare the delimiter, encoding and line break handling in the Source step with what the file actually contains, and set them explicitly rather than relying on detection.

Can I fix a broken row after the import?

Sometimes, but it's fragile. Once a line break has split a row, the pieces have to be found and stitched back together, and the rule for doing that depends on the data. Fix the Source step options first; only rebuild rows when the file itself is malformed.

Does the same apply when combining a folder of CSV files?

Yes. The sample file query and the function generated from it use the same Csv.Document options for every file, so a QuoteStyle or Columns setting in the sample applies to the whole folder. The lesson Combine files from a folder shows where that step lives.

To practise on a realistic export, the Power Query Exercises download European invoices (semicolon CSV) has semicolons between fields, day.month.year dates and decimal commas, with a tested query to compare against yours.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.