Функции преобразования в 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.

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

• Выделение: Table.SelectColumns
• Удаление: Table.RemoveColumns
• Переименование: Table.RenameColumns, Table.PrefixColumns, Table.TransformColumnNames
• Изменение порядка: Table.ReorderColumns

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

Используйте функцию 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. Ниже приведены поддерживаемые функции преобразования.

• Арифметические операции
• Объединение теста
• Арифметические действия с датой и временем (арифметические операторы, Date.AddDays, Date.AddMonths, Date.AddQuarters, Date.AddWeeks, Date.AddYears)
• Продолжительность можно использовать для арифметических действий с датой и временем, но перед записью в приемник ее необходимо преобразовать в другой тип (арифметические операторы, #duration, Duration.Days, Duration.Hours, Duration.Minutes, Duration.Seconds, Duration.TotalDays, Duration.TotalHours, Duration.TotalMinutes, Duration.TotalSeconds)
• Большинство стандартных, экспоненциальных и тригонометрических числовых функций (все функции, представленные в разделе Операции, Округление и Тригонометрия, за исключением Number.Factorial, Number.Permutations, и Number.Combinations)
• Замена (Replacer.ReplaceText, Replacer.ReplaceValue, Text.Replace, Text.Remove)
• Позиционное извлечение текста (Text.PositionOf, Text.Length, Text.Start, Text.End, Text.Middle, Text.ReplaceRange, Text.RemoveRange)
• Простое форматирование текста (Text.Lower, Text.Upper, Text.Trim/Start/End, Text.PadStart/End, Text.Reverse)
• Функции даты и времени (Date.Day, Date.Month, Date.Year Time.Hour, Time.Minute, Time.Second, Date.DayOfWeek, Date.DayOfYear, Date.DaysInMonth)
• Выражения if (ветви должны иметь соответствующие типы)
• Фильтры строк в качестве логического столбца
• Числовые, текстовые, логические константы, а также константы даты и константы даты и времени

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

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

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

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

• Необходимо использовать с функцией статистического выражения
• Поддерживаемые функции статистического выражения: List.Sum, List.Count, List.Average, List.Min, List.Max, List.StandardDeviation, List.First, List.Last

Сортировка

Используйте функцию 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 и выберите столбец для сведения.

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

• После нажатия кнопки ОК данные в редакторе обновятся с учетом сведенных значений.
• Вы также увидите предупреждающее сообщение о том, что преобразование может не поддерживаться.
• Чтобы устранить это предупреждение, раскройте сведенный список вручную в редакторе 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, используйте следующие наборы.

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

#"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.