Connecting to Excel and CSV Files — The Practical, Slightly Theatrical Guide

You already know the Power BI stage: datasets, reports, dashboards. Now let’s invite Excel and CSV to the party — but politely. They can be messy guests if you don’t set rules.

This lesson builds on what you learned about Import vs DirectQuery vs Live Connection and the core Power BI concepts. Quick reminder: Excel and CSV files are usually imported into Power BI (so think snapshots of data), not queried live like a SQL database. Keep that in your back pocket while we proceed.

Why this matters (and why you’ll thank me later)

• Most analysts start with Excel or CSVs. If you can’t connect to them cleanly, nothing else matters.
• Small mistakes at the import step compound. Bad headers, wrong data types, and merged cells will haunt your visuals.
• Refresh behavior is different. If your file lives in OneDrive or SharePoint Online, refresh in Power BI Service is easy. If it lives on your laptop, you’ll need a gateway.

Imagine your data as a date: CSV shows up in sweatpants (fast, messy), Excel arrives in a suit with a garden of merged cells hiding secrets. We’ll get both presentable.

Quick step-by-step: Connect to a single Excel or CSV file

1. Open Power BI Desktop. Click Get data -> Excel or Text/CSV.
2. Browse and select the file. Click Open.
3. In the Navigator, preview available sheets, tables, or the CSV preview. Select what you want.
4. Choose Load (if you trust it) or Transform Data to open Power Query and clean first.
5. In Power Query: fix headers, set data types, remove blank rows, rename columns, create calculated columns, etc.
6. Click Close & Apply to bring the cleaned table into the data model.

Pro tip: Most folks should click Transform Data. You’ll save ten times the headache.

Excel vs CSV — quick comparison

Bottom line: use Excel Tables (Ctrl+T) or named ranges. For CSVs, make sure columns are consistent across files.

Power Query rituals (the sacred steps you will thank me for)

• Promote headers properly: If first row is header, use the Promote Headers button. If not, create meaningful column names.
• Set data types early: Dates as Date, numbers as Decimal/Whole, text for IDs (preserve leading zeros!).
• Remove top/bottom garbage: Delete notes, subtotal rows, or repeated header rows.
• Unpivot when needed: If Excel gives you months as columns, use Unpivot to get tidy data.

Remember: Power BI keeps the query steps. You’re building a reproducible recipe, not a one-off fix.

Combining many CSVs (the magical Folder connector)

Want to stitch together 12 monthly CSVs into one table without copy-pasting? Use:

1. Get Data -> Folder -> point to the folder with all CSVs.
2. Click Combine & Transform Data.
3. Power Query samples the first file, then generates steps to apply to all files.

Code snippet (M) — what Power Query generates behind the scenes:

let
    Source = Folder.Files("C:\\Data\\MonthlyReports"),
    CSVFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")),
    Combined = Csv.Document(File.Contents(CSVFiles{0}[Folder Path] & CSVFiles{0}[Name]),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.Csv])
in
    Combined

(You don’t need to memorize this, but seeing it demystifies the process.)

Common pain points & fixes (you will encounter these)

• Headers repeat every 50 rows: That’s an export quirk — remove rows that match header pattern or filter.
• Dates imported as numbers or wrong locale: Set Locale in the source step or change type using the correct locale.
• Leading zeros disappear: Import as text, or format columns as text in Excel before exporting.
• Encoding issues (weird characters): Use encoding drop-down in CSV import (UTF-8 vs 1252).
• Power BI Service can’t refresh a local file: Upload the file to OneDrive/SharePoint or configure an On-premises data gateway.

Question to ask: Where does the file live and who updates it? That determines refresh method and security.

Best practices (aka how to not be the cause of chaos)

• In Excel: convert data ranges to Tables (Ctrl+T). Name the tables. Clean the sheet before you hand it off.
• Use consistent headers and column order across multiple CSV files.
• Don’t bury calculated columns in Excel — move transformations into Power Query for transparency.
• Document the data source, owner, and update frequency in a README or dataset description in Power BI.
• If data will be refreshed in the Service, prefer storing files on OneDrive or SharePoint or set up a gateway for local files.

A few strategic reminders (linking back to earlier lessons)

• You learned about Import vs DirectQuery vs Live Connection: Excel and CSV are typically Import, which means Power BI stores a copy of the data in the dataset. That’s great for speed and visuals but remember refresh needs either OneDrive/SharePoint or a gateway.
• If your data needs to be real-time or extremely large, consider pushing it into a database that supports DirectQuery — files are not the best live-systems.

Expert take: Files are perfect for prototypes and many production scenarios — just be intentional about storage and refresh.

Closing — TL;DR (and one last dramatic flourish)

• Excel: Use Tables, avoid merged cells, and prefer Transform Data.
• CSV: Great for exports; use Folder connector for multiples and watch encoding and delimiters.
• Always check data types and headers in Power Query.
• Know where the file lives — this controls how refresh works in the Power BI Service.

Go forth and import with confidence. Clean your tables like Marie Kondo for data: if it doesn’t spark reliable analysis, remove or transform it.