Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🔌 Module 2 · Connecting to Data Beginner LESSON 11 / 72

Connecting to Excel & CSV

26 min 5 quiz questions 1 practical challenge 4 syllabus topics

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.

Lesson 11 of 72 · Connecting to Data 15%
01

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.

By the end of this lesson you will be able to
  • 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
02

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,50 was read with an English locale.
  • A file with accented names loads as Müller instead of Müller, because the encoding was guessed as Windows-1252 rather than UTF-8.
None of these throws an error
All three load "successfully". The first breaks visibly later; the other two produce wrong data immediately and silently. This is why the import dialog deserves ten seconds of attention rather than a reflexive click.
03

Concept

Connecting to Excel

Home › Get data › Excel workbook. The Navigator that appears lists three kinds of object, and the icons distinguish them.

What the Navigator offers
ObjectIconBehaviourVerdict
Named tableTable icon (blue grid)A real Excel table object. Columns are referenced by name and the range grows automatically as rows are addedBest — use this whenever possible
WorksheetSheet iconThe whole used range of a sheet. Columns are positional; anything else on the sheet comes tooWorkable — but fragile
Named rangeRange iconA defined range. Fixed unless it is a dynamic named rangeDepends — fine if dynamic, brittle if fixed
Ask the data owner to make it a table
In Excel: select the data and press Ctrl+T, then name the table on the Table Design tab. It takes them fifteen seconds and it makes your query robust against inserted columns, added rows and moved blocks. This request is worth making every time, and it is a good early signal that you know what you are doing.

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 Column7 full 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.

The three CSV settings
SettingWhat it controlsHow it goes wrong
File origin (encoding)How bytes become charactersWrong encoding mangles accented and non-Latin characters. UTF-8 (65001) is the usual correct answer for modern exports
DelimiterWhat separates fieldsEuropean exports often use semicolons because the comma is the decimal separator. Tab and pipe are also common
Data type detectionWhether to guess types, and from how many rowsGuessing 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:

The same characters, two locales
Text in fileRead as English (US/UK)Read as German
1.249,50Error, or 1.249 (with ,50 lost)1249.50 ✓
1,249.501249.50 ✓Error, or 1249050
03/04/20253 April 2025 (UK) or 4 March 2025 (US)3 April 2025

Two ways to control it:

  1. Per-file: File › Options › Current File › Regional Settings sets the locale used for interpreting the whole file.
  2. 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.
The date column ambiguity in that table is not solvable by inspection — 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.
04

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

Typical worksheet faults
What you seeFix in Power Query
Rows 1–2 are a title and a blankRemove Top Rows (2), then Use First Row as Headers
Last row is a bold TOTALFilter out rows where the key column is null or equals 'TOTAL'
Region shown once per group, blank belowTransform › Fill › Down
A blank column between blocksRemove it, or Remove Blank Columns
Numbers stored as text with a leading apostropheChange Type, using the right locale
Dates as 45291 (Excel serial)Change Type to Date — Power Query converts serials correctly
05

Power BI Demonstration

Connect to a CSV and read the dialog
Home › Get data › Text/CSV. Before clicking anything, read the three dropdowns: File Origin, Delimiter, Data Type Detection. Confirm the preview looks like columns, not one wide column of text.
Set encoding explicitly
If accented characters look wrong, change File Origin to 65001: Unicode (UTF-8). This fixes the great majority of character corruption.
Choose Transform Data
As always. You now see the Applied Steps: Source, Promoted Headers, Changed Type.
Inspect the Changed Type step
Click it and look at the formula bar. It lists every column and the type Power BI guessed. This single step is responsible for most silent data corruption in Power BI.
Change a type using locale
Right-click a numeric column → Change Type › Using Locale…. Pick the data type and the locale of the system that produced the file. Note that this creates a separate step, which is more explicit and easier to audit than the blanket Changed Type.
Connect to an Excel workbook
Get data › Excel workbook. In the Navigator, note the different icons. Select a named table if one exists.
Compare a sheet against a table
Load both the sheet and the table from the same data. The sheet version includes the title row and totals row; the table version does not.
Clean a worksheet load
Remove Top Rows → Use First Row as Headers → filter out the totals row → Fill Down on any merged column → remove blank columns.
Rename every step
Double-click each Applied Step and give it a meaningful name — Remove title rows, Drop totals row. Your query becomes self-documenting.
Check Data source settings
Home › Data source settings. Note the full path stored. Consider parameterising it, as covered in Module 1 Lesson 5.
HomeGet dataText/CSVFile Origin65001: Unicode (UTF-8)
Change Type Using Locale is the professional habit
The default Changed Type step applies your machine's locale to everything at once. Explicit per-column locale conversions are more verbose but they document an assumption that would otherwise be invisible — and invisible assumptions are what break a model when someone else opens it on a differently-configured machine.
06

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.csv
1.3 MB · 15067 rows · 13 columns · CSV
Business context
Two full years (Jan 2024 – Dec 2025) of order-line transactions for a mid-sized multi-channel retailer selling across six product categories, five regions and three channels. Contains real seasonality — a November peak, a February trough — and category margins that differ enough to make the analysis interesting. This is the course's primary fact table.

Columns & data types

ColumnData typeMeaning
OrderIDTextOrder identifier. One order can have several rows — one per product line.
OrderDateDateDate the order was placed. Use this for time intelligence.
ShipDateDateDate the order shipped. A second date column, for the role-playing dimension lesson.
CustomerKeyTextForeign key to bwb-customers.csv.
ProductKeyTextForeign key to bwb-products.csv.
StoreKeyTextForeign key to bwb-stores.csv. Blank for non-store channels.
ChannelTextOnline, Retail Store or Partner.
QuantityWhole numberUnits sold on this line.
UnitPriceDecimalList price per unit before discount.
DiscountDecimalDiscount rate applied, 0 to 0.30.
NetSalesDecimalRevenue after discount. The measure you will sum most often.
COGSDecimalCost of goods sold for this line.
ProfitDecimalNetSales 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?
07

Step-by-Step Exercise

Load the sales CSV and record the details
Note the encoding, delimiter and row count Power BI detects. Confirm 15,067 rows load.
Deliberately break the encoding
Reload with File Origin set to something else — Windows-1252, say. Compare the preview. On this file the effect is minimal because the data is plain ASCII; note that, because it explains why encoding problems often surface only when real names arrive.
Deliberately break the delimiter
Reload with the delimiter set to semicolon. Observe that you get one column containing everything. This is the unmistakable signature of a wrong delimiter.
Examine the Changed Type step
Load correctly, then click the Changed Type step and read the formula bar. Write down which columns Power BI typed as Date, which as Decimal, and which as Text.
Test locale conversion
Right-click NetSales → Change Type → Using Locale → Decimal Number → German. Observe the errors. Undo. This demonstrates concretely what a locale mismatch does.
Build a resilient Excel query (if you have Excel)
Paste a few hundred rows of the sales data into a new workbook. Add a title row above it and a totals row below. Save. Then connect from Power BI as a worksheet, and clean it. Then convert the block to a named table with Ctrl+T and connect again. Compare the number of cleaning steps each route needs.
08

Expected Result

What you should observe
TestResult
Correct load15,067 rows, 13 columns, comma delimiter, UTF-8
Wrong delimiter (semicolon)1 column containing the whole row as text
Wrong encoding on this fileLittle visible change — the data is plain ASCII
NetSales as German decimalErrors on most rows, because the file uses a dot as the decimal separator
Worksheet routeRoughly 5 cleaning steps: remove top rows, promote headers, filter totals, fill down, remove blanks
Named table route0–1 cleaning steps
The comparison at the end is the whole lesson
Five steps versus one. And it is not only about effort — every one of those five steps is a point of fragility. Remove Top Rows (2) breaks the day someone adds a third title row. The named table route has nothing to break. Getting the source right is always cheaper than cleaning around it.
09

Common Mistakes

Connecting to a worksheet when a named table exists
The Navigator lists both, and the sheet is often the more obvious-looking option. The sheet version breaks when columns move.

Fix: Look at the icons. Choose the table. If there is no table, ask the owner to create one with Ctrl+T.
Accepting the guessed locale on numeric and date columns
Power BI applies your machine's locale. If the file came from elsewhere, decimals and dates are silently wrong.

Fix: Find out where the file came from. Use Change Type › Using Locale to state the assumption explicitly rather than inheriting it.
Using Remove Top Rows with a fixed count on a variable file
Remove Top Rows (2) works until the report template gains a third header line.

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.
Leaving the totals row in
A bold TOTAL row loads as a data row and every aggregate is roughly doubled.

Fix: Filter it out explicitly. Then validate: your row count should match the source's data rows, not its data rows plus one.
Building a production model on a personal spreadsheet
The file lives on someone's laptop, gets renamed, gets a column inserted, and the report breaks. Nobody owns it.

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.
10

Professional Tip

Never trust a load until the row count matches
After every connection, before any cleaning: compare the row count Power Query reports against the source. For a CSV, open it in a text editor and check the line count minus one for the header. For Excel, use Ctrl+End to find the last row.

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.
11

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?

12

Practical Challenge

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.

Requirements
  • 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: Column7 breaks if columns shift.
  • A filter on Region ≠ null survives both.

Condition-based steps beat position-based steps almost every time.

13

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 nameWhat it doesResilient?
SourceConnects to the workbookn/a
Remove title rowsRemove Top Rows (2)No — position-based
Promote headersUse First Row as HeadersYes
Drop totals rowFilter where OrderID is not nullYes — condition-based
Fill region downTransform › Fill › Down on RegionYes
Remove spacer columnRemove ColumnsPartly — breaks if the column is renamed
Type revenue as decimal (UK)Change Type Using LocaleYes, and explicit
Validate row countA note, not a stepn/a

The connection checklist

CheckFails silently?
1Encoding is correct — accented characters look right in the previewSilent
2Delimiter is correct — the preview shows multiple columnsObvious
3Chose Transform Data, not LoadSilent
4Connected to a named table, not a worksheet, where one existsDelayed — breaks later
5Read the Changed Type step and confirmed each type is intendedSilent
6Locale set explicitly for numeric and date columns from foreign systemsSilent
7Title, totals and blank rows removed by condition where possibleSilent
8Column quality shows no unexpected errors or emptiesSilent
9Loaded row count matches the source's data rowsSilent
10Applied Steps renamed; source origin noted in the query descriptionn/a — a maintainability check
Seven of ten fail silently
That ratio is the reason this checklist is worth having. Only one item on it — the delimiter — announces its own failure. Everything else produces a file that loads cleanly and is wrong.

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.

Power Query M · Position-independent header detection
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
    Promoted

Now 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.

The principle worth taking away
Prefer steps that fail loudly over steps that fail quietly. A query that errors on refresh gets fixed that morning. A query that silently shifts every column produces a fortnight of wrong reports before anyone notices. When you have a choice between the two, take the noisy one every time.
14

Lesson Summary

Key takeaways
  • 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

ExcelCSVSheets vs tables vs rangesEncoding and delimiters
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 15% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.