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

Folder Connector & Web Data

28 min 5 quiz questions 1 practical challenge 4 syllabus topics

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.

Lesson 12 of 72 · Connecting to Data 17%
01

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.

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

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.

This is the demo that sells Power BI internally
If you want to show a sceptical team why this tool is worth learning, do not show them a dashboard. Show them the Folder connector eliminating a monthly manual task. It is immediate, concrete, and it is a task they resent.
03

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

What appears in your Queries pane
ObjectTypeWhat it does
Sample FileQueryA reference to one file — by default the first — used as the template for shaping
Parameter1ParameterA binary parameter. It is what makes the transform function reusable across files
Transform Sample FileQueryThis is the one you edit. Shape this single file and every file inherits the shaping
Transform FileFunctionThe function generated from the sample. Applied to every file's binary in turn
The pattern in one sentence
You shape one file, and Power Query applies that shaping to all of them. Once this clicks, the four objects stop looking like clutter and start looking like a well-designed abstraction.

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 Name column.
  • Or after combining, the Source.Name column 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

ProblemSymptomFix
One file has an extra or missing columnNulls appear, or the combine errorsFilter the folder list to expected files; or handle the variation in the transform
A file is open in ExcelA lock file ~$name.xlsx appears and breaks the combineFilter the folder query to exclude names starting with ~$
Mixed file types in the folderThe combine cannot infer one shapeFilter on Extension
Subfolders contain unrelated filesUnexpected rowsFilter on Folder Path, or point at a more specific folder
The sample file is atypicalShaping is wrong for the majorityChange 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:

CaseBehaviourReliability
An HTML page with tablesPower Query parses the page and offers each <table> it findsFragile — breaks when the page layout changes
A direct file URL (.csv, .xlsx, .json)Downloads and treats it as that file typeGood — as reliable as the URL
A REST API endpointReturns JSON or XML for you to parse, with optional headers for authenticationGood, 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>.

Three things the Web connector cannot do
1. JavaScript-rendered content. Power Query fetches the HTML the server sends; it does not run a browser. If the data appears only after scripts run, it will not be there.
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.
Check the terms of use before scraping any site, and prefer a published API or download link where one exists. Beyond the legal question, an API is a contract the publisher intends to keep stable, whereas page markup is not.
04

Visual Explanation

What the Folder connector actually does, step by step.

Manual consolidation versus the Folder connector

Manual pasteFolder connector
Time for the first month~10 min~15 min (setup)
Time for each subsequent month~10 min0 min
Over two years~4 hours~15 minutes
Risk of transcription errorEvery single timeNone
Traceable to source fileNoYes, via Source.Name
Survives the person leavingNoYes
The timings are illustrative rather than measured, but the shape is what matters: the connector costs slightly more once and nothing thereafter. That is the whole argument, and it is the argument that persuades a manager.
05

Power BI Demonstration

Create a test folder
Make a folder and put three or four CSVs with identical structure in it. You can split the sales file by year, or just copy it three times with different names.
Connect to the folder
Home › Get data › Folder. Browse to it. The preview shows one row per file with metadata columns.
Choose Transform Data first
Do not click Combine yet. Look at the file list. Note the Name, Extension and Folder Path columns — these are your filtering tools.
Filter the file list
Filter Extension to .csv. Filter out names beginning with ~$ if any Excel lock files are present. This protects the query from junk in the folder.
Now combine
Click the double-down-arrow icon on the Content column header, or Home › Combine Files. Confirm the sample file in the dialog and click OK.
Inspect what was created
Look at the Queries pane. You now have a Transform File group containing the sample, the parameter, the transform sample query and the function.
Edit the Transform Sample File
Open it. Remove a column, or change a type. Return to the combined query and see the change reflected across every file's rows.
Keep the source filename
In the combined query, check for a Source.Name column. If it is not there, go back to the folder query and keep the Name column before combining.
Test resilience
Add another file to the folder. Refresh. It should appear with no further work. This is the moment the connector's value becomes obvious.
Try the Web connector
Get data › Web. Use a URL that points directly at a CSV — many open-data portals provide these. Note that it behaves exactly like the CSV connector once fetched.
HomeGet dataFolderTransform DataCombine Files
Filter the file list before combining, not after
Every file you leave in the list gets opened and parsed. Filtering on extension and name pattern at the folder stage means fewer files are read at all — which on a folder of several hundred files is a large difference in refresh time.
06

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.

07

Step-by-Step Exercise

Split the sales data into monthly files
Load 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.
Add one deliberately broken file
Create a fourth file with an extra column, or a missing one. This is what real folders look like.
Combine the folder
Connect, filter to .csv, combine. Observe what the broken file does to the result.
Handle the broken file two ways
First: filter it out at the folder stage by name. Second: remove the filter and instead handle the variation in the Transform Sample File — for example by selecting only the columns you need, so an extra one is harmless.
Add Source.Name and test traceability
Ensure the combined table carries the filename. Filter to one file and confirm the row count matches that file.
Add a fifth file and refresh
Confirm it appears without any query changes.
08

Expected Result

What you should find
SituationResult
Three identical files combinedOne table, rows = the sum of all three files
A file with an extra columnThat column appears with nulls for the other files
A file with a missing columnNulls for that file's rows in the missing column
Filtering the bad file out by nameClean result, but the query silently ignores real data
Selecting only needed columns in the transformClean result, and robust — extra columns in any file are harmless
Adding a fifth file and refreshingIt appears with no query changes at all
Which of the two fixes is better?
Selecting the columns you need in the Transform Sample File, almost always. Filtering out a file by name means that file's data is silently excluded, and nobody will remember why in six months. Explicitly selecting columns means the query states what it requires, tolerates extra columns, and errors loudly if a required column is genuinely missing.

That is the same fail-loudly principle from Lesson 1, applied to a different problem.
09

Common Mistakes

Editing the combined query instead of the Transform Sample File
The change works but is applied after combining, so filters throw away rows you already paid to load, and the logic is in a less discoverable place.

Fix: Edit Transform Sample File. That is what it is for.
Not filtering the folder list before combining
Excel lock files (~$…), 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.
Losing the source filename
The combined table has no way to tell you which file a row came from. When one month is wrong you have no way to isolate it.

Fix: Keep the Name column before combining. It costs one column and saves hours.
An unrepresentative sample file
The first file alphabetically is a partial month or an old format, so the shaping derived from it is wrong for everything else.

Fix: Open the Sample File query and point it at a typical file.
Scraping a web page for production reporting
The page owner redesigns and your report breaks with no warning and no explanation.

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

Professional Tip

Select columns explicitly in every folder transform
In the Transform Sample File, finish with Home › Choose Columns and pick exactly the columns you need.

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

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?

12

Practical Challenge

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.

Requirements
  • 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:

  1. Filter the folder list by Extension and by a name pattern that excludes lock files — that handles the non-data file generically.
  2. In the Transform Sample File, use Choose Columns to select exactly the columns you require — that handles the extra column.
  3. 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.
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 solution

1 · Filter the folder list generically

Power Query M · Folder-stage filtering
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
    Recent

Note 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:

Power Query M · Explicit column contract in Transform Sample File
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
    Typed
MissingField.Error is the important part
Table.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 itWhyMitigation
A required column renamed in a future exportTable.SelectColumns matches on name, so a rename reads as a missing columnCorrect 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 encodingThe transform hard-codes bothRare in practice within one folder. If it happens, the file almost certainly belongs to a different pipeline
The sample file being deletedThe Sample File query points at a specific filePoint the sample at a file you control, or accept that it re-samples
The folder path changingHard-coded pathAlready mitigated here — DataFolder is a parameter
A file with a genuinely different date formatThe locale is fixed at en-GB in the type stepThis 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.

14

Lesson Summary

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

FolderWeb dataCombine Files patternWeb scraping caveats
15

Progress

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

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