1.1Connect to data (Excel, CSV)
The essentials in 30 seconds
- Power BI connects to more than 200 sources via Get data; the most common are Excel/CSV files, the web and the Folder connector.
- The professional reflex: "Transform" rather than "Load" — you open Power Query to control the data before importing it.
- Import (default, fast) vs DirectQuery (data remaining in the source): keep Import unless very large volume or real time; the Folder connector merges the monthly exports.
Running mini-case — Zineb connects her agri-food SME's sales
Zineb, a management controller in an agri-food SME in Algiers, receives a CSV export of sales every month. Instead of stacking the files, she applies the section: she opens Power BI Desktop, clicks Get data → Text/CSV, chooses Transform rather than Load to control the preview, then Close & Apply. Better: she groups all the monthly exports into a folder and uses the Folder connector, which combines them into a single "Sales" table. She keeps Import mode (a few thousand rows) and names her table properly. Her report is connected to a single source that grows on its own.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 1.1.
1. Get data
Everything starts with Get data: Power BI connects to more than 200 sources — Excel/CSV files, folders, SQL databases, web pages, cloud services. Two structuring choices right at import: Load directly or Transform first. The right reflex is always to Transform to control the data before importing it, like Zineb with her sales export.
2. Import or DirectQuery
Two connection modes: Import mode copies and compresses the data in the .pbix file (fast, the recommended default); DirectQuery leaves the data in the source and queries it live (for very large volumes or real time, at the cost of performance). Zineb stays on Import: a few thousand rows don't justify DirectQuery.
3. The Folder connector
The most underrated connector is the Folder: it points to a folder of homogeneous files and combines them into a single table — the anti-copy-paste of monthly exports. Zineb drops her twelve CSVs into a folder and gets a single "Sales" table, updated with each new export. Final reflex: name tables and queries properly right at import.
🔎 Concrete example — 🔎 Concrete example — The Folder connector
Zineb merges her monthly exports instead of stacking them:
A folder with sales-jan.csv, sales-feb.csv, sales-mar.csv
→ Get data → Folder → Combine
→ a SINGLE merged table (updated with each new export).Reading: the Folder connector reads all the homogeneous files in a folder and reunites them.
Lesson: no more monthly copy-pastes — a table that grows on its own, and always via "Transform".
Section vocabulary
- Get data
- The universal entry point (Home): more than 200 connectors, from files to databases via the web.
Simple example: Zineb connects her sales export there.
What it is not: a manual entry of data in Power BI.
Reflex: connect a source → Get data.
- Load vs Transform
- Load = import as is; Transform = open Power Query first.
Simple example: Zineb always goes through Transform to check.
What it is not: a blind import without verification.
Reflex: reflex: Transform, always.
- Import mode
- The data is copied and compressed in the .pbix: maximum performance, the recommended default.
Simple example: Zineb stays on Import with her few thousand rows.
What it is not: DirectQuery, which queries the source live.
Reflex: small/medium volume → Import.
- DirectQuery
- The data stays in the source, queried live: for very large volumes or real time.
Simple example: Zineb would reserve it for a bulky warehouse.
What it is not: the default Import mode.
Reflex: large volume / real time → DirectQuery.
- Folder connector
- Points to a folder of homogeneous files and combines them into a single table.
Simple example: Zineb merges her monthly exports into a "Sales" table.
What it is not: a file-by-file copy-paste.
Reflex: periodic exports → Folder.
In practice — In practice — Three connections, three reflexes
- Connect a CSV file (Get data → Text/CSV): choose Transform data (not Load), like Zineb.
- Hover over the Power Query preview, then Close & Apply: the "Sales" table arrives in the model.
- Create a folder with 2-3 copies of CSV in the same format → Get data → Folder → Combine: a single merged table.
- In the Data pane, rename your tables properly (right-click → Rename): "Sales", not "sales (2)".
Step-by-step script — connecting sales like Zineb (5 min)
1) Zineb opens Power BI Desktop → Home → Get data → Text/CSV. 2) She selects the sales export and choose Transform data: the Power Query editor opens. 3) Hover over the preview, check the headers, then Close & Apply. 4) In the Data pane, rename the table to "Sales". 5) Create a folder of several exports and repeat via the Folder connector → Combine.
The Folder connector is the anti-copy-paste: a single object to refresh instead of twelve files.
Copy-paste template — my connection checklist
[ ] Source chosen: Excel / CSV / Folder / Web
[ ] Going through TRANSFORM (Power Query), never loading straight away
[ ] Import mode (DirectQuery reserved for large volume / real time)
[ ] Table and query renamed properly ("Sales")
[ ] Sources stored in a stable location.Monthly export
Zineb drops her CSVs into a folder: the Folder connector combines them into a single table.
Web page
Zineb connects a statistics page via Get data → Web: Power BI detects the tables.
Database
For a heavier volume, Zineb connects to a SQL database — always via Transform first.
Moved source
Zineb stores her sources in a team OneDrive: the connection doesn't break if moved.
✓ Best practices
- Always Transform before loading: you control the data.
- Keep Import mode; DirectQuery only if necessary.
- Name tables and queries properly right at import.
✗ Common pitfalls
- Clicking "Load" without going through Power Query.
- Copy-pasting the monthly exports instead of the Folder connector.
- Storing sources in an unstable location (the connection breaks).
Key takeaways
- You get data via 200+ connectors (file, web, database, folder).
- Reflex: "Transform data" to control before loading.
- Import by default; DirectQuery for large volume / real time.
- The Folder connector merges periodic exports into a single table.
48h challenge — apply tomorrow
Connect like Zineb a real source: import a CSV via Transform data, rename the table, then create a folder and merge 2-3 exports with the Folder connector. Note in one sentence why you stay in Import mode.
- I know how to connect an Excel/CSV file, the web and a folder.
- I apply the "Transform first" reflex and I choose Import/DirectQuery.
- I merge monthly exports with the Folder connector.
Frequently asked questions
My Excel source is "dirty" (titles, empty rows, totals): what to do?
That's Power Query's job (section 1.2): you import the raw sheet and clean it in repeatable steps. Above all, don't retouch the source file by hand at each export — that discipline avoids redoing the work every month.
What happens to my report if I move the source file?
The connection breaks: Home → Transform data → Data source settings → change the path. To avoid it, store the sources in a stable location (team SharePoint/OneDrive) from the start.
What is the right reflex when importing data?