I'm a CPA: How to Merge Large Datasets in Power Query (Replace XLOOKUP)
I'm a CPA, and in this Power Query tutorial, I'll show you how to merge large datasets in Excel Power Query — replacing XLOOKUP with a faster, fully automated workflow.
If you're still using XLOOKUP to combine sales data with customer, product, and calendar tables, you're probably dealing with massive file sizes, slow load times, and formulas that break when data changes. Power Query's Merge Queries feature solves all of that — and once it's set up, it refreshes automatically every time you load new data.
In this Excel Power Query tutorial, you'll learn:
• Why XLOOKUP becomes inefficient on large datasets (10,000+ rows)
• How to load multiple Excel tables into Power Query
• How to merge Sales data with Customers, Products, and Calendar tables using common keys
• How to create summary tables using Group By (no pivot tables needed)
• How to use Reference queries to build summary outputs
• How to load everything back to Excel with Close & Load
This Power Query merge technique is essential for finance professionals working with growing datasets. Whether you're in FP&A, financial analysis, or business intelligence, replacing XLOOKUP with Power Query will reduce your file size, speed up your reports, and automate your data transformation workflow.
📊 Download the practice file: https://docs.google.com/spreadsheets/...
⏱️ Chapters:
0:00 — Why XLOOKUP fails on large datasets
0:40 — Benefits of Power Query for data merging
1:15 — Loading data into Power Query
2:24 — Merge #1: Sales + Customers (Customer Code)
3:29 — Merge #2: Sales + Products (Product Code)
4:00 — Merge #3: Sales + Calendar (Invoice Date)
4:49 — Creating a summary table (Sales By Channel)
5:30 — Group By: summing Net Sales by Year & Channel
6:04 — Why this replaces XLOOKUP for good
I'm a CPA with 20+ years in commercial finance and FP&A. On this channel, I help finance professionals learn data tools — SQL, Excel, Power Query, Power BI, and AI — without the tech jargon.
🔔 Subscribe for weekly tutorials on Power Query, Excel, and data skills for finance: / @financedatapro
Free SKOOL community - https://www.skool.com/finance-data-pr...
💬 Have you used Power Query before, or are you still relying on XLOOKUP? Let me know in the comments — I read every one.
#PowerQuery #Excel #DataAnalysis