Поделиться через

Функции преобразования в Power Query для первичной обработки данных

  • Статья

ОБЛАСТЬ ПРИМЕНЕНИЯ: Фабрика данных Azure Azure Synapse Analytics

Совет

Попробуйте использовать фабрику данных в Microsoft Fabric, решение для аналитики с одним интерфейсом для предприятий. Microsoft Fabric охватывает все, от перемещения данных до обработки и анализа данных в режиме реального времени, бизнес-аналитики и отчетности. Узнайте, как бесплатно запустить новую пробную версию !

Первичная обработка данных в Фабрике данных Azure позволяет выполнять гибкую подготовку данных без использования кода и первичную обработку в масштабе облака, преобразуя скрипты M Power Query в скрипт Потока данных. Фабрика данных Azure интегрируется с Power Query Online и делает функции M Power Query доступными для первичной обработки данных через выполнение Spark с использованием инфраструктуры Spark потока данных.

В настоящее время для первичной обработки данных поддерживаются не все функции Power Query M, хотя они все доступны при разработке. Если функция не поддерживается, то при создании гибридных веб-приложений отобразится следующее сообщение об ошибке:

UserQuery : Expression.Error: The transformation logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out.

Ниже приведен список поддерживаемых функций M Power Query.

Управление столбцами

Фильтрация строк

Используйте функцию M Table.SelectRows для фильтрации по следующим условиям:

  • равенство и неравенство;
  • сравнения чисел, текста и дат (но не даты и времени);
  • числовые данные, например Number.IsEven/Odd;
  • вложение текста с помощью Text.Contains, Text.StartsWith, или Text.EndsWith;
  • диапазоны дат, в том числе все функции даты "IsIn");
  • сочетания этих операторов с операторами AND, OR и NOT.

Добавление и преобразование столбцов

Следующие функции M добавляют или преобразовывают столбцы: Table.AddColumn, Table.TransformColumns, Table.ReplaceValue, Table.DuplicateColumn. Ниже приведены поддерживаемые функции преобразования.

Слияние и соединение таблиц

  • Power Query создаст вложенное соединение (Table.NestedJoin; кроме того, пользователи могут вручную написать Table.AddJoinColumn). Затем пользователи должны развернуть столбец вложенного соединения в соединении без вложения (Table.ExpandTableColumn, не поддерживается в другом контексте).
  • Функцию M Table.Join можно включить напрямую, чтобы избежать необходимости в дополнительном шаге расширения, но тогда пользователю придется самостоятельно обеспечить отсутствие повторяющихся имен столбцов среди соединенных таблиц.
  • Поддерживаемые типы соединений: Inner, LeftOuter, RightOuter, FullOuter
  • В качестве функций сравнения для проверки равенства поддерживаются обе функции Value.Equals и Value.NullableEquals

Группировать по

Используйте функцию Table.Group для выполнения статистического вычисления значений.

Сортировка

Используйте функцию Table.Sort для сортировки значений.

Сокращение количества строк

Сохранить и удалить верхние строки, сохранить диапазон строк (соответствующие функции M, поддерживающие подсчеты, но не условия: Table.FirstN, Table.Skip, Table.RemoveFirstN, Table.Range, Table.MinN, Table.MaxN)

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

Function Состояние
Table.PromoteHeaders Не поддерживается. Тот же результат можно получить, задав в наборе данных значение "Первая строка в качестве заголовка".
Table.CombineColumns Это распространенный сценарий, который не поддерживается напрямую, но может быть реализован путем добавления нового столбца, который объединяет два заданных столбца. Например, Table.AddColumn(RemoveEmailColumn, "Name", каждый столбец [FirstName] & " " & [LastName])
Table.TransformColumnTypes В большинстве случаев эта функция поддерживается. Следующие сценарии не поддерживаются: преобразование строки в тип валюты, преобразование строки в тип времени, преобразование строки в тип времени, преобразование строки в тип "Процент" и обход с языковым стандартом.
Table.NestedJoin Если просто выполнить объединение, возникнет ошибка проверки. Чтобы функция работала, столбцы должны быть расширены.
Table.RemoveLastN Удаление нижних строк не поддерживается.
Table.RowCount Эта функция поддерживается, но ее можно реализовать путем добавления пользовательского столбца, содержащего значение 1, а затем статистического выражения этого столбца с помощью функции List.Sum. Поддерживается функция Table.Group.
Обработка ошибок на уровне строк Обработка ошибок на уровне строк в настоящее время не поддерживается. Например, чтобы отфильтровать нечисловые значения из столбца, можно преобразовать текстовый столбец в число. Все ячейки, для которых не удалось выполнить преобразование, будут находиться в состоянии ошибки и их нужно будет отфильтровать. Этот сценарий невозможен в функции M с горизонтальным увеличением масштаба.
Table.Transpose Не поддерживается

Способы обхода сценариев M

SplitColumn

Ниже описан альтернативный способ разбиения по длине и положению

  • Table.AddColumn(Source, "первые символы", каждый Text.Start([Email], 7), введите текст)
  • Table.AddColumn(#"вставленные первые символы", "диапазон текста", каждый Text.Middle([Email], 4, 9), введите текст)

Этот параметр доступен в параметре «Извлечь» на ленте

Расширенный запрос на добавление столбца

Table.CombineColumns

  • Table.AddColumn(RemoveEmailColumn, "имя", каждый [FirstName] & " " & [LastName])

сводными таблицами;

  • Выберите преобразование Pivot в редакторе PQ и выберите столбец для сведения.

Общие сведения о Power Query

  • Затем выберите столбец значения и агрегатную функцию.

Селектор селектора Pivot Power Query

  • После нажатия кнопки ОК данные в редакторе обновятся с учетом сведенных значений.
  • Вы также увидите предупреждающее сообщение о том, что преобразование может не поддерживаться.
  • Чтобы устранить это предупреждение, раскройте сведенный список вручную в редакторе PQ.
  • Выберите вариант "Расширенный редактор" на ленте.
  • Развертывание списка сведенных значений вручную.
  • Замените List.Distinct() списком значений в таком формате:
#"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type 1", {{"genres", type text}}), {"Drama", "Horror", "Comedy", "Musical", "Documentary"}, "genres", "Rating", List.Average)
in
  #"Pivoted column"

Форматирование столбцов даты и времени

Чтобы задать формат даты и времени при использовании Power Query ADF, используйте следующие наборы.

Тип изменения Power Query

  1. Выберите столбец в пользовательском интерфейсе Power Query и щелкните Изменить тип > Дата и время.
  2. Вы увидите предупреждающее сообщение.
  3. Откройте Расширенный редактор и измените TransformColumnTypes на TransformColumns. Укажите формат, язык и региональные параметры на основе входных данных.

Редактор Power Query

#"Changed column type 1" = Table.TransformColumns(#"Duplicated column", {{"start - Copy", each DateTime.FromText(_, [Format = "yyyy-MM-dd HH:mm:ss", Culture = "en-us"]), type datetime}})

Связанный контент

Узнайте, как создать первичную обработку данных Power Query в ADF.