Forum Discussion
Power Query - Large Data Set Question
Hello - I combined a handful of .xlsx files and there's about 5 million rows total. I'm trying to identify duplicate "project IDs" (the long strings below in rows 22-29), basically what this filter i...
You may hit one of the classic pain points with Excel which is 1,048,576 row limit. With 5 million rows and about 180k project IDs, you are already pushing Excel beyond what it was designed for. Power Query can crunch that much data, but once you load it back into the Excel grid, you hit the ceiling.
You may consider this as workaround:
Since you specifically want the project IDs with exactly 1 duplicate (count = 2):
- Do the grouping in Power Query.
- Filter for Count = 2.
- Load the results into the Data Model (not the sheet).
- Use a PivotTable to explore or export to CSV if you need the raw list.
This way, you bypass the worksheet limit entirely while still working inside Excel.