How to Import a CSV into Power BI

1. Import it

Before the explanation: a CSV is a plain text file with no type information in it at all. So where do you think Power BI gets the idea that one column is a date and another is a number?

On the Home ribbon, choose Get data, then Text/CSV. Pick your file and press Open. A preview window appears showing the first rows and three dropdowns along the top.

That preview window is the answer to the question. Nothing in the file says "this column holds dates". Power BI reads a sample of the rows and guesses, then writes the guess down as a permanent step. The dropdowns are how you influence the guess, and they are much easier to set now than to unpick later.

2. The three dropdowns, one at a time

File Origin is the character set. This one has a specific trap: the character set is not inferred, and UTF-8 is only detected automatically if the file starts with a special marker called a byte order mark. Plenty of files do not carry that marker. If your preview shows é where an é should be, this dropdown is the fix, and setting it to a UTF-8 option will usually correct the whole file at once. Character encoding for people who never heard the phrase is the fuller explanation.

Delimiter is what separates the values. Power BI offers colon, comma, equals sign, semicolon, space, tab, a custom delimiter of any text you like, and fixed width for files that use a set number of characters per field. It guesses, and it guesses well, but check the preview: if you see one column containing everything, the delimiter is wrong.

Data Type Detection is the important one. Three choices: based on the first 200 rows, based on the entire dataset, or off, which loads every column as text. The default samples the top of the file, which is fast and is exactly the behaviour in the picture above.

Choose Based on entire dataset whenever you can. Microsoft's own warning is that this makes the initial load slower, which is true and is a fair trade: a slower import once beats a column typed wrongly forever. Their documentation says it plainly, that inference can be incorrect and you should double check before loading.

Choose Do not detect data types when the file is genuinely messy and you would rather set every type yourself than untangle a wrong guess. Everything arrives as text, and you convert deliberately.

3. Transform Data, not Load

The preview window offers Load and Transform Data. Load drops the table straight into your model. Transform Data opens the Power Query editor first.

Choose Transform Data every time, even when the file looks perfect. The editor is where every action you take is recorded as a step in the Applied Steps list on the right, and that list replays on every future refresh. Work done there is permanent; work done later in the report is not.

Say what that means for a column you fix by hand after loading, before reading on. It means you will fix it by hand again after the next refresh, and so will whoever inherits the file. The editor is not an advanced option, it is where the durable version of the work lives.

4. Fix the types once, properly

In the editor, each column header carries a small symbol saying what Power BI thinks it holds: ABC for text, 123 for whole numbers, 1.2 for decimals, a calendar for dates. Read that row of symbols before anything else. It is a two-second check that prevents most downstream confusion.

Three fixes worth making immediately when they apply.

A number that arrived as text. Usually a thousands separator, a currency symbol, or a stray "N/A" in one row. Click the symbol and set the correct type, and if it errors, the offending value is now easy to find by filtering.

A date that arrived as text, or is wrong by a month. Day and month have been swapped. Use Change Type, then Using Locale, and pick the locale the file was written in. That is the difference between telling Power BI what the column is and telling it what language the column was written in, and for dates you need the second one.

A code that has lost its leading zeros. Postcodes, account numbers and product codes typed as numbers lose the zeros at the front. Set the type to Text, and if the zeros are already gone, they must be recovered in the source file rather than in Power BI. The leading zeros fix covers the source-side version.

Then press Close & Apply. The steps are saved, and every refresh from now on replays them in order.

5. The columns=x trap

This one is documented, quiet, and expensive, and almost nobody knows about it until it bites.

When you import a CSV, Power BI writes a step recording the number of columns the file had at that moment, as columns=x. If somebody later adds a column to the source file and you refresh, the new column does not appear. The refresh succeeds, the numbers all update, and the extra field is silently ignored because the step says the file has x columns.

There is no error and no warning. A monthly report can run for a year without the field somebody added in March, and the person who eventually notices is usually the one asking why two systems disagree.

The fix is simple once you know: open the Applied Steps, edit the Source step, and let it pick up the new column count. The habit is better than the fix. Whenever the source file changes shape, check the import step rather than trusting a successful refresh, because success here means the steps ran, not that the data is complete.

Now picture a report you refresh regularly. If a column were added to its source tomorrow, how would you find out? For most reports the honest answer is that you would not, which is exactly why this trap is worth remembering.

6. When the file is not really a CSV

Power BI treats a .csv as structured with a comma delimiter, and for a plain text file it tries to work out whether there is a delimiter at all. Usually it is right. Two cases where it is not, both documented.

Structured text that is not a CSV. A tab-separated file saved as .txt works fine, because Power BI detects the tab and treats it as a delimited file. The extension does not decide this; the content does.

Unstructured text read as a table. Occasionally a document with similar numbers of commas across its paragraphs gets interpreted as a CSV. The fix is in the editor: edit the Source step and change Open File As from CSV to Text.

There is also a Line breaks setting on that same edited Source dialog, deciding whether line breaks inside quoted values are honoured or ignored. If a single record has split itself across two rows, or two records have merged into one, that setting is why.

What goes wrong, and the fix

Six that account for most of it.

Accented characters look like é. File Origin. The character set is not inferred unless the file carries a byte order mark. Set it to UTF-8.

Everything landed in one column. Wrong delimiter. Change it in the dropdown and watch the preview split.

A number column will not sum. It is typed as text, usually because of a separator, a symbol, or one non-numeric value. Set the type in the editor, then filter to find the offending row.

Dates are out by a month. Day and month swapped. Change Type, Using Locale, and choose the locale the file was written in.

A new source column never appears. The columns=x step. Edit the Source step.

Refresh breaks on a row that was always fine. The type was inferred from the first 200 rows and a later row does not fit. Re-import with detection based on the entire dataset.