Connecting to Excel & CSV
Excel and CSV are the sources most Power BI projects actually start from, and both are more treacherous than they look. This lesson covers connecting to workbooks, why a named table beats a worksheet, and the encoding and delimiter problems that silently corrupt CSV loads.
Learning Objective
These two connectors look trivial and cause a disproportionate share of real problems, because spreadsheets are designed for humans and CSVs carry no type information at all.
- Connect to an Excel workbook and choose between a sheet, a named table and a named range
- Explain why a named table is the safer choice and what breaks without one
- Diagnose delimiter, encoding and locale problems on a CSV load
- Handle the common spreadsheet shapes: merged cells, title rows, and totals rows
- Decide when a spreadsheet source is too fragile to build on at all
Why This Matters
Three failures that are entirely typical.
- A report refreshes fine for four months. Then someone inserts a column in the source spreadsheet and every visual breaks, because the query referenced a sheet and columns are positional there.
- A CSV exported from a German system loads with revenue values a hundred times too large, because
1.249,50was read with an English locale. - A file with accented names loads as
Müllerinstead ofMüller, because the encoding was guessed as Windows-1252 rather than UTF-8.
Concept
Connecting to Excel
Home › Get data › Excel workbook. The Navigator that appears lists three kinds of object, and the icons distinguish them.
| Object | Icon | Behaviour | Verdict |
|---|---|---|---|
| Named table | Table icon (blue grid) | A real Excel table object. Columns are referenced by name and the range grows automatically as rows are added | Best — use this whenever possible |
| Worksheet | Sheet icon | The whole used range of a sheet. Columns are positional; anything else on the sheet comes too | Workable — but fragile |
| Named range | Range icon | A defined range. Fixed unless it is a dynamic named range | Depends — fine if dynamic, brittle if fixed |
What goes wrong with worksheets
- Title rows. A merged "Q4 Sales Report" banner in row 1 means your headers are in row 3. Use Remove Top Rows then Use First Row as Headers.
- Totals rows. A SUM row at the bottom loads as data and inflates every aggregate. Filter it out — usually by removing rows where the key column is null.
- Merged cells. They produce nulls in every cell but the first. Transform › Fill › Down is the usual fix.
- Blank spacer columns. They arrive as
Column7full of nulls. Remove them. - Multiple tables on one sheet. Power Query sees one rectangular used range containing all of them. This is genuinely painful — ask for separate sheets.
Connecting to CSV
Home › Get data › Text/CSV. The preview dialog exposes three settings, and all three matter.
| Setting | What it controls | How it goes wrong |
|---|---|---|
| File origin (encoding) | How bytes become characters | Wrong encoding mangles accented and non-Latin characters. UTF-8 (65001) is the usual correct answer for modern exports |
| Delimiter | What separates fields | European exports often use semicolons because the comma is the decimal separator. Tab and pipe are also common |
| Data type detection | Whether to guess types, and from how many rows | Guessing from the first 200 rows misses problems further down. "Do not detect" gives you everything as text, which is safer if you intend to type deliberately |
The locale problem
This is the one that costs people the most. A CSV has no type information — 1.249,50 is just characters. What it means depends on locale:
| Text in file | Read as English (US/UK) | Read as German |
|---|---|---|
1.249,50 | Error, or 1.249 (with ,50 lost) | 1249.50 ✓ |
1,249.50 | 1249.50 ✓ | Error, or 1249050 |
03/04/2025 | 3 April 2025 (UK) or 4 March 2025 (US) | 3 April 2025 |
Two ways to control it:
- Per-file: File › Options › Current File › Regional Settings sets the locale used for interpreting the whole file.
- Per-step: right-click a column → Change Type › Using Locale… and pick the locale for that specific conversion. This is the precise tool when one file mixes conventions.
03/04/2025 is genuinely ambiguous. You have to know which system produced the file. If you cannot find out, look for a row where the day exceeds 12; that disambiguates the whole column.Visual Explanation
Pick a source and see what it needs. This selector covers the sources in this module.
A messy spreadsheet, and what each problem needs
| What you see | Fix in Power Query |
|---|---|
| Rows 1–2 are a title and a blank | Remove Top Rows (2), then Use First Row as Headers |
| Last row is a bold TOTAL | Filter out rows where the key column is null or equals 'TOTAL' |
| Region shown once per group, blank below | Transform › Fill › Down |
| A blank column between blocks | Remove it, or Remove Blank Columns |
| Numbers stored as text with a leading apostrophe | Change Type, using the right locale |
| Dates as 45291 (Excel serial) | Change Type to Date — Power Query converts serials correctly |
Power BI Demonstration
Example Dataset
Use this to practise the CSV connector. It is clean UTF-8 with comma delimiters, so it should load without incident — which makes it a good baseline before you meet the messy file in Module 3.
bwb-retail-sales.csv1.3 MB · 15067 rows · 13 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
OrderID | Text | Order identifier. One order can have several rows — one per product line. |
OrderDate | Date | Date the order was placed. Use this for time intelligence. |
ShipDate | Date | Date the order shipped. A second date column, for the role-playing dimension lesson. |
CustomerKey | Text | Foreign key to bwb-customers.csv. |
ProductKey | Text | Foreign key to bwb-products.csv. |
StoreKey | Text | Foreign key to bwb-stores.csv. Blank for non-store channels. |
Channel | Text | Online, Retail Store or Partner. |
Quantity | Whole number | Units sold on this line. |
UnitPrice | Decimal | List price per unit before discount. |
Discount | Decimal | Discount rate applied, 0 to 0.30. |
NetSales | Decimal | Revenue after discount. The measure you will sum most often. |
COGS | Decimal | Cost of goods sold for this line. |
Profit | Decimal | NetSales minus COGS. Pre-computed so you can validate your own DAX. |
Questions this dataset can answer
- Which category generates the most revenue, and is it also the most profitable?
- How did monthly revenue trend across the two years, and where is the seasonal peak?
- Which region has the highest revenue per customer?
- What share of revenue comes from discounted orders, and does discounting improve profit?
- Which products account for the top 20% of revenue?
Step-by-Step Exercise
NetSales → Change Type → Using Locale → Decimal Number → German. Observe the errors. Undo. This demonstrates concretely what a locale mismatch does.Expected Result
| Test | Result |
|---|---|
| Correct load | 15,067 rows, 13 columns, comma delimiter, UTF-8 |
| Wrong delimiter (semicolon) | 1 column containing the whole row as text |
| Wrong encoding on this file | Little visible change — the data is plain ASCII |
| NetSales as German decimal | Errors on most rows, because the file uses a dot as the decimal separator |
| Worksheet route | Roughly 5 cleaning steps: remove top rows, promote headers, filter totals, fill down, remove blanks |
| Named table route | 0–1 cleaning steps |
Common Mistakes
Fix: Look at the icons. Choose the table. If there is no table, ask the owner to create one with Ctrl+T.
Fix: Find out where the file came from. Use Change Type › Using Locale to state the assumption explicitly rather than inheriting it.
Fix: Where possible, filter on a condition instead — remove rows until a known header value appears, or filter out rows where the key column is null. Condition-based cleaning survives layout changes.
Fix: Filter it out explicitly. Then validate: your row count should match the source's data rows, not its data rows plus one.
Fix: Move it somewhere shared (SharePoint, OneDrive) and agree the structure will not change. If neither is possible, say plainly that the report will be fragile — that is a real finding, not a complaint.
Professional Tip
It takes fifteen seconds and it catches the entire family of "rows silently disappeared" faults — wrong delimiter splitting rows, an encoding problem truncating the file, a filter you did not realise was applied in the source. These faults are invisible after cleaning, and extremely expensive once they reach a report.
Two supporting habits:
- Note the source's origin in the query description. Right-click a query → Properties → Description. "Exported from the German ERP, semicolon delimited, comma decimal" is exactly the note that saves the next person two hours.
- Keep the raw file. Never let cleaning happen in the source spreadsheet. If the raw file survives, you can always redo the cleaning; if someone cleaned it in place, the original is gone.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
A CSV loads into a single column containing the whole row as text. What is wrong?
Why is connecting to an Excel named table more robust than connecting to a worksheet?
A file from a German system contains 1.249,50. You load it with UK regional settings. What happens?
Your source worksheet has a bold TOTAL row at the bottom. You forget to remove it. What is the symptom?
What is the single most valuable check immediately after any load?
Practical Challenge
Write a connection checklist and test it on a hostile file
Create a deliberately awkward Excel workbook — title rows, a totals row, merged cells in one column, a blank spacer column, and at least one numeric column stored as text. Load it into Power BI and clean it. Then write the reusable checklist you wish you had had at the start.
- Build the hostile workbook with at least five distinct problems
- Clean it in Power Query, with every Applied Step renamed meaningfully
- Validate the final row count against the real number of data rows
- Write a connection checklist of no more than ten items
- For each checklist item, note whether failing it is silent or obvious
- Identify one cleaning step you used that would break if the source changed, and give a more resilient alternative
For the resilience question, look at your steps and ask of each: what change to the source would break this?
Remove Top Rows (2)breaks if a third title row appears.Removed Columns: Column7breaks if columns shift.- A filter on
Region ≠ nullsurvives both.
Condition-based steps beat position-based steps almost every time.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
A model answer
The cleaning sequence
| Step name | What it does | Resilient? |
|---|---|---|
| Source | Connects to the workbook | n/a |
| Remove title rows | Remove Top Rows (2) | No — position-based |
| Promote headers | Use First Row as Headers | Yes |
| Drop totals row | Filter where OrderID is not null | Yes — condition-based |
| Fill region down | Transform › Fill › Down on Region | Yes |
| Remove spacer column | Remove Columns | Partly — breaks if the column is renamed |
| Type revenue as decimal (UK) | Change Type Using Locale | Yes, and explicit |
| Validate row count | A note, not a step | n/a |
The connection checklist
| Check | Fails silently? | |
|---|---|---|
| 1 | Encoding is correct — accented characters look right in the preview | Silent |
| 2 | Delimiter is correct — the preview shows multiple columns | Obvious |
| 3 | Chose Transform Data, not Load | Silent |
| 4 | Connected to a named table, not a worksheet, where one exists | Delayed — breaks later |
| 5 | Read the Changed Type step and confirmed each type is intended | Silent |
| 6 | Locale set explicitly for numeric and date columns from foreign systems | Silent |
| 7 | Title, totals and blank rows removed by condition where possible | Silent |
| 8 | Column quality shows no unexpected errors or empties | Silent |
| 9 | Loaded row count matches the source's data rows | Silent |
| 10 | Applied Steps renamed; source origin noted in the query description | n/a — a maintainability check |
This is the same detectability principle from Module 1, and it is why professional practice leans so heavily on routine validation rather than on noticing problems.
The fragile step, made resilient
Fragile: Table.Skip(Source, 2) — Remove Top Rows with a hard-coded count. It breaks the moment the report template gains or loses a header line, and it breaks silently, shifting every column by one row.
Resilient alternative: find the header row by its content rather than its position.
let
Source = Excel.Workbook(File.Contents(Path), null, true),
Sheet = Source{[Item = "Sales", Kind = "Sheet"]}[Data],
// find the row index where the first column contains "OrderID"
HeaderRow = Table.PositionOf(
Sheet,
Table.SelectRows(Sheet, each [Column1] = "OrderID"){0}
),
Trimmed = Table.Skip(Sheet, HeaderRow),
Promoted = Table.PromoteHeaders(Trimmed, [PromoteAllScalars = true])
in
PromotedNow the query finds its own starting point. Add a title row, remove one, insert a logo — it still works. And if OrderID ever disappears entirely, the query errors loudly rather than shifting silently, which is exactly the behaviour you want.
Lesson Summary
- Prefer an Excel named table over a worksheet — columns are referenced by name and the range grows automatically.
- Worksheets bring title rows, totals rows, merged cells and spacer columns with them. Clean by condition, not by position, wherever possible.
- CSVs carry no type information. Encoding, delimiter and locale all have to be chosen, and two of the three fail silently.
- Use Change Type Using Locale for data from other regions. It makes an invisible assumption explicit.
- The Changed Type step applies your machine's locale to everything at once. Read it rather than trusting it.
- Always compare the loaded row count to the source. Fifteen seconds, and it catches the whole family of silent data-loss faults.
- Prefer steps that fail loudly over steps that fail quietly.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.