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
PromotedEvery option in that record changes how the file is split. Microsoft's reference lists them (Csv.Document, Microsoft Learn):
| Option | What it controls |
|---|---|
Delimiter | The character between values. Default ",". |
Columns | How many columns to return. Fewer than the file has: extra columns are ignored. More: the extra ones are null. |
Encoding | The text encoding. Default 65001 (UTF-8). |
QuoteStyle | Whether quoted line breaks are part of the value (QuoteStyle.Csv) or end the row (QuoteStyle.None). |
CsvStyle | Whether 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
PromotedResult
| ID | Item | Note |
|---|---|---|
| 1 | Kite | Red, large |
| 2 | Reel | Ships in two parts Box 1 of 2 |
| 3 | Line | Spare |
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
FlattenedResult
| ID | Item | Note |
|---|---|---|
| 1 | Kite | Ships in two parts Box 1 of 2 |
| 2 | Reel | Plain |
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
PromotedResult
| ID | Item | Note | Column4 |
|---|---|---|---|
| 1 | Kite | Red | large |
| 2 | Reel | Plain | null |
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:
- Ask for the export again with text values quoted, or with a delimiter that never appears in the data.
- 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).
- 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
PromotedResult
| ID | Item | Note |
|---|---|---|
| 1 | Kite | Red, large |
| 2 | Reel | Plain |
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
PromotedResult
| ID | Item | Note |
|---|---|---|
| 1 | Kite | Says "best" seller |
| 2 | Reel | Plain |
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
PromotedResult
| ID;Item;Price | Column2 |
|---|---|
| 1;Kite;12 | 50 |
| 2;Reel;8 | 00 |
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
TypedResult
| ID | Item | Price |
|---|---|---|
| 1 | Kite | 12.5 |
| 2 | Reel | 8 |
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
PromotedResult
| ID | Item | Note |
|---|---|---|
| 1 | Kite | Red |
| 2 | Reel | Plain |
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
| Symptom | First thing to check |
|---|---|
| More rows than expected, text fragments in the first column | QuoteStyle or Line breaks setting |
An extra Column4 on some rows | An unquoted comma in a text value |
| One wide column with semicolons in it | Delimiter should be ; |
| Prices split into two columns | Comma delimiter on a decimal-comma file |
| A new column in the source never appears | Columns count in the Source step |
| Accented letters garbled | Encoding / File Origin |
| Everything wrong from one row onwards | A 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
- Csv.Document, Microsoft Learn (learn.microsoft.com)
- Text/CSV connector, Microsoft Learn (learn.microsoft.com)
- QuoteStyle reference (Microsoft Learn) (learn.microsoft.com)
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
- Case sensitivity in Power Query: the silent cause of missing matchesPower Query treats upper and lower case as different, so Ben@Shop.com won't match ben@shop.com. Fix merges, filters and groups with a key or a comparer.
- Power Query row count is wrong? How to find where rows go missingFind the Power Query step that loses rows: count rows after every step, list the dropped rows with an anti join, and fix the five usual causes.
- Why dates turn into numbers after Power Query loads to ExcelDates that load to Excel as 46296 are Excel serial numbers. Find the untyped column in Power Query, set a Date type, and stop the table losing its format.
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.