The Library · Insights
Deep dives across data, automation & AI
109 in-depth articles — from Excel foundations to production machine learning. Filter by topic or difficulty to find exactly what you need.
Power QueryPractitioner
Splitting and Processing Fixed-Width and Delimited Text Files in Power Query: Parsing Legacy Flat File Formats
Legacy flat files from mainframes, ERPs, and AS/400 systems don't cooperate with Power Query's default import wizard. This lesson teaches you how to parse fixed-width and non-standard delimited formats with full control — from raw binary loading to reusable, dynamic parsers.
21 min read
Power QueryFoundation
Navigating the M Standard Library: A Practical Reference to Built-In Functions, Namespaces, and When to Use Each
The M standard library has over 700 built-in functions — and most Power Query users never get a proper map of the territory. This lesson teaches you how the library is organized by namespace, what each major namespace covers, and how to confidently look up and use any function you encounter.
16 min read
Power QueryFoundation
Connecting to Excel Workbooks and Named Ranges in Power Query: A Practical Foundation Guide
Learn how to build reusable, refreshable data connections in Power Query — including how to connect to external Excel workbooks, named tables, and named ranges. This foundation lesson turns a two-hour monthly chore into a one-click refresh.
16 min read
Power QueryExpert
Implementing Custom Query Diagnostics and Step-Level Profiling in Power Query M
Native Query Diagnostics only goes so far. Learn how to build a full diagnostic harness directly in M that captures per-step execution time, row counts, and anomaly detection — giving you precise bottleneck identification in complex multi-step pipelines.
26 min read
Power QueryExpert
Fuzzy Matching and Probabilistic Record Linkage in Power Query: A Complete Expert Guide
Most Power Query practitioners either don't know fuzzy matching exists or apply it naively and produce garbage results. This deep-dive lesson covers how the n-gram Jaccard engine actually works, how to build multi-field probabilistic scoring with blocking for scalability, and how to validate your results with precision and recall — so you can trust what comes out.
25 min read
Power QueryPractitioner
Building a Dynamic Date Dimension Generator in Power Query M: Fiscal Periods, ISO Weeks, and Holiday Logic
Generic date tables break when fiscal years, ISO week numbering, and holiday-aware working day calculations enter the picture. This lesson walks you through building a fully parameterized Power Query M function that handles all three — with production-ready code you can deploy today.
23 min read
Power QueryPractitioner
Generating Cross-Tabulated Reports in Power Query: Pivot, Aggregate, and Shape Data for Presentation
Learn how to build production-ready cross-tabulated reports entirely in Power Query. This lesson goes beyond the Pivot Column dialog to teach you how to pre-aggregate data, add dynamic totals rows and columns, enforce column order, and handle the edge cases that break most pivot implementations.
19 min read
Power QueryFoundation
Working with Binary Data in Power Query M: Reading, Parsing, and Converting Bytes to Structured Tables
Most Power Query users never touch binary data — but when a legacy system or API throws raw bytes at you, the M language has everything you need to parse it. This lesson teaches you how to read binary files, decode typed values from byte positions, and build a complete parser that converts any structured binary format into a clean, queryable table.
16 min read
Power QueryFoundation
Filtering and Sorting Rows in Power Query: Practical Techniques for Slicing Your Data
Learn how to filter and sort rows in Power Query with genuine control — not just clicking dropdowns, but understanding the M code behind each step. This lesson uses a realistic sales dataset to teach text filters, date ranges, number conditions, multi-column sorting, and troubleshooting the mistakes that trip up beginners.
16 min read
Power QueryExpert
Implementing Custom Table.Schema Validation and Type Enforcement Pipelines in Power Query M for Robust Data Quality Contracts
Stop letting schema drift silently corrupt your Power Query reports. Learn how to build a full validation and type enforcement pipeline in M that checks column presence, type compatibility, and nullability against an explicit contract — and tells you exactly what went wrong when it doesn't match. This is the data quality architecture your Power BI models have been missing.
26 min read
Power QueryExpert
Merging Slowly Changing Dimensions in Power Query: Tracking Historical Changes with Type 1 and Type 2 SCD Patterns
Most analytics models quietly corrupt historical data by overwriting dimension attributes without any tracking strategy. This deep-dive lesson teaches you to implement both Type 1 (overwrite) and Type 2 (version) SCD merge patterns entirely in Power Query M, with complete code, realistic scenarios, and honest guidance on when the tool reaches its limits.
28 min read
Power QueryPractitioner
Implementing Custom Connectors with M Language: Building, Packaging, and Deploying .mez Extensions for Enterprise Data Sources
Most Power BI developers hit a wall when their data source isn't natively supported — and reach for workarounds that create maintenance debt. This lesson teaches you to build production-grade custom connectors from scratch using M language, covering everything from HTTP authentication and cursor pagination to schema enforcement, signing, and gateway deployment.
21 min read