Folder Connector & Web Data
Two connectors that solve very different problems. The Folder connector combines dozens of identically-shaped files into one table and is genuinely transformative once you understand the sample-file pattern behind it. The Web connector pulls data from pages and endpoints, and comes with caveats worth taking seriously.
Learning Objective
The Folder connector is one of the highest-value features in Power Query and is widely under-used. The Web connector is easy to use badly.
- Combine many files into one table with the Folder connector
- Explain the four objects Combine Files creates and what each does
- Modify the transform function so a change applies to every file at once
- Add a source-file column so combined data remains traceable
- Use the Web connector responsibly, and know when it will not work
Why This Matters
A situation almost everyone meets. Finance produces one export per month. After two years there are 24 files. Someone has been opening each one and pasting it into a master workbook — about an hour a month, and every paste is a chance to make a mistake.
The Folder connector reduces that to zero minutes a month. Point it at the folder once. New files appear; refresh picks them up. No pasting, no mistakes, no master workbook that only one person understands.
Concept
The Folder connector
Home › Get data › Folder. Point it at a folder and it returns a table of files — one row per file, with metadata columns and a Content column holding the binary.
You then click Combine, and Power Query does something clever that is worth understanding rather than treating as magic.
The four objects Combine creates
| Object | Type | What it does |
|---|---|---|
| Sample File | Query | A reference to one file — by default the first — used as the template for shaping |
| Parameter1 | Parameter | A binary parameter. It is what makes the transform function reusable across files |
| Transform Sample File | Query | This is the one you edit. Shape this single file and every file inherits the shaping |
| Transform File | Function | The function generated from the sample. Applied to every file's binary in turn |
Editing the transform
To change how every file is processed, open Transform Sample File and edit it as though it were an ordinary query. Remove a column, fix a type, filter rows — the change propagates to all files on the next refresh.
A common mistake is editing the combined output query instead. That works, but it applies the change after combining, which is less efficient and — for row filters — means you loaded rows only to throw them away.
Keeping the source filename
By default, the combined table loses track of which file each row came from. Add Source.Name back:
- In the folder query, before combining, keep the
Namecolumn. - Or after combining, the
Source.Namecolumn often survives — check for it and keep it.
This matters enormously for debugging. When one month's numbers look wrong, being able to filter to that file is the difference between a five-minute fix and an afternoon.
What breaks a folder combine
| Problem | Symptom | Fix |
|---|---|---|
| One file has an extra or missing column | Nulls appear, or the combine errors | Filter the folder list to expected files; or handle the variation in the transform |
| A file is open in Excel | A lock file ~$name.xlsx appears and breaks the combine | Filter the folder query to exclude names starting with ~$ |
| Mixed file types in the folder | The combine cannot infer one shape | Filter on Extension |
| Subfolders contain unrelated files | Unexpected rows | Filter on Folder Path, or point at a more specific folder |
| The sample file is atypical | Shaping is wrong for the majority | Change which file the sample points at, in the Sample File query |
The Web connector
Home › Get data › Web. Give it a URL. It handles three quite different cases:
| Case | Behaviour | Reliability |
|---|---|---|
| An HTML page with tables | Power Query parses the page and offers each <table> it finds | Fragile — breaks when the page layout changes |
| A direct file URL (.csv, .xlsx, .json) | Downloads and treats it as that file type | Good — as reliable as the URL |
| A REST API endpoint | Returns JSON or XML for you to parse, with optional headers for authentication | Good, but needs care with auth and paging |
Add Table Using Examples
When the Navigator's detected tables are unhelpful, the Add Table Using Examples button lets you type a few values you want extracted and Power Query infers the pattern. It is genuinely useful for pages where the data is not in a proper <table>.
2. Anything behind an interactive login. Form-based sign-in flows are not supported. API keys and standard auth schemes are.
3. Survive a redesign. Scraping a page couples your report to someone else's HTML, which they will change without telling you.
Visual Explanation
What the Folder connector actually does, step by step.
Manual consolidation versus the Folder connector
| Manual paste | Folder connector | |
|---|---|---|
| Time for the first month | ~10 min | ~15 min (setup) |
| Time for each subsequent month | ~10 min | 0 min |
| Over two years | ~4 hours | ~15 minutes |
| Risk of transcription error | Every single time | None |
| Traceable to source file | No | Yes, via Source.Name |
| Survives the person leaving | No | Yes |
Power BI Demonstration
Extension to .csv. Filter out names beginning with ~$ if any Excel lock files are present. This protects the query from junk in the folder.Content column header, or Home › Combine Files. Confirm the sample file in the dialog and click OK.Source.Name column. If it is not there, go back to the folder query and keep the Name column before combining.Example Dataset
This lesson is conceptual and needs no download — it uses the sample data shown inline above. The next lesson that requires a file will link it here.
You can still browse every dataset in the course from the dataset library.
Step-by-Step Exercise
bwb-retail-sales.csv, then use Excel or a text tool to split it into several files by month — or simply create three copies named sales-2024.csv, sales-2025.csv, sales-extra.csv. Exact contents matter less than having several identically-shaped files.Expected Result
| Situation | Result |
|---|---|
| Three identical files combined | One table, rows = the sum of all three files |
| A file with an extra column | That column appears with nulls for the other files |
| A file with a missing column | Nulls for that file's rows in the missing column |
| Filtering the bad file out by name | Clean result, but the query silently ignores real data |
| Selecting only needed columns in the transform | Clean result, and robust — extra columns in any file are harmless |
| Adding a fifth file and refreshing | It appears with no query changes at all |
That is the same fail-loudly principle from Lesson 1, applied to a different problem.
Common Mistakes
Fix: Edit Transform Sample File. That is what it is for.
~$…), stray PDFs, and files from an unrelated subfolder all get opened and break the combine.Fix: Filter on Extension and on Name before you click Combine. Treat the folder list as untrusted input.
Fix: Keep the
Name column before combining. It costs one column and saves hours.Fix: Open the Sample File query and point it at a typical file.
Fix: Prefer a published API or a direct file URL. If you must scrape, expect breakage, keep the query simple, and tell your stakeholders the dependency exists.
Professional Tip
Three things this buys you: extra columns in any file become harmless; a genuinely missing required column produces a clear error rather than a silent null; and the query documents its own requirements, so the next person can see at a glance what the source must provide.
It is one extra step and it converts a fragile combine into a robust one.
Two more:
- Use the SharePoint folder connector rather than a mapped drive when the files live in SharePoint or OneDrive. A mapped drive letter is machine-specific and will not refresh in the service; the SharePoint connector will.
- Add a Date modified filter if the folder accumulates indefinitely. Combining only the last 24 months keeps refresh times stable as the archive grows.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
Which query should you edit to change how every file in a folder combine is processed?
Your folder combine suddenly fails. A colleague has the source workbook open in Excel. Why does that break it?
Why keep the Source.Name column in a combined folder query?
You need data from a page where the numbers only appear after the page's JavaScript runs. What will the Web connector do?
A folder contains files that mostly share a structure, but one has an extra column. What is the more robust fix?
Practical Challenge
Build a folder pipeline that survives contact with reality
Create a folder of monthly export files that includes every awkward thing a real folder contains, then build a combine query that handles all of it without manual intervention.
- At least five valid data files with identical structure
- One file with an extra column
- One file with a missing column
- One non-data file (a PDF, a .txt, or a fake
~$lock file) - A combine query that produces correct output with no manual exclusions by filename
- Source.Name retained, and a validation note comparing total rows against the sum of the individual files
- A written note on what would still break it
Work in this order:
- Filter the folder list by
Extensionand by a name pattern that excludes lock files — that handles the non-data file generically. - In the Transform Sample File, use Choose Columns to select exactly the columns you require — that handles the extra column.
- Decide deliberately what should happen for the missing column. Should the query error, or should it produce nulls? Both are defensible; the point is choosing rather than discovering.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
A model solution
1 · Filter the folder list generically
let
Source = Folder.Files(DataFolder),
// keep only CSVs, and drop Excel/Office lock files
OnlyCsv = Table.SelectRows(Source, each
Text.Lower([Extension]) = ".csv"
and not Text.StartsWith([Name], "~$")
),
// optional: only the last 24 months, so refresh time stays stable
Recent = Table.SelectRows(OnlyCsv, each
[Date modified] >= Date.AddMonths(DateTime.LocalNow(), -24)
)
in
RecentNote that none of this names a specific file. Drop any junk into the folder and it is excluded by rule, not by exception.
2 · Make the transform tolerant of extra columns
In Transform Sample File, after promoting headers and setting types, finish with an explicit column selection:
let
Source = Csv.Document(Parameter1, [Delimiter = ",", Encoding = 65001]),
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
// the columns this pipeline requires — extra columns in any file are ignored,
// and a genuinely missing required column raises an error rather than a null
Required = Table.SelectColumns(Promoted,
{"OrderID", "OrderDate", "CustomerKey",
"ProductKey", "Channel", "Quantity", "NetSales"},
MissingField.Error),
Typed = Table.TransformColumnTypes(Required, {
{"OrderDate", type date},
{"Quantity", Int64.Type},
{"NetSales", type number}
}, "en-GB")
in
TypedTable.SelectColumns takes an optional third argument.MissingField.Error (the default) — the query fails loudly if a required column is absent.MissingField.UseNull — the column is created full of nulls, and the query succeeds quietly.For a required column, choose
MissingField.Error. A refresh failure gets investigated that morning; a column silently full of nulls produces weeks of wrong reports.3 · Retain Source.Name and validate
In the folder query, keep Name alongside Content before invoking the transform function. After expanding, rename it to Source File.
Then validate: group by Source File and count rows. Compare each count against the line count of the corresponding file. They must match exactly.
4 · What would still break it
| Would break it | Why | Mitigation |
|---|---|---|
| A required column renamed in a future export | Table.SelectColumns matches on name, so a rename reads as a missing column | Correct behaviour — it errors loudly. Add a renaming step that maps known old names to new ones if the source is prone to this |
| A file with a different delimiter or encoding | The transform hard-codes both | Rare in practice within one folder. If it happens, the file almost certainly belongs to a different pipeline |
| The sample file being deleted | The Sample File query points at a specific file | Point the sample at a file you control, or accept that it re-samples |
| The folder path changing | Hard-coded path | Already mitigated here — DataFolder is a parameter |
| A file with a genuinely different date format | The locale is fixed at en-GB in the type step | This is the one I would flag to the data owner rather than engineer around. Mixed date formats within one export pipeline is a source-system problem |
Why the last row matters
A good engineering answer knows where to stop engineering. You could write M that sniffs each file's date format and adapts. It would be clever, hard to read, and it would paper over a real problem in the upstream system that will cause trouble elsewhere too.
Recognising which problems to solve in the query and which to escalate to the data owner is a judgement that separates a maintainable pipeline from a heroic one. Heroic pipelines are impressive right up until the person who wrote them leaves.
Lesson Summary
- The Folder connector combines many identically-shaped files into one table, and picks up new files automatically on refresh.
- Combine creates four objects. Edit Transform Sample File — shaping one file shapes all of them.
- Filter the folder list before combining, on extension and name pattern. Treat the folder as untrusted input.
- Keep Source.Name so every row can be traced back to its file.
- Use Choose Columns with MissingField.Error in the transform — it tolerates extra columns and fails loudly on missing required ones.
- The Web connector handles HTML tables, direct file URLs and REST endpoints. It cannot run JavaScript or handle interactive logins.
- Prefer a published API or file URL over scraping page markup.
- Know when to escalate rather than engineer — some problems belong to the source system.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.