Power BI Lesson 6 – Getting Data from Files | Dataplexa

Beginner Level · Lesson 6

Getting Data from Files

The most common starting point for any Power BI project is loading data from a file — usually an Excel workbook or a CSV export — and knowing exactly how Power BI reads those files, what can go wrong, and how to fix it will save you hours of troubleshooting before you ever build a chart.

How Power BI Connects to Data

When you connect Power BI to any data source, it does not just open the file the way Excel does. It creates a query — a set of instructions that describes how to retrieve and shape the data. Every time you refresh the report, Power BI replays those instructions from scratch. This is a fundamentally different model from copy-pasting data.

How beginners think it works

Power BI opens the file and copies all the data into the report. If the file moves or changes, the data in the report stays as it was — like a static snapshot.

How it actually works

Power BI stores the file path and a query. When you refresh, it goes back to that path, re-reads the file, and re-applies all transformations. Move the file and the connection breaks.

This means where you save your source files matters from day one. Keep your Excel and CSV files in a stable, predictable location — not in your Downloads folder or on a Desktop that gets reorganised regularly.

The Get Data Dialog

Every data connection starts with the same entry point — Home → Get Data in the ribbon. This opens the Get Data dialog, which lists every connector Power BI supports. There are over 100 connectors. For file-based sources you only need to know four of them.

Get Data

Connecting to an Excel File

Excel is the most common source for beginner Power BI work. The connection process involves three steps — selecting the file, choosing which sheets or tables to load, and deciding whether to load directly or transform first.

Open Get Data and select Excel Workbook Click Home → Get Data → Excel Workbook in the ribbon. A standard Windows file browser opens. Navigate to your Excel file and click Open. Power BI will read the file structure.

The Navigator window appears Power BI lists every sheet and every named Table it found in the workbook. Sheets show with a grid icon. Named Tables show with a table icon and are usually cleaner to work with. Tick the items you want to load — you can select multiple. A preview of the selected item appears on the right.

Load or Transform Data You have two choices at the bottom of the Navigator: Load brings the data straight into the model as-is. Transform Data opens Power Query Editor first so you can clean and reshape the data before it enters the model. For most real-world data, always choose Transform Data — raw data almost always needs some work.

Navigator — SalesData.xlsx

SalesData.xlsx

📋 Sales_Table

📋 Products_Table

📊 Sheet1

📊 Sheet2 (hidden)

Preview — Sales_Table

OrderID	OrderDate	Region	Product	Revenue	Qty
1001	2024-01-05	North	Laptop	1200	2
1002	2024-01-07	South	Monitor	350	1
1003	2024-01-09	North	Keyboard	85	5
1004	2024-01-12	East	Laptop	1200	1
1005	2024-01-14	West	Monitor	350	3

Load

Transform Data

Cancel

Sheets vs Named Tables — Which to Use

When Power BI reads an Excel file it finds both raw sheets and named tables. You should always prefer named tables over raw sheets when both are available. Here is exactly why.

Behaviour	Raw Sheet	Named Excel Table
Row detection	Reads all rows in the used range — including blank rows between data blocks	Reads only the rows that belong to the table — clean boundary
Column headers	First row promoted to headers — only if no extra rows are above the data	Headers are always correct — defined as part of the table structure in Excel
New rows added	New rows below the original range may not be detected on refresh	New rows inside the table are always picked up on the next refresh
Extra junk	Totals rows, notes, and charts on the sheet get picked up as data rows	Only the defined table rows are included — nothing else on the sheet is read

Teacher's Note: Before connecting any Excel file to Power BI, open it in Excel first and convert your data ranges to named tables. Select your data, press Ctrl + T, give the table a clear name like Sales_2024, and save. This one habit prevents at least 80% of the import problems beginners run into with Excel files.

Connecting to a CSV File

CSV files are simpler than Excel — they have no sheets, no named tables, no formatting. Power BI reads them as a flat table of rows and columns. The process is almost identical to Excel but with a few important differences to understand.

Home → Get Data → Text/CSV Select Text/CSV from the Get Data menu. Browse to your .csv file and click Open. Power BI immediately reads the file and shows a preview — there is no Navigator window because there is only one table in a CSV.

Check the three auto-detected settings The preview dialog shows File Origin (character encoding — usually UTF-8), Delimiter (the separator between columns — usually comma, but sometimes semicolon or tab), and Data Type Detection (how many rows Power BI scans to guess column types). Check all three before proceeding.

Load or Transform Data Same choice as Excel — Load to bring it in as-is, or Transform Data to open Power Query Editor first. For CSV files, always choose Transform Data — Power BI's automatic data type detection is unreliable and almost always needs manual correction.

CSV Preview — orders_export.csv

File Origin

1252: Western European (Windows) ▾

Delimiter

Comma ▾

Data Type Detection

Based on first 200 rows ▾

OrderID	OrderDate	CustomerName	Product	Amount	Status
2001	05/01/2024	Alice Brown	Laptop	1200	Shipped
2002	07/01/2024	Bob Singh	Monitor	350	Delivered
2003	09/01/2024	Carol Diaz	Keyboard	85	Processing
2004	12/01/2024	Dan Patel	Laptop	1200	Shipped

Load

Transform Data

Cancel

Common CSV Problems and How to Fix Them

CSV files exported from different systems come in slightly different formats. These are the most common issues and exactly how to resolve each one.

Problem: All data appears in one column

The delimiter is set to Comma but your file uses Semicolons or Tabs. This is common with European-locale exports from SAP or other ERP systems.

Fix: In the CSV preview dialog, change the Delimiter dropdown from Comma to Semicolon (or Tab). The data will immediately split into the correct columns.

Problem: Special characters show as garbled symbols

The File Origin encoding is wrong. The file was saved as UTF-8 but Power BI is reading it as Windows-1252, or vice versa.

Fix: Change the File Origin dropdown to UTF-8 (65001). If the characters fix themselves, you have found the right encoding.

Problem: A number column is being treated as text

Power BI scanned the first 200 rows to detect types but found mixed content (e.g. "N/A" entries among numbers) and decided the whole column is text.

Fix: Click Transform Data and change the column type manually in Power Query Editor. Right-click the column header → Change Type → Decimal Number or Whole Number.

Problem: Date column shows as plain numbers (e.g. 44927)

The CSV was exported from Excel and dates were stored as Excel serial numbers instead of formatted date strings.

Fix: In Power Query Editor, select the date column → Change Type → Date. If that does not work the column may need a custom transformation, covered in Lesson 22.

Using the Folder Connector to Combine Multiple Files

A very common real-world scenario: you receive a new CSV export from a system every month and need to combine them all into one table. The Folder connector lets Power BI automatically stack all files from a folder into one query — so each month when a new file arrives, you drop it in the folder and refresh.

Get Data → Folder Choose Folder from Get Data. Enter the folder path or click Browse to navigate to the folder containing your files. Click OK.

Power BI lists all files in the folder A preview shows every file found — name, extension, date modified, and file size. Click Combine → Combine and Transform Data to stack all files and open Power Query Editor for any cleaning needed.

Power BI creates a helper query automatically Behind the scenes Power BI creates a "Transform Sample File" helper query that defines how each file should be read. It applies those same steps to every file. When you drop a new file into the folder and refresh, it is processed automatically using the same rules.

Folder Connector — What the File List Looks Like

Name	Extension	Date Modified	Size (bytes)	Folder Path
orders_jan_2024	.csv	01/02/2024	14,220	C:\Reports\Monthly\
orders_feb_2024	.csv	01/03/2024	15,840	C:\Reports\Monthly\
orders_mar_2024	.csv	01/04/2024	13,980	C:\Reports\Monthly\
orders_apr_2024	.csv	01/05/2024	16,100	C:\Reports\Monthly\

New file added this month — picked up automatically on next refresh

What Happens After You Load Data

Once you click Load (or Close and Apply from Power Query), Power BI imports the data into its in-memory engine. You will see a loading progress bar. When it finishes, your tables appear in the Data pane on the right side of the interface.

After loading, here is what you will see:

DATA PANE (right side)
  ▼ Sales_Table                ← your table name from Excel
      ∑ Revenue                ← numeric column (aggregatable)
      ∑ Quantity               ← numeric column
      📅 OrderDate             ← detected as date type
      🔤 Region                ← text column
      🔤 Product               ← text column
      🔑 OrderID               ← whole number / key column

STATUS BAR (bottom of window)
  Rows loaded: 1,240
  Storage mode: Import
  Last refresh: Today 14:22

If you loaded multiple tables, each appears as its own
section in the Data pane. Tables do NOT auto-relate to
each other — you define relationships manually in
Model View (covered in Lesson 11).

What each Data pane icon means after loading

∑

Numeric — Power BI will SUM this by default when dragged into a visual

📅

Date — enables time intelligence and year/month/day drill-down in charts

🔤

Text — used as categories, axis labels, and filter values

🔑

Key / ID column — used to create relationships between tables in Model View

Refreshing Data and Changing the File Path

Once data is loaded, you will need to refresh it whenever the source file is updated. You will also occasionally need to update the file path if the source file has moved. Both operations are straightforward once you know where to look.

Refreshing data

Go to Home → Refresh in the ribbon. Power BI re-reads all connected source files from their stored paths and re-applies every transformation. If a file has been updated since last refresh the new data appears. If a file is missing you will get a refresh error.

Changing the file path

Go to Home → Transform Data → Data Source Settings. Select the source whose path you want to change and click Change Source. Browse to the new file location. Click OK and then Close and Apply. Power BI will re-read the data from the new path.

Teacher's Note: Never rename or move source files without updating the path in Power BI immediately. If you share a .pbix file with a colleague and they try to refresh it on their machine, the path C:\Users\YourName\Documents\sales.xlsx will not exist on their computer. For shared reports, store source files in a shared network drive or SharePoint folder and connect Power BI to that shared path from the very beginning.

Practice

Practice 1 of 3

When connecting to an Excel file, Power BI shows both raw sheets and named ___. You should always prefer the named version because it has cleaner row detection and reliable column headers.

Practice 2 of 3

If all CSV data appears in a single column after import, the most likely cause is that the wrong ___ setting was auto-detected in the CSV preview dialog.

Practice 3 of 3

To update a broken source file path after a file has moved, you go to Home → Transform Data → Data ___ Settings.

Lesson Quiz

Quiz 1 of 3

What is the key difference between clicking Load and Transform Data in the Navigator window when connecting to an Excel file?

Load brings in only selected columns; Transform Data brings in all columns Load imports the data as-is; Transform Data opens Power Query Editor so you can clean the data first Load creates a live connection; Transform Data imports a static copy They do the same thing — Transform Data just takes longer

Quiz 2 of 3

You receive a new monthly CSV export and need Power BI to include it automatically without creating a new connection each time. Which connector should you use?

Excel Workbook connector — connect to each file individually Text/CSV connector — connect to the most recent file and update the path monthly Folder connector — point to the folder and new files are picked up automatically on refresh Web connector — host the CSV online and Power BI downloads it on refresh

Quiz 3 of 3

A colleague opens your .pbix file on their machine and the refresh fails immediately. The most likely reason is:

The source file path points to a location on your machine that does not exist on theirs They are using a different version of Power BI Desktop The .pbix file is corrupt and needs to be re-exported Power BI only allows one user to open a file at a time

Next up — Lesson 7 takes you beyond files and shows you how to connect Power BI directly to databases — SQL Server, MySQL, and others — including what credentials mean, the difference between Import and DirectQuery mode, and how to write your first SQL query inside Power BI.

← Previous Course Index Next →

Power BI Course