Power Query в Power BI: подготовка данных и проверка ошибок

Обновлено 2 сентября 2026 года.

Power Query — механизм подключения и преобразования данных в Power BI, Excel и других продуктах Microsoft. Это руководство рассматривает прежде всего Power BI Desktop и режим Import: подготовленный результат загружается в семантическую модель. Для DirectQuery данные запрашиваются у источника во время работы отчёта, а допустимые преобразования ограничены. Общие навыки M полезны в разных продуктах, но коннекторы, аутентификация, интерфейс и возможности обновления могут различаться.

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

Под капотом каждый шаг — это выражение на функциональном языке 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). Доступны коннекторы к файлам, базам данных и сервисам. Для API нужно отдельно учитывать аутентификацию, пагинацию и ограничения частоты запросов. Поддержка источника и способ подключения проверяются в каталоге коннекторов Microsoft. Не каждое преобразование транслируется в запрос к источнику.

Для поддерживаемых источников используйте свёртку запроса (query folding): часть преобразований выполняется на стороне источника. Это зависит от коннектора и конкретных шагов, а не только от выбора таблицы. Подключение к SQL Server само по себе не гарантирует полную свёртку.

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

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

Настраиваемые столбцы и обычные преобразования Power Query задаются на M. В Power BI Desktop также есть шаги выполнения R и Python. Они требуют установленной среды, имеют отдельные условия обновления в сервисе и не являются универсальной возможностью каждого продукта с Power Query. Подробнее — в документации по Python в редакторе запросов.

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

В режиме Import нажмите «Закрыть и применить» (Close & Apply), чтобы применить изменения и загрузить результат в модель. У промежуточных запросов можно отключить «Включить загрузку» (Enable load). Это исключает их отдельные таблицы из модели, но не отменяет вычисления, если на них ссылаются другие запросы; ускорение обновления не гарантировано.

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

Для практики используйте генератор Power Query M из CSV и генератор диапазона дат на M.

Запрос Power Query задаётся выражением языка M. Часто используют конструкцию let ... in: в let именуют промежуточные значения, после in указывают результат. Для примера ниже нужен файл C:\Data\Продажи.xlsx, лист «Лист1» с заголовками «Дата» и «Сумма». Даты и суммы разбираются по русской локали:

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

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

Откройте «Главная» → «Расширенный редактор», чтобы увидеть M-код. Шаги, созданные кнопками, отражаются в коде. Произвольную сложную логику 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 доступен бесплатно. Для публикации в сервис нужны поддерживаемая рабочая или учебная учётная запись и соответствующие права; отдельные источники также требуют входа.
3. Откройте Power BI Desktop. На вкладке «Главная» нажмите «Получить данные», выберите источник — и при выборе «Преобразовать данные» откроется редактор Power Query.

Power BI Desktop работает в поддерживаемой среде Windows. На Mac можно использовать удалённую Windows-среду; совместимость виртуальной машины и архитектуры процессора нужно проверять отдельно. Веб-сервис Power BI имеет собственный набор возможностей и не является полной копией Desktop. Системные требования и установка.

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

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) складывает строки таблиц. В отличие от SQL UNION, оно не удаляет дубли; по этому свойству ближе к UNION ALL. Столбцы сопоставляются по именам, отсутствующие заполняются null. Поэтому одинаковая структура желательна для сопоставимых данных, но не обязательна для выполнения операции. Справка по Append.

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

Команда «Объединить запросы» находится на вкладке «Главная» в группе «Объединить». В диалоге первая выбранная таблица считается левой, вторая — правой; от их расположения зависит результат. 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. Нажмите «ОК». Появится новый столбец-таблица; кнопкой разворачивания (значок с двумя стрелками) выберите нужные поля из правой таблицы.

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

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

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

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

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

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

Table.Distinct удаляет дубли по выбранным столбцам, но в общем случае не гарантирует, какая запись останется. Оптимизация и свёртка могут изменить результат. Это прямо указано в справке Microsoft.

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

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

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

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

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

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

Условные выражения в 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 = null даёт true, а сравнения порядка вроде null > 100 дают null. Условию if требуется логическое значение, поэтому сначала обработайте отсутствие суммы: if [Сумма] = null then "Нет данных" else .... Подробности — в спецификации операторов M.

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

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() в Desktop возвращает время компьютера, а в Power Query Online — UTC. Для явного 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.Round(2.5, 0) даёт 2. Если нужны другие правила округления, задайте третий аргумент. Справка 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, а не текст «null». Однозначный вариант в M: Table.ReplaceValue(Источник, null, 0, Replacer.ReplaceValue, {"Сумма"}). Подстановка нуля допустима только если отсутствие суммы действительно означает нулевое значение.

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

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

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

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

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

На вкладке «Главная» откройте «Управление параметрами». Задайте имя, тип и текущее значение. Список предлагаемых значений — отдельная настройка, а не тип данных. Он облегчает ввод, но не заменяет проверку допустимости параметра. Не храните пароли и секреты в открытых параметрах M.

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

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

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

Параметры СтартГод и КонецГод могут менять фильтр периода. После изменения нужно выполнить запрос и обновить данные; само значение параметра не пересчитывает уже загруженную модель мгновенно. Для подключения параметров к срезам DirectQuery существует отдельный механизм с ограничениями.

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

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

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

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

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

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

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

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

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

Функцию можно написать в пустом запросе или создать из запроса с параметрами. Команда «Вызвать настраиваемую функцию» применяет её к строкам таблицы. При объединении файлов Power Query создаёт вспомогательные запросы и функцию на основе файла-примера; проверьте, что остальные файлы имеют подходящую структуру.

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

Query folding переносит поддерживаемые преобразования на источник. Это может сократить передаваемый объём, но выигрыш нужно измерять: источнику тоже нужны ресурсы. Оптимизировать следует полный путь получения данных, а не только число строк в предпросмотре.

Свёртка может быть полной, частичной или отсутствовать. Команда «Просмотреть собственный запрос» полезна для поддерживаемых коннекторов, но её недоступность сама по себе не доказывает отсутствие свёртки. Проверяйте доступные средства диагностики и фактический запрос на источнике. Механизм query folding.

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

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

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

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

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

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

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

В этом примере ошибка преобразования заменяется на null. Для рабочего отчёта сохраните также исходный текст и признак ошибки: иначе реальная проблема источника станет неотличима от отсутствующего значения. Если требуется определённая локаль, укажите её в Number.FromText.

3. Проверяйте целостность данных. Контролируйте дубли ключей и число строк после слияния. Профилирование столбцов по умолчанию охватывает первые 1 000 строк; для проверки всей таблицы переключите его на весь набор. Отсутствие ошибок в предпросмотре не доказывает отсутствие ошибок во всей выгрузке. Инструменты профилирования.

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

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

В Desktop вы проектируете запросы и запускаете обновление импортированных данных вручную. Расписание обновления семантической модели настраивается после публикации в сервис. Автоматическое обновление страницы DirectQuery — другой механизм, его не следует путать с загрузкой Import.

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

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

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

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

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

Для больших таблиц можно настроить инкрементное обновление с RangeStart и RangeEnd: политика определяет, какую историю хранить и какие разделы перечитывать. Это не автоматическое отслеживание любых изменений источника. Исправления за пределами окна обновления и физические удаления требуют отдельного решения. Проверяйте, что фильтр периода эффективно применяется на источнике. Описание incremental refresh.

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

Чтобы 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) запросы и параметры: один базовый запрос-источник и несколько производных от него. Промежуточные запросы отключайте от загрузки в модель.

Используйте понятные преобразования и проверяйте свёртку. Кнопки интерфейса создают M-код; ручное выражение не становится медленнее только потому, что его написали вручную.

Power Query, семантическая модель и DAX: в чём разница

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

Power Query (язык M)

Power Query описывает получение и преобразование данных на M. В Import результат загружается в модель при обновлении. В DirectQuery поддерживаемые преобразования входят в запросы к источнику; формула «M всегда выполняется только до загрузки» здесь недостаточна.

Семантическая модель Power BI и Power Pivot в Excel

Power Pivot — инструмент моделирования в Excel. В Power BI работают с семантической моделью: таблицами, связями и вычислениями. Импортированные данные используют колоночное хранение VertiPaq, но модель может включать и другие режимы хранения. Называть интерфейс модели Power BI отдельным компонентом Power Pivot неточно.

DAX (Data Analysis Expressions)

DAX — язык выражений для семантической модели. Меры вычисляются в контексте запроса и фильтров. Вычисляемые столбцы и таблицы имеют другую семантику, поэтому не каждое выражение DAX пересчитывается как мера при движении среза. Для DirectQuery и других режимов действуют отдельные ограничения.

Power Query готовит запросы к данным, семантическая модель определяет таблицы и связи, а DAX задаёт вычисления. Power Pivot использует сходные идеи в Excel; это не название всей модели Power BI.

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

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

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

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

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

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

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

Да. Merge соединяет таблицы по ключам, Append добавляет строки и сопоставляет столбцы по именам, сохраняя повторы. При объединении источников проверьте типы ключей, уровни конфиденциальности и доступность обоих подключений в среде обновления.

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

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

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

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

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

M описывает получение и преобразование данных; DAX — вычисления в семантической модели. Меры DAX реагируют на контекст фильтров. В Import Power Query выполняется при загрузке и обновлении, а DirectQuery использует запросы к источнику, поэтому граница «до/после загрузки» не универсальна.

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

Расписание обновления импортированных данных настраивают в сервисе после публикации, в параметрах семантической модели. Для локального источника обычно нужен шлюз. Проверяйте историю выполнения, а не только наличие расписания. Автоматическое обновление страницы DirectQuery решает другую задачу.

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

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

Контрольный пример: слияние не должно увеличивать сумму

Учебные продажи: заказ 1, товар A, сумма 100; заказ 2, товар B, сумма 200. Сумма до слияния — 300. В справочнике должны быть уникальные ключи A и B. Если B встречается дважды, левое слияние после раскрытия даст три строки с суммами 100, 200, 200, итог — 500. Это ошибка кратности соединения, которую не исправляет смена формата числа.

Перед применением запроса проверьте уникальность справочника, число строк и контрольную сумму до и после Merge. Отдельно выведите ключи без совпадений. Сохраните такую проверку вместе с запросом — она поможет обнаружить новый дубль при следующем обновлении.

Дополнительная документация: практика работы с Power Query, справочник M. Примеры с внешними файлами требуют указанных колонок и доступного пути; их нужно проверить в своей среде Power BI Desktop.