Power Query в Power BI: подробное руководство по подготовке, очистке и преобразованию данных

Power Query — это встроенный в Power BI механизм извлечения, преобразования и загрузки данных (ETL — Extract, Transform, Load). С его помощью вы подключаетесь к источникам, очищаете «сырые» данные, приводите их к нужной структуре и загружаете в модель Power BI для построения отчётов. Тот же самый движок Power Query работает в Excel (вкладка «Данные» → «Получить данные»), в потоках данных (dataflows) сервиса Power BI и в Microsoft Fabric, поэтому навыки переносятся между продуктами без изменений.

Ключевая идея Power Query в том, что все действия записываются как последовательность шагов. Вы не правите исходные данные вручную — вы описываете рецепт преобразования, который применяется заново при каждом обновлении. Подключили новый месяц продаж, нажали «Обновить» — и все шаги (удаление лишних столбцов, фильтрация, объединение таблиц, смена типов) выполняются автоматически в том же порядке. Это устраняет ручную рутину и делает подготовку данных воспроизводимой.

Под капотом каждый шаг — это выражение на функциональном языке M (Power Query M formula language). Визуальный редактор генерирует код M за вас, но вы в любой момент можете открыть «Расширенный редактор» (Advanced Editor) и посмотреть или поправить его вручную. Такое сочетание графического интерфейса и читаемого кода делает Power Query доступным новичку и мощным для эксперта.

Power Query: основы работы в Power BI

Редактор Power Query (Power Query Editor) открывается из Power BI Desktop кнопкой «Преобразовать данные» (Transform Data) на вкладке «Главная» либо сразу после подключения к источнику, когда вы выбираете «Преобразовать данные» вместо «Загрузить». Интерфейс состоит из четырёх частей: список запросов слева, лента инструментов сверху, область предпросмотра данных в центре и панель «Примененные шаги» (Applied Steps) справа.

Панель «Примененные шаги» — главный инструмент контроля. Каждая операция добавляет в неё именованный шаг: «Источник», «Навигация», «Измененный тип», «Удаленные столбцы» и так далее. По шагам можно перемещаться вперёд и назад, чтобы увидеть состояние данных на любом этапе, переименовывать их для читаемости, удалять или вставлять новые между существующими. Это превращает преобразование в прозрачный, отлаживаемый процесс.

Получение данных с помощью Power Query

Работа начинается с подключения к источнику через окно «Получить данные» (Get Data). Power Query содержит сотни готовых коннекторов: файлы (Excel, CSV, JSON, XML, папка), базы данных (SQL Server, PostgreSQL, Oracle, MySQL, Snowflake), онлайн-сервисы (SharePoint, Dynamics 365, Salesforce), веб-страницы и REST/OData API. Знать SQL для подключения не обязательно — большинство трансформаций описывается мышью, а Power Query сам формирует запрос к источнику на его «родном» языке.

При подключении к структурированным источникам (реляционные БД, OData) важно подключаться напрямую к таблицам, а не загружать всё разом: тогда работает механизм свёртки запроса (см. раздел об оптимизации), и фильтрация выполняется на стороне сервера.

Преобразование данных с помощью Power Query

После подключения данные открываются в редакторе, где доступен полный набор операций: фильтрация и сортировка строк, изменение типов столбцов, удаление дубликатов, разделение и объединение столбцов, замена значений, группировка, разворачивание и сворачивание (pivot/unpivot), объединение и присоединение таблиц. Каждая операция — кнопка на ленте, за которой стоит конкретная M-функция, например Table.SelectRows для фильтра или Table.RemoveColumns для удаления столбцов.

Для нестандартной логики используется команда «Настраиваемый столбец» (Custom Column), где вы пишете выражение на языке M. Если же требуется статистика или продвинутая обработка, Power Query умеет выполнять шаги на R и Python через команды «Запустить скрипт R» и «Запустить скрипт Python» — это отдельные шаги преобразования, а не замена M. Сами столбцы и базовые трансформации всегда описываются на M.

Загрузка данных в Power BI

Когда таблица приведена в порядок, нажмите «Закрыть и применить» (Close & Apply) на вкладке «Главная». Power Query выполнит весь рецепт шагов и загрузит результат в модель данных Power BI, где над ним уже работают меры DAX и визуализации. Для промежуточных (служебных) запросов, которые не должны попадать в модель, в контекстном меню запроса отключают параметр «Включить загрузку» (Enable load) — это экономит память и ускоряет обновление.

Что такое язык M и как он работает в Power Query

Каждый запрос Power Query — это программа на языке M. Её структура — конструкция let ... in: в блоке let перечислены именованные шаги (это и есть «Примененные шаги»), а после in указано, какой шаг вернуть как результат. Минимальный пример:

let Источник = Excel.Workbook(File.Contents("C:\Data\Продажи.xlsx")), Лист = Источник{[Item="Лист1",Kind="Sheet"]}[Data], ПовышениеЗаголовков = Table.PromoteHeaders(Лист), ИзмененныйТип = Table.TransformColumnTypes(ПовышениеЗаголовков, {{"Дата", type date}, {"Сумма", Currency.Type}}) in ИзмененныйТип

Важные особенности M: язык чувствителен к регистру (Table.SelectRows и table.selectrows — разные вещи), а имена шагов с пробелами заключаются в кавычки с решёткой: #"Измененный тип". Каждый следующий шаг ссылается на предыдущий по имени, поэтому запрос — это цепочка преобразований, где результат одного шага становится входом следующего.

Открыть код целиком можно через «Главная» → «Расширенный редактор». Любое изменение в визуальном интерфейсе тут же отражается в M-коде, и наоборот. Понимание этой связи — ключ к отладке: если шаг ведёт себя не так, как ожидалось, его выражение всегда можно прочитать и поправить.

Установка и запуск Power Query в Power BI

Отдельно устанавливать Power Query не нужно — он входит в состав Power BI Desktop. Достаточно поставить сам Power BI Desktop, и редактор Power Query будет доступен из коробки.

1. Установите Power BI Desktop одним из способов: из Microsoft Store (рекомендуется — приложение обновляется автоматически) либо скачайте установщик со страницы продукта на сайте Microsoft (powerbi.microsoft.com).
2. Запустите установку и следуйте указаниям мастера. Power BI Desktop бесплатен; учётная запись Microsoft нужна только для публикации отчётов в сервис.
3. Откройте Power BI Desktop. На вкладке «Главная» нажмите «Получить данные», выберите источник — и при выборе «Преобразовать данные» откроется редактор Power Query.

Тот же движок доступен в Excel для Windows (вкладка «Данные» → «Получить данные»), поэтому при необходимости запросы можно разрабатывать и там. Учтите системные требования: Power BI Desktop работает только в Windows; на macOS его запускают через виртуальную машину или используют веб-версию сервиса Power BI.

Подключение источника и первичная настройка

1. Нажмите «Получить данные» и выберите тип источника, например «Excel» или «SQL Server».
2. Укажите параметры подключения (путь к файлу или имя сервера и базы) и при необходимости способ аутентификации.
3. В окне «Навигатор» отметьте нужные таблицы или листы и нажмите «Преобразовать данные», чтобы открыть редактор (а не «Загрузить», если данные требуют очистки).
4. Выполните преобразования и нажмите «Закрыть и применить».

Power BI запоминает учётные данные для каждого источника отдельно. Изменить или сбросить их можно через «Файл» → «Параметры и настройки» → «Параметры источника данных».

Импорт данных из разных источников

Power Query поддерживает большинство популярных форматов и систем. Ниже — типичные категории источников:

Чтобы импортировать данные, выберите источник через «Получить данные», задайте параметры подключения, отметьте таблицы в «Навигаторе», при необходимости выполните очистку в редакторе и нажмите «Закрыть и применить». Отдельный мощный сценарий — коннектор «Папка»: он позволяет за один запрос объединить десятки однотипных файлов (например, выгрузки за каждый месяц) в одну таблицу с помощью функции «Объединить файлы».

Фильтрация данных в Power Query

Фильтрация отсекает ненужные строки как можно раньше — это и упрощает данные, и ускоряет работу, особенно если фильтр сворачивается в запрос к базе данных. Фильтр задаётся через выпадающее меню в заголовке столбца или командами на ленте; в коде ему соответствует функция Table.SelectRows.

Фильтрация по значениям

Самый простой вариант — выбрать конкретные значения из списка. Нажмите на стрелку в заголовке столбца «Город» и снимите флажки со всех значений, кроме «Москва» — останутся только московские записи. Этот способ удобен, когда набор допустимых значений невелик и заранее известен.

Фильтрация по условию

Для числовых, текстовых и дат доступны фильтры по условию: «равно», «не равно», «больше», «меньше», «между», «начинается с», «содержит». Например, чтобы оставить клиентов старше 30 лет, выберите «Числовые фильтры» → «Больше» и введите 30. Условия можно комбинировать логическими «И»/«ИЛИ». В M это выглядит так:

= Table.SelectRows(Источник, each [Возраст] > 30 and [Город] = "Москва")

Фильтрация по текстовому шаблону

Текстовые фильтры «начинается с», «заканчивается на» и «содержит» опираются на функции Text.StartsWith, Text.EndsWith и Text.Contains. Чтобы оставить строки, где «Название» начинается с буквы «А»:

= Table.SelectRows(Источник, each Text.StartsWith([Название], "А"))

Совет: фильтруйте на ранних шагах и по возможности по столбцам, поддерживающим свёртку. Тогда из базы данных приедет только нужное подмножество строк, а не вся таблица.

Изменение структуры и типов данных

Перед анализом данные почти всегда нужно реструктурировать. Базовые операции редактора:

• Изменение типа данных: задайте корректный тип каждому столбцу — текст, целое число, десятичное, валюта, дата, дата/время, логический. Тип влияет и на доступные операции, и на корректность вычислений в модели. Рекомендуется делать это явным шагом, а не полагаться на автоопределение.
• Добавление столбцов: создавайте вычисляемые столбцы через «Настраиваемый столбец» (M-выражение), «Столбец по примеру» (Power Query угадывает логику по введённым образцам) или «Условный столбец» (визуальный конструктор if-then-else).
• Удаление столбцов: убирайте лишние поля как можно раньше — это уменьшает объём данных и нередко улучшает свёртку запроса.
• Замена значений и очистка текста: исправляйте опечатки и приводите данные к единому виду через «Заменить значения», а также функции «Усечь» (Trim) и «Очистить» (Clean), удаляющие лишние пробелы и непечатаемые символы.
• Разворот и сворачивание: операции Pivot/Unpivot переводят данные между «широким» и «узким» форматами. Сворачивание столбцов (Unpivot) — частый приём приведения отчётной таблицы с месяцами по столбцам к нормализованному виду, удобному для модели.

Практический совет: держите изменение типов в одном итоговом шаге ближе к концу запроса. Многократные мелкие переопределения типов засоряют список шагов и могут мешать свёртке.

Объединение таблиц: слияние (Merge) и присоединение (Append)

В Power Query есть две принципиально разные операции объединения, и их важно не путать:

• Слияние запросов (Merge) — объединение «по горизонтали»: к строкам левой таблицы подтягиваются столбцы из правой по совпадению ключевых полей. Это аналог JOIN в SQL.
• Присоединение запросов (Append) — объединение «по вертикали»: строки нескольких таблиц с одинаковой структурой складываются в одну (как UNION в SQL). Используется, например, чтобы собрать продажи за разные месяцы в единую таблицу.

Типы соединения при слиянии

Команда «Объединить запросы» находится на вкладке «Главная» в группе «Объединить». В диалоге первая выбранная таблица считается левой, вторая — правой; от их расположения зависит результат. Power Query поддерживает шесть типов соединения:

• Внутреннее (Inner join): только строки, совпавшие в обеих таблицах.
• Левое внешнее (Left outer join): все строки левой таблицы плюс совпавшие из правой. Тип по умолчанию и самый частый на практике.
• Правое внешнее (Right outer join): все строки правой таблицы плюс совпавшие из левой.
• Полное внешнее (Full outer join): все строки из обеих таблиц; недостающие значения заполняются null.
• Левое антисоединение (Left anti join): только те строки левой таблицы, которым нет пары в правой. Удобно для поиска «осиротевших» записей.
• Правое антисоединение (Right anti join): только строки правой таблицы без пары в левой.

Как выполнить слияние

1. Откройте редактор Power Query и выберите левую таблицу.
2. На вкладке «Главная» нажмите «Объединить запросы» (или «Объединить запросы как новые», чтобы получить отдельный запрос-результат).
3. В диалоге укажите правую таблицу и выделите столбцы-ключи в обеих таблицах. Для составного ключа выбирайте несколько столбцов с зажатым Ctrl — порядок выбора в обеих таблицах должен совпадать.
4. Выберите тип соединения. Под диалогом Power Query покажет оценку числа совпадений — это помогает заранее заметить проблему с ключами.
5. Нажмите «ОК». Появится новый столбец-таблица; кнопкой разворачивания (значок с двумя стрелками) выберите нужные поля из правой таблицы.

Важно: столбцы-ключи должны быть одного типа данных (например, оба целочисленные), иначе совпадения не найдутся. Если точных совпадений нет из-за разного написания, включите «нечёткое сопоставление» (fuzzy matching) — оно работает только для текстовых столбцов.

Агрегация данных: операция Group By

Группировка сворачивает строки в сводку по одному или нескольким полям с применением агрегатных функций. В коде ей соответствует Table.Group. Например, можно сгруппировать продажи по категории товара и получить сумму выручки и среднюю цену по каждой категории.

1. В редакторе выберите таблицу и на вкладке «Преобразование» (или «Главная») нажмите «Группировать по» (Group By).
2. Укажите поля группировки. Кнопкой «Дополнительно» можно задать несколько полей сразу.
3. Задайте агрегаты: «Сумма», «Среднее», «Минимум», «Максимум», «Количество строк», «Количество различных строк». Можно добавить несколько агрегатов одновременно.
4. Дайте осмысленные имена новым столбцам и нажмите «ОК».

Совет по производительности: при работе с базой данных группировка часто сворачивается в SQL с GROUP BY, и сервер возвращает уже агрегированный, компактный результат — это значительно быстрее, чем тянуть все строки в Power BI.

Удаление дубликатов

Команда «Удалить дубликаты» (Remove Duplicates) убирает повторяющиеся строки; в коде это Table.Distinct. Ключевой момент: дубликаты определяются по выбранным столбцам, а из каждой группы повторов сохраняется первая встретившаяся строка — поэтому порядок строк перед удалением имеет значение.

По одному столбцу

1. Выделите столбец, по которому нужно убрать повторы.
2. На вкладке «Главная» нажмите «Удалить строки» → «Удалить дубликаты» (либо ту же команду в контекстном меню столбца).

Останутся строки с уникальными значениями выбранного столбца.

По нескольким столбцам

1. Выделите несколько столбцов с зажатым Ctrl — комбинация их значений определит уникальность строки.
2. Нажмите «Удалить строки» → «Удалить дубликаты».

Если важно сохранить конкретную запись из группы дубликатов (например, самую свежую), сначала отсортируйте таблицу так, чтобы нужная строка оказалась первой, и только потом удаляйте дубликаты.

Условные выражения в Power Query

Условная логика реализуется конструкцией if ... then ... else. В отличие от Excel, ключевые слова в M пишутся строчными буквами и являются обязательными — у if всегда должна быть ветка else. Проще всего создать условие через «Добавление столбца» → «Условный столбец»; для сложной логики используют «Настраиваемый столбец».

Простое условие — пометка «Да»/«Нет»:

= Table.AddColumn(Источник, "Крупный заказ", each if [Сумма] > 100 then "Да" else "Нет")

Несколько уровней через else if:

= Table.AddColumn(Источник, "Категория", each if [Сумма] > 100 then "Больше 100" else if [Сумма] > 50 then "От 50 до 100" else "50 и меньше")

Комбинирование условий логическими операторами and и or:

= Table.AddColumn(Источник, "Диапазон", each if [Сумма] > 100 and [Сумма] < 200 then "От 100 до 200" else "Вне диапазона")

Помните о null: если в столбце возможны пустые значения, сравнение с ними даёт null, а не false. Чтобы корректно их обработать, добавляйте явную проверку, например if [Сумма] = null then "Нет данных" else ....

Работа с датами и временем

Power Query содержит обширную библиотеку функций для дат — категории Date, Time, DateTime и Duration. Большинство операций доступно и через ленту: вкладка «Преобразование» → «Дата» и «Время» позволяют извлекать год, месяц, день, номер недели, день недели без написания кода. Полезные функции M:

• Date.FromText("2026-06-26") — преобразует текст в дату (для надёжного разбора лучше указывать культуру/формат).
• Date.AddDays([Дата], 30) — прибавляет (или, с отрицательным аргументом, вычитает) дни; аналогично есть Date.AddMonths и Date.AddYears.
• Date.Year([Дата]), Date.Month([Дата]), Date.Day([Дата]) — извлекают компоненты даты.
• DateTime.LocalNow() — текущие дата и время в локальном часовом поясе; для UTC используйте DateTimeZone.UtcNow().
• Date.IsLeapYear([Дата]) — проверяет, високосный ли год.
• DateTime.ToText([Поле], "dd.MM.yyyy HH:mm") — форматирует дату и время в текст по заданному шаблону.

Частая ошибка — разные форматы дат в исходных файлах (например, дд.мм.гггг и мм/дд/гггг). Чтобы избежать неверного разбора, при смене типа указывайте локаль («Изменить тип» → «С использованием локали») вместо автоматического определения.

Преобразование текстовых данных

Текстовые поля часто требуют чистки и реструктуризации. Основные приёмы:

1. Разделение текста

Команда «Разделить столбец» (Split Column) разбивает значения по разделителю (запятая, точка с запятой, пробел), по числу символов или по переходу регистра. Например, поле «ФИО» можно разделить по пробелу на отдельные столбцы. В коде используется Table.SplitColumn.

2. Объединение текста

Обратная операция — «Объединить столбцы» (Merge Columns): несколько полей сводятся в одно с заданным разделителем. Так из «Имя» и «Фамилия» получают «Полное имя». Соответствующая функция — Text.Combine внутри Table.CombineColumns.

3. Изменение регистра

Для смены регистра в M используются функции Text.Lower (нижний регистр), Text.Upper (верхний) и Text.Proper (каждое слово с заглавной буквы). Обратите внимание: правильные имена — именно Text.Lower/Text.Upper, а не «Text.ToLower». Пример приведения к нижнему регистру:

= Table.TransformColumns(Источник, {{"Email", Text.Lower}})

Те же действия доступны без кода: вкладка «Преобразование» → «Формат» → «нижний регистр / ВЕРХНИЙ РЕГИСТР / Каждое Слово С Прописной». Здесь же находятся «Усечь» (убрать пробелы по краям) и «Очистить» (удалить непечатаемые символы) — их стоит применять к данным, выгруженным из внешних систем.

Преобразование числовых данных

Числовые поля часто требуют округления и форматирования.

Округление

Функция Number.Round округляет до заданного числа знаков. Чтобы округлить «Цена» до двух знаков, добавьте настраиваемый столбец:

Есть и родственные функции: Number.RoundUp и Number.RoundDown для округления всегда вверх или вниз.

Форматирование чисел

Функция Number.ToText переводит число в текст по стандартному числовому формату .NET. Чтобы показать сумму с разделителем тысяч и двумя знаками после запятой, используйте формат "N2":

Важный нюанс: Number.ToText возвращает текст, поэтому такой столбец годится для подписей, но не для арифметики и не для агрегатов в модели. Числовое форматирование для визуализаций лучше задавать средствами модели Power BI, оставляя в Power Query «чистые» числа.

Работа с пустыми значениями (null)

В Power Query отсутствующее значение обозначается ключевым словом null. Его важно отличать от пустой текстовой строки "" и от пробела — это разные вещи, и обрабатываются они по-разному.

Замена значений (Replace Values). Чтобы подставить вместо null конкретное значение:

1. Выберите столбец с пустыми значениями.
2. Правой кнопкой → «Заменить значения» (или вкладка «Преобразование» → «Заменить значения»).
3. В поле «Значение для поиска» введите null (через «Дополнительно» можно явно указать тип), в «Заменить на» — нужное значение.

Заполнение (Fill). Команды «Заполнить» → «Вниз» и «Вверх» копируют значение из соседней непустой ячейки в пустые. Это типичный приём для данных, выгруженных из сводных отчётов, где категория указана только в первой строке группы. В коде — Table.FillDown и Table.FillUp.

Удаление пустых строк. «Удалить строки» → «Удалить пустые строки» убирает полностью пустые записи. Чтобы отбросить строки с null в конкретном столбце, примените фильтр «не равно null».

Параметры в Power Query

Параметры — это именованные значения, которые можно подставлять в запросы вместо «зашитых» констант: путей к файлам, имён серверов, дат, значений фильтров. Они делают модель гибкой: чтобы переключить источник со «staging» на «production» или сменить отчётный год, достаточно поменять параметр, не трогая шаги запроса.

Создание параметра

На вкладке «Главная» нажмите «Управление параметрами» (Manage Parameters) → «Создать». Задайте имя, тип (текст, число, дата, логический или список допустимых значений), а также текущее и, при желании, значение по умолчанию. Если указать список допустимых значений, параметр превращается в удобный выпадающий список.

Использование параметра

Параметр можно подставить в фильтр, в строку подключения к источнику или в любое M-выражение, просто сославшись на его имя. Например, фильтр по году с параметром Год:

= Table.SelectRows(Источник, each Date.Year([Дата]) = Год)

Так с двумя параметрами СтартГод и КонецГод можно ограничить выборку диапазоном лет, а меняя их значения — мгновенно перенастраивать отчёт без правки кода. Параметры также лежат в основе механизма объединения файлов и пользовательских функций (см. ниже).

Пользовательские функции в Power Query

Пользовательская функция — это переиспользуемый блок логики, который принимает входные значения и возвращает результат. Функции незаменимы, когда один и тот же набор преобразований нужно применить ко многим столбцам, файлам или таблицам.

Синтаксис функции на M

Функция в M описывается как (параметры) => выражение. Никаких «end let» в языке нет. Простейший пример — функция без параметров:

let Привет = () => "Hello World" in Привет

Функция с параметрами, например расчёт цены с НДС:

let ЦенаСНДС = (цена as number, ставка as number) as number => цена * (1 + ставка) in ЦенаСНДС

Указание типов аргументов (as number) необязательно, но повышает надёжность: Power Query сразу сообщит об ошибке при передаче значения неверного типа.

Создание функции через интерфейс

Чаще функцию создают не вручную, а из готового запроса: настраивают преобразования на одном примере, затем правой кнопкой по запросу → «Создать функцию» (Create Function). Power Query сам превратит шаги в функцию с параметрами. Вызвать её для каждой строки таблицы можно командой «Добавление столбца» → «Вызвать настраиваемую функцию» (Invoke Custom Function). Именно так под капотом работает объединение множества файлов из папки.

Оптимизация производительности и свёртка запроса

Главный механизм производительности Power Query — свёртка запроса (query folding). Это процесс, при котором Power Query транслирует ваши шаги на «родной» язык источника (например, в SQL) и поручает выполнение самому серверу, а не тянет все данные к себе. Сервер возвращает уже отфильтрованный и агрегированный результат — это кардинально быстрее, особенно на больших таблицах.

Свёртка возможна в основном для структурированных источников (SQL Server, PostgreSQL, Oracle, OData). Возможны три исхода: полная свёртка (все шаги выполнены на сервере), частичная (часть на сервере, часть в движке Power Query) и отсутствие свёртки (всё считается локально). Проверить, свёрнут ли шаг, можно через контекстное меню шага → «Просмотреть собственный запрос» (View Native Query): если пункт активен — шаг свёрнут в SQL.

Практические рекомендации:

Для глубокой диагностики в Power BI есть инструмент «Диагностика запросов» (Query Diagnostics) на вкладке «Сервис»: он записывает, какие запросы и сколько времени выполнялись, и помогает найти узкое место.

Поиск и устранение ошибок в Power Query

Ошибки в Power Query бывают двух уровней: на уровне шага (шаг целиком не выполняется — например, обращение к несуществующему столбцу) и на уровне ячейки (отдельные значения не преобразуются — скажем, текст «н/д» в числовом столбце). Ячейки с ошибкой подсвечиваются и показывают Error.

1. Локализуйте проблему по шагам. Переключайтесь по «Примененным шагам» и найдите тот, на котором появилась ошибка. Чаще всего виноваты смена типа данных и переименование/удаление столбцов, на которые ссылаются последующие шаги.

2. Обрабатывайте ошибки явно. Команда «Удалить строки» → «Удалить ошибки» убирает строки с ошибочными ячейками, а «Сохранить ошибки» — наоборот, оставляет только их для анализа. В коде для перехвата используется конструкция try ... otherwise ...:

= Table.AddColumn(Источник, "Число", each try Number.FromText([Текст]) otherwise null)

Здесь некорректные значения не ломают запрос, а заменяются на null.

3. Проверяйте целостность данных. Функции Table.Distinct (уникальные строки) и Table.SelectRows (фильтрация по условию) помогают находить дубликаты и аномалии. Профилирование данных («Вид» → «Качество столбца», «Распределение столбца») наглядно показывает долю ошибок, пустот и уникальных значений в каждом столбце.

4. Используйте диагностику. Когда штатных средств не хватает, подключайте «Диагностику запросов» для анализа производительности и порядка выполнения шагов.

Обновление данных: планирование и автоматизация

Важно понимать разделение ролей: в Power BI Desktop вы только проектируете запросы и обновляете их вручную кнопкой «Обновить». Автоматическое обновление по расписанию настраивается в облачном сервисе Power BI (app.powerbi.com) уже после публикации отчёта.

Запланированное обновление в сервисе

После публикации отчёта откройте параметры набора данных в сервисе Power BI и в разделе «Запланированное обновление» (Scheduled refresh) задайте частоту (до 8 раз в сутки на лицензии Pro и чаще на Premium/Fabric) и время обновления. Сервис будет сам пере-выполнять рецепт Power Query и обновлять данные отчёта.

Шлюз данных для локальных источников

Если источник находится в локальной сети или на вашем компьютере (файл на диске, локальный SQL Server), сервису нужен локальный шлюз данных (On-premises data gateway). Это отдельная бесплатная программа: установите её на машину с доступом к источнику, привяжите к учётной записи Power BI — и сервис сможет безопасно обращаться к данным за пределами облака по расписанию. Для облачных источников шлюз обычно не требуется.

Инкрементное обновление

Для больших таблиц вместо полной перезагрузки настраивают инкрементное обновление (incremental refresh): Power BI обновляет только свежие партиции (например, последние месяцы), а исторические данные не перечитывает. Это резко сокращает время и нагрузку и опирается на свёртку запроса по столбцу даты.

Работа с большими объёмами данных

Чтобы Power Query уверенно справлялся с миллионами строк, сочетайте несколько подходов:

• Свёртка запроса — главный приём: фильтрация и агрегация выполняются на сервере источника, в Power BI приезжает компактный результат.
• Ранняя фильтрация и удаление столбцов — отбрасывайте ненужное в самом начале запроса.
• Группировка вместо детализации — если отчёту нужны итоги, агрегируйте данные через Group By до загрузки.
• Инкрементное обновление — для постоянно растущих таблиц обновляйте только новые данные.
• Параметры диапазона — параметризуйте период, чтобы при разработке работать на подвыборке, а в продакшене — на полном объёме.

Публикация и совместное использование

Цикл работы выглядит так: вы создаёте отчёт в Power BI Desktop, настраиваете источники и преобразования в Power Query, затем кнопкой «Опубликовать» (Publish) выгружаете отчёт в рабочую область сервиса Power BI. Все шаги преобразования сохраняются вместе с набором данных и выполняются при каждом обновлении.

Если одни и те же преобразования нужны в нескольких отчётах, вынесите их в поток данных (dataflow) сервиса Power BI или в Microsoft Fabric. Поток данных — это Power Query, выполняемый в облаке: результат сохраняется централизованно, и его переиспользуют сразу несколько наборов данных, не дублируя логику очистки.

Лучшие практики работы с Power Query

1. Давайте шагам осмысленные имена. Вместо «Измененный тип1», «Фильтрованные строки2» переименовывайте шаги в понятные: «Оставлены продажи за 2026 год». Через полгода это сэкономит вам и коллегам много времени.

2. Очищайте и фильтруйте как можно раньше. Удаляйте лишние строки и столбцы в начале запроса — меньше данных проходит через последующие шаги, лучше работает свёртка.

3. Берегите свёртку запроса. Старайтесь не разрывать её без необходимости; «несворачиваемые» операции переносите ближе к концу. Проверяйте «Просмотреть собственный запрос».

4. Документируйте логику. В M поддерживаются комментарии: однострочные // ... и блочные /* ... */. Поясняйте нетривиальные шаги прямо в коде расширенного редактора.

5. Разделяйте запросы на слои. Используйте «опорные» (reference) запросы и параметры: один базовый запрос-источник и несколько производных от него. Промежуточные запросы отключайте от загрузки в модель.

6. Не злоупотребляйте настраиваемыми столбцами. Многие задачи решаются штатными командами ленты, которые лучше оптимизируются и сворачиваются, чем ручной M-код.

Power Query, Power Pivot и DAX: в чём разница

Эти три технологии Power BI решают разные задачи и дополняют друг друга. Понимание границы между ними — частый камень преткновения у новичков.

Power Query (язык M)

Работает до загрузки данных в модель. Его зона ответственности — извлечение из источников, очистка, типизация, объединение и реструктуризация. Правило памяти: «если действие нужно сделать с данными до того, как они попадут в отчёт, — это Power Query».

Power Pivot и модель данных

Power Pivot — это аналитический движок и модель данных (хранилище VertiPaq с колоночным сжатием) внутри Power BI. Здесь строятся связи между таблицами и создаётся «звёздная» схема, на которой работают визуализации.

DAX (Data Analysis Expressions)

DAX — язык формул для вычислений после загрузки, уже внутри модели: меры, вычисляемые столбцы, KPI. Он отвечает за бизнес-логику отчёта — суммы нарастающим итогом, доли, сравнения периодов. Правило: «если показатель должен пересчитываться при взаимодействии пользователя со срезами и фильтрами — это DAX, а не Power Query».

Итог: Power Query (M) готовит и формирует данные, Power Pivot хранит модель и связи, DAX вычисляет показатели поверх неё. Вместе они образуют полный конвейер аналитики в Power BI.

Экосистема и сопутствующие инструменты Power Query

Вокруг Power Query сложилась обширная экосистема — все перечисленные ниже инструменты официальные и проверяемые:

Полный справочник по языку и функциям доступен в официальной документации Microsoft Learn (разделы Power Query и Power Query M formula language) — это первоисточник, к которому стоит обращаться при сомнениях в синтаксисе или поведении функций.

Вопрос-ответ

Какой функцией в Power Query группируют данные?

Используется команда «Группировать по» (Group By) на вкладке «Преобразование», которой в коде соответствует функция Table.Group. Она объединяет строки по одному или нескольким столбцам и применяет к группам агрегаты — сумму, среднее, минимум, максимум, количество строк.

Можно ли объединять данные из нескольких источников?

Да. Для соединения таблиц по ключу (как JOIN в SQL) используется «Объединить запросы» (Merge), а для складывания строк однотипных таблиц (как UNION) — «Добавить запросы» (Append). Источники при этом могут быть разными: например, можно соединить таблицу из SQL Server со справочником из Excel.

Какими способами очистить данные в Power Query?

Доступны удаление пустых строк, удаление дубликатов (Table.Distinct), замена значений, заполнение пустот «вниз»/«вверх», усечение и очистка текста, смена типов данных, фильтрация и удаление ошибок. Большинство операций выполняется кнопками ленты без написания кода.

Чем Power Query помогает при подготовке данных для Power BI?

Power Query собирает данные из разных источников, очищает их от ошибок и пустот, приводит типы, объединяет таблицы и создаёт нужные столбцы — и всё это записывается как воспроизводимый рецепт, который применяется при каждом обновлении. На выходе модель получает чистые, структурированные данные, готовые к расчётам DAX и визуализации.

Чем язык M отличается от DAX?

M (Power Query) работает до загрузки данных и отвечает за их извлечение и преобразование. DAX работает после загрузки, внутри модели, и отвечает за вычисления — меры и показатели, которые пересчитываются при взаимодействии с фильтрами отчёта. Это два разных языка для двух разных этапов.

Как настроить автоматическое обновление данных?

В Power BI Desktop обновление только ручное (кнопка «Обновить»). Автоматическое обновление по расписанию настраивается в облачном сервисе Power BI после публикации отчёта — в разделе «Запланированное обновление» набора данных. Если источник локальный, дополнительно потребуется установить локальный шлюз данных (On-premises data gateway).

Нужно ли знать программирование, чтобы работать в Power Query?

Нет. Большинство задач решается визуальными командами ленты, а код M Power Query генерирует за вас. Знание M полезно для сложных сценариев и пользовательских функций, но не обязательно для повседневной подготовки данных.