How to make Power Query load CSV files faster

There’s a small setting in Power BI and Excel that can make your data load much faster. Most people never touch it, but it can save you a lot of waiting time.
The trick is simple: turn off automatic type detection and decide the column types yourself at the end.

When Power Query imports a file, it tries to guess what each column is. It scans the data a few times, checking if it’s a number, a date or text. That sounds helpful, but it takes time. And sometimes it gets it wrong. A code like “0123” suddenly becomes “123” or a date gets mixed up because of a different format.

If you load everything as text first, you skip that extra scanning. You also stay in full control of what happens later.

Here’s what to do.
When you import a CSV, remove the step called Changed Type that Power Query adds automatically.
Keep all columns as text while you clean and transform your data.
Then, right before the end, add one step where you set the right types for each column.
That’s it. Your file will load faster and you’ll know exactly what Power Query is doing.

If you want to stop Power Query from guessing types automatically, you can turn it off completely.
In Power BI Desktop, go to
File → Options and settings → Options → Current File → Data Load
and remove the check next to
“Automatically detect column types and headers for unstructured sources.”

Once you’ve done that, every new file will just come in as text until you tell Power Query what to do with it.

I like to finish my queries with a small “type map” step that sets all types in one place. It keeps things tidy and easy to read later.

let
    Source = PreviousStep,
    TypeMap = [
        OrderID = Int64.Type,
        OrderDate = type date,
        Amount = type number,
        Customer = type text
    ],
    Final = Table.TransformColumnTypes(Source, Record.ToList(
        Record.TransformFields(TypeMap, (n,t)=>{n,t})
    ))
in
    Final

A few quick tips from experience: keep IDs, postcodes and product numbers as text, even if they look like numbers. Convert dates at the very end to avoid strange results. And if you combine several CSV files, set the types after combining so everything stays consistent.

It’s a small habit, but it makes a big difference. Files load faster, refreshes are smoother, and you avoid those little surprises that cost time later.

Tags: power bi Power Query