A Power BI import is more than opening a file. Encoding, delimiter, header promotion, type detection, culture, identifiers, file location, and refresh path can all change the result. Review those decisions before a visual makes the wrong data look convincing.
Power BI Desktop and the Power BI service can both receive CSV data, but they do not create the same operating workflow. Desktop gives you the Text/CSV connector and Power Query editor before the model. The service can create a semantic model or report from an uploaded local file or a file in OneDrive or SharePoint. A successful one-time upload is not the same as an updating connection.
Microsoft's current Power BI CSV service guidance says file location affects synchronization and documents a 1 GB service-import limit. It also says semantic models created with the legacy Excel/CSV import experience stopped refreshing after July 31, 2026 and stopped loading after August 31, 2026. If an older model is affected, migrate it instead of treating a newly cleaned CSV as the fix.
Decide who owns the source file, where the replacement file will live, whether its name and path stay stable, who may refresh it, and whether a gateway or cloud file route is appropriate. Only then prepare a repeatable handoff. Open the private Power BI CSV preparation desk.
Use one header row, one logical record per row, and the same number of fields throughout. Quote cells that contain commas, quotes, or line breaks and double an embedded quote. Remove decorative title rows, merged-cell exports, subtotal lines, repeated headers, and trailing notes before the import. A CSV can be syntactically valid while still mixing transaction rows with report furniture.
Microsoft's Text/CSV connector documentation exposes file origin, delimiter, and type-detection controls. It notes that UTF-8 is automatically inferred only when the file begins with a UTF-8 BOM. That is why this workspace adds a BOM to the normalized CSV by default. The generated query separately fixes encoding to code page 65001, so the intent remains readable even if somebody later changes a connector dialog.
Keep output headers nonblank and unique without relying on letter case. Do not silently remove a field just because it looks unused in a preview; downstream measures or relationships may depend on it. The local workspace preserves column order, row order, and cell text. Header edits change only the exported header row.
Power Query can base automatic type detection on the first 200 rows, on the entire data set, or turn detection off. The first option is fast, but a later word in an apparently numeric field can still fail. This preparation desk inspects every nonblank value before suggesting a strict type. Mixed shapes remain text until you review them.
Treat IDs, postal codes, account numbers, SKUs, phone numbers, and reference numbers as text unless arithmetic is genuinely meaningful. 00101 and 101 may identify different records even though they are the same number. Once a numeric type removes leading zeros, labels, joins, distinct counts, and exports can all change. The tool therefore gives identifier-shaped headers and digit strings with leading zeros a text suggestion.
Whole number, decimal, date, date/time, and logical choices should pass every nonblank row. Blanks are counted separately because Power Query can represent nulls, but a blank business key still matters. Select one business key when the file should contain one row per order, customer, ticket, or other entity. The local check lists every blank or repeated key row without exposing the raw key in its ledger.
The string 08/02/2026 can mean August 2 or 8 February. 1,234 can be a grouped integer, a decimal value, or a delimiter problem depending on the file and culture. Do not let a workstation locale make that choice silently. Prefer ISO YYYY-MM-DD for portable dates and a consistent decimal convention in the source whenever you control the export.
The workspace supports four explicit Power Query cultures: en-US, en-GB, de-DE, and fr-FR. Changing culture reruns every reviewed type check. Ambiguous slash dates remain text during suggestion because selecting a culture is a user decision, not a fact that can be recovered from the value alone. After choosing the correct culture, change the column to Date or Date/time and resolve every reported row.
Microsoft documents that Table.TransformColumnTypes accepts a list of column/type transformations and an optional culture. Carrying the reviewed culture in M makes the decision inspectable in source control and refresh troubleshooting.
A reviewed query should state the path, CSV parsing rules, header promotion, types, and culture. The generated starter uses File.Contents, then Csv.Document with comma delimiter, encoding 65001, and QuoteStyle.Csv. It promotes the first row and applies the complete ordered type list. The file path starts as a visible placeholder; replace it only after placing the normalized CSV where the intended refresh process can reach it.
Microsoft's Csv.Document reference documents delimiter, encoding, column, CSV-style, and quote-style options. The query intentionally omits a fixed column count: a lower fixed count can ignore later columns, while a higher count can create null columns. Header and schema drift still need a refresh-time check rather than silent truncation.
Paste the starter into a blank query's Advanced Editor or adapt the equivalent generated steps. It is not a credential, gateway, refresh schedule, semantic model, or publication script. Keep it beside the source contract so a future owner can see exactly why an ID is text or a date uses en-GB.
A local path in Desktop is useful for development, but the Power BI service cannot magically reach every laptop path. If a recurring process overwrites a file, keep its schema, path, filename, delimiter, and encoding stable. Decide whether the published model will use a gateway, OneDrive, SharePoint, or another supported source path, then test that exact route with the account that will own refresh.
Microsoft's OneDrive and SharePoint CSV refresh guidance describes synchronization that usually appears within about an hour and distinguishes it from scheduled semantic-model refresh. It also warns that using the Desktop Text/CSV connector against a locally synchronized OneDrive file creates a local reference unless a gateway is configured; the online file requires a different connection choice.
Do not promise a refresh merely because the first import worked. Replace the source with a second representative file, trigger or wait for the real refresh mechanism, inspect credentials and ownership, and verify that rows, types, relationships, and measures still behave. Document the rollback: preserve the last accepted source and the reviewed query before replacing either.
Start with exact row and column counts. Spot-check identifiers with leading zeros, the earliest and latest dates, decimal totals, blank rates, and several non-ASCII labels. If a business key should be unique, compare its distinct count with row count. A refresh can complete while still converting one column incorrectly or dropping rows upstream.
Then test the model: relationship direction, one-to-many cardinality, unknown members, measures against hand-calculated examples, date-table coverage, filters, and representative visuals. Compare a known total across the original export, normalized CSV, Power Query preview, model table, and final visual. Record where any difference is intentional.
Finally, test the next delivery, not only today's file. Add a later-row type edge case, a missing optional value, a non-ASCII label, and a new category in a disposable copy. The preparation report is evidence about one file; the M query is the repeatable decision; the refresh test proves whether the operating workflow survives change.
Review the delimiter and encoding, preserve identifier columns as text, choose a deliberate culture, validate every value against explicit Power Query types, check any business key, then import a representative normalized CSV before relying on refresh.
Microsoft documents that the Text/CSV connector automatically infers UTF-8 only when a UTF-8 BOM is present. The generated M query also declares encoding 65001, so both manual and scripted handoffs are explicit.
Values such as 00101 are identifiers, not quantities. Assigning a number type can remove meaningful leading zeros and change joins, labels, and exports, so identifier-shaped columns default to text.
No. Slash dates are culture-dependent, so they stay text until you choose a culture and deliberately select Date or Date/time. ISO dates can be suggested without making that hidden locale choice.
It reads an explicit local path with File.Contents, parses comma-delimited UTF-8 with CSV quote handling, promotes headers, and applies every reviewed column type using the selected culture.
No. It creates a normalized CSV, a Power Query M starter, and an aggregate readiness report. You still test and model the data in Power BI Desktop or the Power BI service.
You can select one business-key column to find blanks and repeated normalized values across the complete file. The tool does not infer database constraints, relationships, measures, or dimensions.
No. Parsing, type review, validation, preview, query generation, and downloads happen in the browser tab. A data handoff occurs only when you later import or connect from Power BI.