Power BI CSV: Import, Refresh, and Avoid Data Type Traps

CSV Is Still Power BI’s Everyday Fuel

Despite warehouses and APIs, analysts in the US, UK, Australia, Canada, and Germany still drop CSV extracts into Power BI Desktop and the service. Power Query’s Text/CSV connector is excellent — when delimiter, encoding, and locale are explicit. When they are not, dates swap, numbers break, and scheduled refresh fails on a file that “looked fine” in Excel.

Step-by-Step: Get Data → Text/CSV

Do this instead of double-clicking CSV in Excel and copy-pasting.

• Power BI Desktop → Get data → Text/CSV.
• Select the file; wait for the preview.
• Set Delimiter explicitly (Comma, Semicolon, Tab, or Custom).
• Set Data type detection thoughtfully — or detect after you set Locale.
• Open Transform Data (Power Query) before Load when anything looks wrong.
• Promote headers if row 1 is column names.
• Rename steps so future you knows why locale was forced.
• Close & Apply; build relationships only after keys look clean.

Delimiter and European Files

German and many EU extracts are semicolon CSV.

• If everything lands in one column, the delimiter is wrong — not “Power BI hates CSV.”
• Pre-convert with CSV to Delimited when multiple teams must standardize on comma UTF-8.

Locale: The Hidden Date and Number Switch

Power Query locale controls how text becomes dates and decimals.

• US locale: 03/05/2026 → March 5; decimals use period.
• UK/AU locale: 03/05/2026 → 3 May; decimals use period (usually).
• DE locale: 03.05.2026 and 1.234,56 style numbers.
• Set locale on the CSV connector or change type with culture — do not rely on the machine default alone for shared PBIX files.

order_id,order_date,amount,country
A-1001,2026-03-05,149.00,US
A-1002,2026-05-13,89.50,GB

Encoding and the Mystery Character

UTF-8 vs Windows-1252 shows up in customer names first.

• Set File Origin / encoding to UTF-8 when you see ü instead of ü.
• UTF-8 with BOM is usually fine; document it for mixed Linux/Windows pipelines.
• Re-save source CSV as UTF-8 using a browser editor if the source system cannot.

Data Types That Break Refresh

Scheduled refresh fails when a later file violates an assumed type.

• Do not leave “Any” on key columns you will relate.
• Keep order IDs and ZIP/postcodes as text.
• Amounts: Decimal number; watch European comma decimals under wrong locale.
• True/false columns: unify YES/No/1/0 before changing type.
• Empty strings vs null — replace error rows deliberately.

Large CSV Files and Performance

Desktop can open more than Excel’s row comfort zone — still be intentional.

• Filter early in Power Query to drop unused columns and rows.
• Prefer splitting huge extracts by month upstream.
• For recurring corporate data, graduate from CSV to a database or dataflow when refresh SLAs matter.
• Disable load on staging queries you only need for transforms.

Power BI Service and Gateway Paths

Local CSV paths do not magically exist in the cloud.

• Publish refresh needs OneDrive/SharePoint, a data gateway to an on-prem file share, or a move to cloud storage.
• Keep filenames stable; parameterize folder paths when months change.
• Test refresh with a deliberately “ugly” second file (extra column, EU dates) before go-live.

Common Mistakes

Report-breaking habits.

• Using Excel as a silent middle step that rewrites dates.
• Assuming Desktop locale equals the colleague’s machine in another country.
• Combining US and UK CSV months with Append without normalizing dates first.
• Detecting types on a 100-row sample that lacks day-13 dates or EU decimals.
• Renaming source columns in the file without updating the query.

Best Practices

Make CSV sources contract-driven.

• Document delimiter, encoding, and locale in the dataset README.
• Standardize interchange files to comma UTF-8 + ISO dates when you can.
• Keep raw CSV immutable; transform only in Power Query.
• Add a QA page: row counts, min/max dates, sum of amounts vs source.
• Clean vendor files in Convert CSV Online before first load when needed.

Why Use Convert CSV Online?

Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Normalize delimiters and preview columns in the Online CSV Editor before Power BI, convert JSON extracts to CSV, and deliver UTF-8 files that behave the same for US, UK, AU, CA, and DE teammates. Works on Windows, macOS, and Linux browsers.

Conclusion

Power BI CSV imports succeed when delimiter, locale, encoding, and types are chosen on purpose. Treat every extract as a contract — especially across US and European date/number formats — and refresh stops being a monthly surprise.