Power BI: память растет до 800 МБ с Excel 800 КБ

Вероятнее всего это не «фантомная» ошибка Power BI, а ожидаемое поведение движка Power Query: Excel‑источник не поддерживает query folding, а множественные подключения и последовательные merge/объединения создают промежуточные копии таблиц и многократные перечисления, что резко повышает потребление RAM. Частые причины — объединения по текстовым ключам, несовпадение типов, дублированная загрузка одного и того же файла; исправляется диагностикой (Query Diagnostics), приведением типов, объединением подключений и оптимизацией архитектуры запросов.

Содержание

• Почему Power BI / Power Query раздувает память при объединении Excel?
• Как объединения (merge) в Power Query приводят к росту памяти
• Диагностика: как найти шаг, жрущий память в Power BI / Power Query
• Быстрые исправления — что сделать прямо сейчас
• Архитектура запросов и отношения между таблицами в Power BI
• Когда это баг Power BI и как его подтвердить
• Контроль и мониторинг памяти: инструменты и метрики
• Чек‑лист: приоритетные шаги для снижения расхода памяти
• Источники
• Заключение

Почему Power BI / Power Query раздувает память при объединении Excel?

Коротко — Excel‑источник обрабатывается локально движком Mashup (Power Query) и не поддерживает query folding, поэтому все преобразования и объединения выполняются в памяти. Если у вас четыре подключения к одному/нескольким файлам и один запрос делает merge трёх других, движок может:

• загрузить каждую из четырёх «копий» в память;
• для выполнения merge создать дополнительные промежуточные таблицы (материализации/буфферы);
• многократно перечислять одну и ту же таблицу при повторных обращениях (re-enumeration).

Итог: компактный файл 800 КБ на диске может превратиться в сотни мегабайт в памяти из‑за накладных расходов на объекты, индексы, строки/столбцы и промежуточные структуры. Аналогичные случаи и объяснения см. в обсуждениях сообщества Microsoft Fabric и в анализе производительности Power Query на блоге Chris Webb: https://community.fabric.microsoft.com/t5/Power-Query/How-to-Optimize-Memory-Usage/td-p/57915 и https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-in-power-bi/.

Как объединения (merge) в Power Query приводят к дублированию и росту памяти

Немного текста о механике: при отсутствии возможности делегировать (query folding) Power Query выполняет объединения сам, локально. В зависимости от ключей и используемых шагов движок может применять разные подходы: sort‑merge, hash‑join или более дорогие стратегии (включая множественные сканы/перечисления). Объединение по текстовым колонкам чаще даёт более тяжёлую обработку, чем по целочисленным ключам — и именно поэтому преобразование ключа в число часто резко снижает нагрузку. Подробно об этом — в статье Chris Webb: https://blog.crossjoin.co.uk/2020/06/07/optimising-the-performance-of-power-query-merges-in-power-bi-part-3-table-join-and-sortmerge/

Ещё момент: Table.Buffer может предотвратить повторное вычисление запроса, но если вы «буферизуете» таблицу, она полностью кладётся в память. То есть Table.Buffer решает проблему многократного чтения ценой дополнительной RAM — и если буфер > доступной памяти, начнётся страница/пейджинг и ещё хужее поведение. См. замечания по Table.Buffer и лимитам контейнера в практике мониторинга: https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-in-power-bi/

Диагностика: как найти конкретный шаг, жрущий память в Power BI / Power Query

Что сделать по шагам, чтобы определить виновника:

1. Запустите обновление и наблюдайте за PBIDesktop.exe в Диспетчере задач / Resource Monitor — смотрите Working Set и Commit. Это даст грубую картину.
2. В Power Query Editor включите Query Diagnostics (Tools → Start Diagnostics), выполните Refresh проблемного запроса и остановите диагностику. Экспортируйте результаты — они покажут, какие шаги занимают время и сколько раз выполняются. Руководство по использованию Query Diagnostics и анализу памяти — у Chris Webb: https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-in-power-bi/
3. В Power BI Desktop используйте Performance Analyzer (View → Performance Analyzer) — он полезен для выявления медленных визуализаций и их связанных запросов.
4. Воспроизведите тест: замените Excel‑источник на источник с поддержкой folding (например, SQL) — если проблема исчезнет, это подтверждает, что причина в локальной обработке Excel/отсутствии folding (см. руководство по query folding: https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding).
5. Составьте минимальный PBIX, который воспроизводит проблему — пригодится и для тестов, и для обращения в поддержку.

Результат диагностики скажет: много коротких повторяющихся шагов → проблема с многократной оценкой; один тяжёлый шаг merge/expand → проблема именно в объединении/типах.

Быстрые исправления — что сделать прямо сейчас

Приоритетные действия (быстрые выигрыши):

• Объедините подключения к одному файлу в одно (один Source → несколько Reference), чтобы файл загружался в память один раз. Это даёт большой эффект при дублированных подключениях.
• Отключите загрузку (Disable Load / Enable Load = off) для промежуточных запросов, которые используются только для merge/подсчётов. Таким образом в модель попадёт только итог.
• Приведите ключи к целому типу перед merge (hash join быстрее, чем скан по тексту). Пример в M:

// Приведение столбца Key к целому типу
#"Changed Type" = Table.TransformColumnTypes(PreviousStep, {{"Key", Int64.Type}})

или через Number.FromText:

#"Added KeyNum" = Table.AddColumn(PreviousStep, "KeyNum", each Number.FromText([Key]), Int64.Type),
#"Removed OldKey" = Table.RemoveColumns(#"Added KeyNum",{"Key"})

• Уберите лишние столбцы и строки как можно раньше (SelectColumns, Filter) — меньше данных = меньше памяти.
• Используйте Table.Buffer только целенаправленно: буферизация убирает многократные обращения, но полностью помещает таблицу в RAM. Применяйте её только если после буфера память остаётся в пределах допустимого.
• Отключите фоновые предпросмотры/фоновую загрузку данных в настройках (Options → Data Load → отключить background preview), чтобы уменьшить параллельные нагрузочные операции. (В некоторых случаях это заметно снижает пиковые значения.)
• Если возможно, перенесите тяжёлые объединения на сторону СУБД (SQL/Views) — это вернёт нагрузку серверу и снимет с Power Query. Документация по query folding: https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding

Эти шаги обычно решают проблему разрастания памяти в 90% случаев подобных сценариев.

Архитектура запросов и отношения между таблицами в Power BI

Ваша модель: четыре подключения и один объединяющий запрос — это архитектурный риск. Подумайте о следующих принципах:

• Замените ненужные merge‑операции на модельные отношения (star schema) — часто проще держать таблицы раздельно и строить relationship в модели, а не физически объединять все данные в Power Query.
• Стейджинг: создайте «стадинг» (staging) запросы, которые готовят и минимизируют данные для модели (сжатие, минимум колонок), и затем используйте эти стейджи как источники для финальных объединений.
• Избегайте цепочек запросов, где один запрос вызывает три других и затем ещё один объединяет их — каждая такая ссылка повышает шанс многократной оценки. Лучше: один мастер‑загрузчик + reference на него.
• Кардинальность и направление связей в модели влияют на вычисления DAX и на объём загружаемых данных — держите модель понятной и неглубоко связанной, если это возможно.

Коротко: плохая архитектура запросов — частая причина проблем; отношения между таблицами сами по себе не увеличивают RAM так сильно, как ненужные merge‑операции и дублирование загрузки исходных данных.

Когда это баг Power BI и как его подтвердить

Может ли это быть багом? Да, но редко. Признаки настоящего бага:

• Поведение неконсистентно между версиями (старое PBIX работает, новая — нет).
• Вы проделали все оптимизации (приведение типов, буферизация, объединение подключений), а память всё равно «взлетает» на пустом/минимальном примере.
• Ошибки типа “access violation”/краш движка, которые не объясняются размером данных.

Как действовать:

1. Подготовьте минимальный воспроизводимый пример (PBIX) и подробное описание шагов.
2. Приложите результаты Query Diagnostics и скриншоты/логи Task Manager.
3. Опубликуйте на форуме Microsoft Fabric / Power BI Community или создайте запрос в поддержке, приложив PBIX. Примеры похожих обсуждений — https://community.fabric.microsoft.com/t5/Power-Query/High-Ram-and-CPU-Consumption-with-small-data/td-p/3333032 и https://community.fabric.microsoft.com/t5/Power-Query/How-to-Optimize-Memory-Usage/td-p/57915.
4. Если Microsoft подтвердит, они попросят PBIX и логи; без PBIX трудно доказать баг.

Контроль и мониторинг памяти: инструменты и метрики

Инструменты:

• Task Manager / Resource Monitor — быстрый взгляд на Working Set, Commit, Private Bytes.
• Performance Monitor (perfmon) — указывает системные счётчики и позволяет строить графики во время обновления.
• Query Diagnostics в Power Query — даёт детальные тайминги и список шагов. Подробный разбор мониторинга — https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-in-power-bi/.

Что смотреть: частые повторные вызовы одного и того же шага (ре‑evaluation), большие spikes при Merge/Expand, использование дискового пейджинга (paging) — это знак, что объёма RAM недостаточно и движок начинает свопить.

Чек‑лист: приоритетные шаги для снижения расхода памяти в Power Query

1. Сделать один Source для каждого файла Excel → создать Reference вместо четырёх отдельных подключений.
2. Отключить загрузку (Enable Load = off) для промежуточных запросов.
3. Перед merge привести ключи к целому типу (Int64.Type) или добавить индекс‑ключ.
4. Удалить лишние столбцы/строки ДО объединения.
5. При необходимости использовать Table.Buffer осознанно (проверить размер буфера).
6. Отключить фоновые предпросмотры/загрузки в настройках.
7. Если возможно — перенести тяжёлые объединения на СУБД (query folding).
8. Запустить Query Diagnostics и проанализировать самые дорогие шаги.
9. Если все шаги не помогают — собрать минимальный PBIX и обращаться в поддержку.

Источники

• How to Optimize Memory Usage? — Microsoft Fabric Community
• High Ram and CPU Consumption with small data — Microsoft Fabric Community
• Monitoring Power Query Memory Usage With Query Diagnostics In Power BI — Chris Webb’s BI Blog
• Optimising the performance of Power Query merges — Chris Webb’s BI Blog
• Query folding guidance in Power BI Desktop — Microsoft Docs

Заключение

Проблема 800 КБ → 800 МБ почти всегда связана с поведением Power Query при локальной обработке Excel: множественные подключения, объединения по текстовым ключам и дублированные промежуточные таблицы приводят к огромным накладным расходам памяти. Начните с диагностики (Query Diagnostics), объедините подключения, приведите ключи к числовому типу и сократите данные перед merge — эти шаги обычно решают ситуацию. Если после оптимизации пиковые значения остаются аномально высокими, подготовьте минимальный PBIX и обратитесь в поддержку с результатами диагностики.