Advanced Power Query Automation: Folder Drop, Parameters, Error Logging, and Folding Optimization
Learn an advanced Power Query design that automatically consolidates data simply by adding files to a folder. This guide provides practical templates and M code for staging queries, parameters, custom functions, error logging, and Query Folding optimization.
Quick Fix: Automated Consolidation in 10 Minutes
- Prepare a folder, then use Data > From Folder to combine files.
- Apply Trim, data types, and splits in the sample file transformation.
- Summarize with Group By, then select Close & Load.
Architecture
- Staging → Parameters → Custom Function → Error Logging
- Maintain Query Folding: apply filters and data types early, and derived columns later.
Practical Example
Parameters
- pFolder: D:Datasales2025-09
- pMinDate: 2025-09-01
- pRevenueFloor: 0
fnCleanCsv (Custom Function)
(folder as text, name as text) as table =>
let
src = Csv.Document(File.Contents(folder & "" & name), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
head = Table.PromoteHeaders(src, [PromoteAllScalars=true]),
keep = Table.SelectColumns(head, {"order_date","category","item","qty","revenue"}),
types = Table.TransformColumnTypes(keep,{{"order_date", type date},{"category", type text},{"item", type text},{"qty", Int64.Type},{"revenue", Int64.Type}}),
trimmed = Table.TransformColumns(types,{{"category", Text.Trim, type text},{"item", Text.Trim, type text}})
in
trimmedOrder Consolidation + Error Logging
let
folder = pFolder,
files = stg_Files,
Applied = Table.AddColumn(files, "TryTable", each try fnCleanCsv(folder, [Name])),
Success = Table.SelectRows(Applied, each [TryTable][HasError] = false),
Failure = Table.SelectRows(Applied, each [TryTable][HasError] = true),
Expanded = Table.ExpandTableColumn(Success, "TryTable", {"Value"}, {"Data"}),
Combined = Table.Combine(Expanded[Data]),
Filtered = Table.SelectRows(Combined, each [order_date] >= pMinDate and [revenue] >= pRevenueFloor),
Final = Table.TransformColumnTypes(Filtered, {{"order_date", type date},{"qty", Int64.Type},{"revenue", Int64.Type}})
in
FinalError Log
let
folder = pFolder,
files = stg_Files,
Applied = Table.AddColumn(files, "TryTable", each try fnCleanCsv(folder, [Name])),
Failure = Table.SelectRows(Applied, each [TryTable][HasError] = true),
Log = Table.TransformColumns(Table.AddColumn(Failure, "ErrorMessage", each [TryTable][Error][Message]), {{"ErrorMessage", Text.From, type text}}),
KeepCols = Table.SelectColumns(Log, {"Name","ErrorMessage"})
in
KeepColsPerformance Tips
- Apply filters and data types early to maintain folding.
- Keep only the columns you need and minimize steps.
- Use Buffer only as a last resort.
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| New files are not included | Path or pattern mismatch | Check the pFolder/StartsWith rule. |
| Dates are recognized as text | Locale | Use type date or Date.FromText. |
| Total is 0 | Numbers are stored as text | Set the data type to Int64.Type. |
| Slow refresh | Folding is broken | Move filters earlier, streamline steps, and minimize Buffer. |
| Error rows are mixed in | Exceptions are not handled | Use try … otherwise to separate q_Errors. |
Conclusion
You can complete advanced Power Query automation with folder drop, parameters, error logging, and Folding optimization. The next article continues with a pipeline that connects Power Query output to dynamic arrays and charts.