Connecting to Files
Leaf & Cup's tills have no database behind them. At the end of each month, each branch exports its sales as a CSV file and saves it to a shared SharePoint folder: pune-2026-01.csv, mumbai-2026-01.csv, and so on. Arjun's first real job is to turn those files into one table in Power BI — in a way that still works next month without him touching it.
The rule
Pick the connector that matches the file, and keep the path reachable for refresh.
Everything starts at Home → Get data. The file connectors you will meet most:
| Connector | Reads | Tip |
|---|---|---|
| Excel workbook | Sheets, formatted tables, named ranges | Prefer a formatted table: it has one header row and grows as rows are added |
| Text/CSV | One delimited text file | Check the delimiter and the file encoding in the preview |
| JSON, XML | Structured text | Arrives as nested records that you expand into columns |
| Parquet | Column-based files from data platforms | Compact and fast to read |
| Tables printed inside a PDF | Fine for one-off extracts | |
| Folder, SharePoint folder | Every file in a folder | Combines files with the same columns into one table |
After you choose a file, a preview appears with two buttons. Load sends the data straight into the model. Transform Data opens Power Query first, so you can clean before loading. Cleaning first is almost always the better habit.
Where the file lives matters the moment you publish. A file on the C: drive of Arjun's laptop refreshes fine in Desktop, but the Service in the cloud cannot see his laptop. To refresh it from the Service he would need an on-premises data gateway running on a machine that can reach the file. Files stored in SharePoint or OneDrive for work or school can be refreshed from the cloud with no gateway.
The shortcut
Ask two questions. One file or many? One file: the Excel or Text/CSV connector. Many files with the same columns: a Folder connector. Where does it live? On a personal drive: plan for a gateway. In SharePoint or OneDrive: the cloud can refresh it.
Spot the pattern
24 CSV files, one per branch per month, all with the same six columns.
The trap is a stray file. A CSV with an extra column, a renamed header or a different layout dropped into the same folder can break the combine or push values into the wrong columns. Keep one layout per folder, and filter the file list by name or extension, so only the files you expect are combined.
Drills
Answer fast — trust the rule.
Leaf & Cup has 24 monthly CSV files with identical columns. Which connector brings them in as one table?
In an Excel workbook, why choose a formatted table rather than the whole sheet?
On the preview screen, what does Transform Data do?
The report reads C:\Reports\sales.xlsx on Arjun's laptop. After publishing, scheduled refresh in the Service fails. What fixes it?
Remember
- Pick the connector that matches the file, and keep the path reachable for refresh.
- Ask: one file or many, and where does it live?
- Trap: a file with a different layout in a combined folder; filter the file list.