Глава 11. Сравнение, замена, соединение, разделение

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. Подготовка данных в Power Query включает в себя несколько методов, направленных на манипулирование данными и их уточнение. Эти методы включают разделение, объединение, сравнение и замену значений для достижения желаемой структуры и качества данных.

Рис. 11.0. Краткий обзор методов, используемых для управления данными

Мои комментарии набраны с отступом.

Предыдущая глава        Содержание    Следующая глава

Скачать заметку в формате Word или pdf

В этой главе рассматриваются следующие темы:

• Ключевые концепции
• Функции сравнения (Comparers)
• Критерии сравнения
• Критерии равенства (Equation criteria)
• Заменители (Replacers)
• Пользовательские заменители (Custom replacers)
• Соединители (Combiners)
• Разделители (Splitters)
• Практические примеры

Чтобы приступить к работе, посетите репозиторий GitHub и скачайте PBIX-файл. Файл подготовлен, чтобы вы могли выполнять примеры, читая главу. Следуя инструкциям, вы приобретаете практический опыт, который закрепляет ваши знания и навыки.

Ключевые понятия

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

Вызов функции

Для эффективного использования функций вспомним, что такое вызов функции (invoke). Вызов состоит из набора круглых скобок, которые опционально могут содержать список аргументов. Вызов запускает выполнение тела функции, которое либо возвращает значение, либо вызывает ошибку. Возвращаемое значение в частности может быть новой функцией. Для более глубокого понимания функций обратитесь к главе 9 Параметры и пользовательские функции.

Самый простой способ вызвать функцию – использовать ее полное имя, после которого в круглых скобках перечислить аргументы для всех обязательных параметров. Например, функцией Text.Contains можно проверить, является ли hello частью Hello World:

Когда вызов функции создает новую функцию, процесс ее полного определения можно продолжить, добавляя выражение вызова. В следующем примере discountFunction создает функцию, которой впоследствии потребуются числовые аргументы в качестве входных данных. Полное определение функции показано на шаге Result:

Некоторые распространенные ошибки

Ниже приведены некоторые часто встречающиеся ошибки, о которых следует помнить:

• Отсутствующая закрывающая скобка. Большинство IDE (Integrated Development Environment, интегрированная среда разработки) поддерживают подсветку совпадающих скобок. Используйте это, чтобы определить местоположение отсутствующей скобки.
• Неправильное количество аргументов. Обратитесь к IntelliSense или документации по функции, чтобы убедиться, что вы передаете правильное количество аргументов.
• Несоответствие типов аргументов. Тип аргумента должен быть совместим с соответствующим типом параметра. Обратитесь к IntelliSense или документации, чтобы убедиться, что вы передаете правильный тип для каждого аргумента.
• Орфографическая ошибка в названии функции или неверный регистр букв. Используйте IntelliSense и выберите функцию из списка, чтобы избежать таких ошибок.

Замыкания

Замыкания могут показаться техническим термином, предназначенным для экспертов и разработчиков, но поверьте, они не так сложны, как кажутся. Позвольте показать, как они могут сделать ваш код умнее. Замыкание – это функция, которая может сохранять значение аргумента для параметра из внешней (родительской) области видимости, даже после завершения выполнения этой внешней функции. Эта способность запоминать отличает замыкание от обычной функции, сохраняя значение для будущих вызовов, независимо от среды, из которой вызывается замыкание. Изучите пример, а также рис. 11.1. Больше о замыканиях, области видимости, контексте и среде см. глава 7 Концепция M.

Разберем по шагам:

makeMultiplier – функция, принимающая один аргумент x. При инициализации возвращает новую функцию: (y) => x * y. Например, makeMultiplier(2) возвращает (y) => 2 * y.

multiplyBy2 и multiplyBy3 являются замыканиями, сгенерированными вызовом функции makeMultiplier с аргументами 2 и 3 соответственно. Каждое из этих замыканий является функцией, которая «запоминает» значение x с момента его создания, 2 для multiplyBy2 и 3 для multiplyBy3. Эта способность запоминать переменные из среды, в которой они были созданы, является фундаментальной концепцией замыкания.

Наконец, эти замыкания могут быть вызваны. Например, когда вызывается multiplyBy2(4), функция использует значение x, которое она запомнила (2), и значение y (4), чтобы вернуть 8. Аналогично, функция multiplyBy3(4) возвращает 12:

Рис. 11.1. Диаграмма кода запроса 11.1

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

Функции высшего порядка

Концепция функций высшего порядка, широко используется в языках программирования. M является функциональным языком и проявляет характерные черты. Например, функции считаются гражданами первого класса, они могут быть присвоены переменным, переданы в качестве аргументов другим функциям и возвращены в качестве результатов. Это позволяет манипулировать ими, как и любым другим значением. Термин функция высшего порядка используется для описания функций, которые могут принимать другие функции в качестве аргументов или возвращать функции. Например, makeMultiplier из запроса 11.1.

Многие функции стандартной библиотеки могут принимать параметр с типом функции, что делает их функциями высшего порядка. Например, List.Transform принимает в качестве параметров список и функцию преобразования. List.Transform применяет функцию преобразования к каждому элементу списка, в результате чего получается список с вновь сформированными элементами:

Как работает этот код?

Переменная defaultDiscount имеет значение 0,1 или 10%.

discountFunction принимает единственный аргумент, discountRate. Это функция, которая возвращает функцию (sales as number) в виде number => sales * (1 — discountRate).

Переменная applyDefaultDiscount – это замыкание, созданное путем вызова функции discountFunction со значением аргумента defaultDiscount. Эта функция будет применять значение defaultDiscount с момента ее создания.

Переменная myList – список, содержащий четыре значения: 100, 900, 200 и 500.

Функция List.Transform вызывает замыкание applyDefaultDiscount для каждого элемента в myList. List.Transform возвращает новый список с преобразованными значениями.

Функции могут изменять отдельные элементы списка или таблицы, а функции высшего порядка позволяют эффективно и кратко применять эти преобразования ко всем элементам. В запросе 11.2 List.Transform автоматически передает обязательный аргумент в замыкание applyDefaultDiscount во время вызова. В терминологии информатики такое поведение часто называют функцией обратного вызова (callback). Однако в документации по языку М Power Query этот процесс не обозначается конкретным термином.

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

Функция сравнения используется в качестве аргумента функции высшего порядка Text.Contains. Последняя автоматически предоставляет два параметра функции сравнения, устраняя необходимость явно определять их как аргументы. Прежде чем мы изучим различные средства сравнения, доступные в языке M, давайте рассмотрим анонимные или пользовательские функции. Они не встроены в язык M, но могут быть созданы пользователем на лету.

Анонимные функции

Анонимные функции – это безымянные функции, которые могут быть определены в других функциях. Они полезны, когда логика пользовательских функций требуется для конкретной цели и не нуждается в повторном использовании где-либо еще:

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

• Функция (x, y) => x + y требует два параметра (x, y) и применяет к ним оператор сложения, чтобы получить одно значение или вызвать ошибку.
• Функция заключена в набор круглых скобок для упорядочения приоритетов и будет вычислять выражение функции для получения значения функции.
• Добавлен оператор вызова, содержащий список аргументов (5, 3). Вызов вычисляет тело функции, чтобы получить значение 8.

В большинстве случаев вычисление функционального выражения отделено от вызова функции.

До сих пор мы затронули такие понятия, как вызов функций, замыкания и функции высшего порядка. Теперь пришло время взглянуть на то, что общего у перечисления Order.Type и функций сравнения.

Упорядочение значений

В Power Query перечисление Order.Type и функции сравнения позволяют упорядочивать данные на основе определенных критериев. Однако перечисления и функции сравнения не одно и то же:

• Перечисление определяет набор именованных значений, организуя связанные константы для удобочитаемости кода. Эти именованные значения разъясняют их назначение, упрощая процесс установки параметров или состояний в коде. Перечисления обсуждались в главе 4. Перечисление Type представляет собой предопределенный набор значений, представляющих различные типы упорядочения:

Рис. 11.1а. Указание порядка значений

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

Таким образом, функции сравнения предлагают комплексный и настраиваемый метод сравнения и сортировки значений, в то время как перечисление Order.Type позволяет легко задать направление сортировки. Несмотря на то, что оба используются для упорядочивания значений, они выполняют разные роли и не могут использоваться взаимозаменяемо в языке M.

Функции сравнения

Функции сравнения играют решающую роль в обработке данных, предоставляя средства для оценки и установления относительного порядка или определения равенства. Возьмем, например, строки Hello, hello и HELLO. Вы можете усомниться в том, что они идентичны. Тем не менее, именно функции сравнения могут диктовать правила (например, чувствительность к регистру) для интерпретации и сравнения строк.

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

В Power Query эти функции в основном используются в качестве необязательных аргументов для функций высшего порядка, для которых требуется операция сравнения. Некоторые из них перечислены здесь, но есть и другие функции, имеющие параметр comparer, equationCriteria или comparisonCriteria:

Рис. 11.1б. Функции сравнения

В языке M есть четыре функции сравнения. За исключением Comparer.Equals, все они возвращают значение функции.

Comparer.Equals

Comparer.Equals возвращает логическое значение после вычисления эквивалентности между двумя значениями. Для этой функции требуются три аргумента: компаратор (функция сравнения), управляющий сравнением, и два сравниваемых значения. В М три встроенных компаратора: Comparer.Ordinal, Comparer.OrdinalIgnoreCase и Comparer.FromCulture.

Переменная myTable возвращает таблицу из списка списков строк. Функция Table.Addcolumn добавляет столбец equivalencecheck в myTable. Ключевое слово each указывает Power Query применить следующую за ним функцию к каждой строке в myTable. Для определения равенства значений используется Comparer.FromCulture(«en-US»), который сравнит значение в столбце1 со значением в столбце2 в соответствии с правилами английского языка и региональных параметров США и выдаст логическое значение:

Рис. 11.2. Проверка эквивалентности с помощью Comparer.Equals

Comparer.Ordinal

Начнем с запроса, который иллюстрирует, как работают порядковые правила сравнения, основанные на значениях Юникода задействованных символов.

Переменная myTable преобразует список списков строк в таблицу со столбцом Value1. Функция Table.AddColumn добавляет столбец unicodeValue в myTable. Text.ToList( [Value1] ) берет текст из столбца Value1 и преобразует его в список, где каждый элемент представляет собой один символ исходного текста. Этот список символов передается в функцию List.Transform, чтобы выполнить преобразование отдельных символов. (v)=> Text.From( Character.ToNumber(v)) каждый символ (v) в списке передает в функцию Character.ToNumber(v), возвращая его Юникод (число). Text.From преобразует это число обратно в текстовое значение. После преобразования каждого символа у нас остается список текстовых значений. Text.Combine берет эти части и собирает их в одну строку, разделяя каждое значение запятой и пробелом:

Рис. 11.3. Значения Юникода для каждого символа в Value1

Первый столбец содержит текстовое значение, а второй – Юникод каждого символа строки. Благодаря этому можно установить относительное ранжирование между текстовыми значениями.

Comparer.Ordinal возвращает функцию сравнения, которая принимает два значения. Затем для сравнения применяются порядковые правила. Следующий запрос иллюстрирует, как выполняется процесс упорядочения ранжированных значений, размещая их в порядке возрастания:

Переменная myTable превращает список списков строк в таблицу со столбцами Value1 и Value2. Comparer.Ordinal присваивается переменной myComparer. sortValue2 сортирует значения столбца Value2, применяя порядковые правила описанные в запросе 11.4 для их ранжирования и упорядочивания.

Чтобы понять, как sortValue2 задает порядок элементов, мы сравним значения Value1 и Value2 в каждой строке и выведем значение ранжирования в новом столбце equivalenceCheck.

each myComparer( [Value1], [Value2] ) будет оценивать два значения в каждой строке, устанавливая относительный ранг. Возвращает целое число, указывающее относительный порядок между ними, что позволяет упорядочить их от меньшего к большему:

Рис. 11.4. Сравнение на основе порядкового правила

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

В редакторе Power Query для запроса ComparerOrdinal в области Примененные шаги выберите шаг sortValue2. Отобразится список отсортированных значений на основе входных данных из второго столбца таблицы. Выходные данные сохраняют тот же порядок, что и первый столбец таблицы, выровненные в соответствии с рангами, указанными в столбце equivalenceCheck.

Comparer.OrdinalIgnoreCase

Comparer.OrdinalIgnoreCase возвращает функцию сравнения, которая принимает два аргумента. Функция применяет правила порядкового сравнения, но игнорирует регистр символов. Функция ранжирует эти значения, определяя их относительное положение в порядке сортировки по возрастанию. Элементы с одинаковым рангом сохраняют свой первоначальный порядок:

Процесс и структура кода аналогичны структуре предыдущего примера, за исключением того, что теперь мы применяем порядковые правила, игнорируя регистр символов. Отличаются следующие фрагменты: Comparer.OrdinalIgnoreCase присваивается переменной myComparer. sortValue2 создает отсортированный список значений столбца Value2 после применения порядковых правил, игнорирующих регистр.

Рис. 11.5. Сравнение на основе порядкового правила, игнорирующего регистр

Без учета регистра apple, Apple и APPLE считаются равными, и их порядок не изменяется.

Comparer.FromCulture

Comparer.FromCulture возвращает функцию сравнения, которая принимает два аргумента. Во-первых, это культура, представленная языковым тегом. Например, en-US для английского языка в США. Этот тег используется для указания параметров языкового стандарта в платформе .NET. Второй аргумент является необязательным логическим значением ignoreCase (true/false), указывающим, следует ли игнорировать регистр символов. По умолчанию он равен false.

Рис. 11.6. Сравнение с правилами, зависящими от языка и региональных параметров

При таком сравнении учитываются культурные соглашения в отношении правил сортировки для конкретного языка и чувствительность к регистру. Любопытно, что в этом примере слово в нижнем регистре ранжируется и упорядочивается перед прописными, в отличие от порядкового сравнения (см. рис. 11.4). Влияние культуры становится еще более очевидным, если сравнивать, например, датское слово Færdig с написанием Faerdig:

Рис. 11.7. Иллюстрация различий в культуре при сравнении символов æ и ae

В английском языке символы æ и ae считаются идентичными; для US поле равно 0. В датском языке эти символы считаются различными, что приводит к значению поля DK = 1.

Критерии сравнения

А теперь посмотрите на книги на полке: заметили ли вы ту, которая находится не на своем месте? Возьмите. Определение ее правильного местоположения требует сравнения. Вы будете сравнивать книгу, которую вы держите в руках, с другими книгами на полке, применяя определенные критерии, повторяя этот процесс, пока не найдете ее законное место. То же самое относится и к данным. Установив соответствующие критерии, вы можете упорядочить данные осмысленными способами. Например, упорядочить названия месяцев с января по декабрь, рабочие дни с понедельника по воскресенье и т. д.

Критерии сравнения можно использовать для организации данных в соответствии с определенной логикой. В этом разделе мы рассмотрим различные методы, предоставляемые языком М Power Query для определения критериев сравнения значений порядка. Мы будем использовать функции List.Sort и Table.Sort в качестве примеров, чтобы показать, как можно создавать пользовательские правила и получать точный контроль над упорядочением данных.

Числовое значение

При сортировке данных с использованием функции Table.Sort вы можете назначить любое числовое значение, чтобы обозначить желаемый порядок сортировки. Меньшие значения имеют приоритет над большими, и данные упорядочиваются в порядке возрастания. Этот метод является простым и подходит для случаев, когда порядок сортировки можно напрямую связать с числовыми значениями. Например:

Table.Sort( myTable, each [Order] ) будет упорядочивать строки на основе значения в столбце Order. По умолчанию эта сортировка выполняется в порядке возрастания:

Рис. 11.8. Результат шага sortData

Вычисление ключа сортировки

Для более сложных сценариев можно использовать функцию для вычисления ключа для каждого элемента. Этот ключ становится основой для сортировки. Метод полезен, когда ключ сортировки не может быть напрямую получен из исходных значений и требует каких-либо вычислений. Рассмотрим список с названиями месяцев в алфавитном порядке: april, february, january, march. Чтобы расположить их в хронологическом порядке, мы можем получить значение типа даты. Ключ сортировки вычисляется добавлением года к названию месяца и использования функции Date.From для преобразования строки в значение типа даты. Затем List.Sort упорядочит названия месяцев в хронологическом, а не в алфавитном порядке:

Рис. 11.9. Упорядочение названий месяцев в хронологическом порядке

Вы можете столкнуться с ситуациями, когда порядковые значения не могут быть вычислены напрямую. В таких случаях может потребоваться поиск для установления определенного порядка. Рассмотрим следующий запрос, который возвращает таблицу myTable из одного столбца со значениями Moderate, Low и High:

Если в столбце Label только эти три значения, мы можем создать запись на лету, чтобы указать предпочтительный порядок сортировки. Убедитесь, что каждое значение соответствует имени поля в записи. Присвойте каждому полю числовое значение; имейте в виду, что числа упорядочены от меньшего к большему. Мы будем использовать 0, 1, 2, но 100, 200, 300 дадут тот же результат. Используйте инициализатор записи – набор квадратных скобок, чтобы создать запись и разделить каждое поле запятой: [High=0, Moderate=1, Low=2], [Label].

Если вы хотите использовать эту запись в нескольких запросах, лучше сохранить ее в отдельном запросе. Это позволит использовать запись многократно, не дублируя ее код. Однако в этом примере мы воспользуемся записью только один раз, поэтому вставим ее сразу в сортировку:

• Вызовите Table.Sort , чтобы изменить порядок строк в myTable.
• Примените функцию each Record.FieldOrDefault( [High=0, Moderate=1, Low=2], [Label] )

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

Рис. 11.10. Упорядочение значений с помощью записи

Теперь, когда вы понимаете, как вычислять ключи сортировки, давайте рассмотрим, как применять эти ключи вместе с направлениями сортировки, которые позволяют настроить порядок данных в соответствии со специфическими требованиями.

Список с ключом и порядком

Чтобы одновременно выбрать ключ для сортировки и управлять порядком сортировки, можно использовать список списков в качестве критерия сортировки. Каждый вложенный список должен содержать два элемента: ключ для сортировки и нужное направление сортировки, указанное с помощью Order.Type (Order.Ascending = 0 или Order.Descending = 1). Этот метод обеспечивает гибкость, когда требуется пользовательская логика сортировки. Например:

Давайте разберем содержимое каждого списка сортировки:

• AfterDelimiter([Category], " ") использует Text.AfterDelimiter для извлечения текста из столбца Category после первого символа пробела.
• Lower преобразует строки категории в нижний регистр.
• Указав 1 или Descending упорядочиваем категории по убыванию.

• Select извлекает цифры как текст из столбца SubCategory.
• From преобразует эти строки в числа.
• Указав 0 или Ascending упорядочиваем подкатегории по возрастанию.

В результате получаем:

Рис. 11.11. Сортировка категорий по убыванию и подкатегорий по возрастанию

Пользовательская функция сравнения с условной логикой

Чтобы полностью контролировать процесс сравнения, можно создать пользовательскую функцию сравнения, которая принимает два аргумента. Эта функция должна возвращать -1, 0 или 1 в зависимости от отношения между левым и правым входами. Несмотря на то, что следующий пример является упрощенным, в нем показано, как можно использовать условную логику для обработки сложных сценариев сортировки:

Выражение customComparer начинается с (x, y) =>, указывая, что это функция с двумя параметрами, x и y, которая возвращает значение функции. Тело функции будет вычисляться только при ее вызове. Это тело содержит условные операторы, которые отдают приоритет четным числам над нечетным, если x больше y. Если x меньше y, значения помещаются позже в порядке сортировки. Если применить функцию к списку с порядковыми номерами от 1 до 10, получим:

Рис. 11.12. Пользовательская функция сравнения с условной логикой для упорядочения значений

Пользовательская функция сравнения на основе Value.Compare

Можно также создать двухпараметрическую пользовательскую функцию сравнения для полного управления процессом сравнения. Используйте функцию Value.Compare для делегирования пользовательской логики сравнения для получения результата -1, 0 или 1 по мере необходимости. Этот метод особенно полезен, когда требуются сложные правила сравнения. Например:

weekdayComparer включает вложенную функцию, которая создает weekdayRecord, которая учитывает язык и региональные параметры и обозначает дни недели и их номера. По умолчанию используется язык и региональные параметры en-US. Если требуется дальнейшее изучение, код M для этой части функции приведен отдельно как fxCreateWeekdayRecord:

Все, что требуется функции fxCreateWeekdayRecord, – это одно значение даты, которое отмечает первый день недели, и опционно язык и региональные параметры:

Рис. 11.13. Выходные данные fxCreateWeekdayRecord в виде записи

Критерии равенства

В мире данных определить равенство не всегда просто. Критерии равенства позволяют выявить различия или общие черты между двумя величинами. Такие критерии используются для определения равенства и сопоставления данных в таблицах или списках в соответствии с определенной логикой. В этом разделе мы рассмотрим различные способы задания критериев равенства в языке M. Мы будем использовать функцию List.Contains, чтобы проиллюстрировать, как эти методы помогают установить контроль над проверкой равенства.

Функции сравнения по умолчанию

Ранее мы подробно рассмотрели роль функций сравнения, которая заключается в предоставлении метода сравнения значений, определения их относительного порядка и равенства. Вы можете использовать одну из функцией сравнения по умолчанию. Она действует как встроенный свод правил, которому следует List.Contains при сравнении значений. Например, игнорируя чувствительность к регистру, Нью-Йорк и нью-йорк считаются равными:

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

Для более сложных сценариев можно создать двухпараметрическую пользовательскую функцию сравнения. Она позволит установить специальные правила равенства. Следующая функция проверяет, содержит ли элемент списка (x) определенную подстроку (y):

Селекторы ключей

Селекторы ключей позволяют сфокусироваться на определенном поле или выборе полей, направляя внимание функции на эти области. Например, если вы работаете со списком записей и заинтересованы только в сопоставлении с полем ID, селектор выберет только это поле, игнорируя остальные:

Комбинирование селекторов ключей и функция сравнения

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

Критерии равенства полезны для сопоставления данных в таблицах или списках. Мы продемонстрировали несколько методов, которые позволяют управлять проверкой равенства в функциях M. Как и в случае с comparisonCriteria, возможность определять и передавать пользовательские функции сравнения в качестве equationCriteria обеспечивает значительную гибкость при определении равенства.

Вы когда-нибудь мечтали о возможности исправить ошибку в одно мгновение? Заменители предоставляют эти средства для ваших данных, позволяя подставлять значения частично или полностью.

Заменители

Функции замены используются другими функциями в языке М Power Query. Существует два заменителя по умолчанию: Replacer.ReplaceText и Replacer.ReplaceValue. Они используются в качестве аргументов List.ReplaceValue и Table.ReplaceValue. Эти функции обычно используются в сценариях преобразования данных, где необходимо изменить и очистить несогласованные данные для получения единообразия. Посмотрите на таблицу, где мы хотим заменить HR на Human Resources в столбце Department:

Рис. 11.14. Пример данных

Вот как это сделать с помощью пользовательского интерфейса. Выберите столбец Department. Пройдите Преобразование –> Замена значений. Появится диалоговое окно Замена значений. Введите HR в поле Значение для поиска. Введите Human Resources в поле Заменить на. Кликните Расширенный параметры, установите галку Ячейка целиком. Кликните OK.

К запросу добавляется шаг Replace Value, и таблица изменится:

Рис. 11.15. Результат операции Замена значений

Код M в строке формул вызывает функцию высшего порядка Table.ReplaceValue. Она использует внутреннюю функцию Replacer.ReplaceValue:

Рассмотрим заменители более подробно. В языке M есть две библиотечные функции замены.

Replacer.ReplaceText

Функция Replacer.ReplaceText облегчает замену текстового шаблона другим. Она имеет три параметра текстового типа: input или curText – где будет происходить замена, oldText – шаблон, который нужно идентифицировать и заменить, и newText – на что заменить. Функция сканирует все вхождения oldText в curText и заменяет их на newText. Например:

Функция Replacer.ReplaceText заменяет каждое вхождение oldText на newText в предоставленной текстовой строке curText, поддерживая частичную замену текста. Синтаксис: Replacer.ReplaceText(curText, oldText, newText).

Рис. 11.16. Примеры замены текста

В первых трех столбцах таблицы представлены входные значения, а в последнем столбце отображаются выходные данные после выполнения функции Replacer.ReplaceText. При выборе параметра Замена значений на вкладке Преобразование в интерфейсе преобразование применяется ко всем выбранным столбцам. Имена этих столбцов перечислены в последнем аргументе Table.ReplaceValue, как показано на рисунке 11.16. В столбцах curText и ReplaceText таблицы точка заменена восклицательным знаком.

Replacer.ReplaceValue

Функция Replacer.ReplaceValue используется для замены значений другими значениями или выражениями. Имеет три параметра типа any: вход curVal – где будет происходить замена; oldVal – значение, которое должно быть идентифицировано и заменено; newVal – новое значение. Например:

Рис. 11.18. Пример замены значений

В первых трех столбцах таблицы представлены входные значения, а в последнем столбце –выходные данные после вызова функции Replacer.ReplaceValue. Важно подчеркнуть, что для Replacer.ReplaceValue требуется точное совпадение. Операция может быть выполнена над любым типом значения, как примитивным, так и структурированным.

Чтобы проиллюстрировать это, приведем пример, в котором выполняется условная замена. Рассмотрим типичный сценарий, но имейте в виду, что существует несколько решений таких проблем. У вас есть таблица измерений, которая медленно изменяется с течением времени. В нашей выборке показаны сотрудники и их отделы в определенные периоды времени:

Рис. 11.19. Медленно изменяющееся измерение

Скопируйте и вставьте следующий код в новый пустой запрос:

Здесь измерение было объединено с таблицей фактов, чтобы получить таблицу со всеми отделами, в которых сотрудник был активен, так как имеется ключ слияния – employee (рис. 11.20). Для первоначальной записи John on November 1st, 2023 мы стремимся вернуть Operations, а для последующих записей мы намерены вернуть Sales из вложенной таблицы:

Рис. 11.20. В результате слияния была получена история отдела для каждого сотрудника

Чтобы создать код, выберите столбец Department, и пройдите Преобразование –> Замена значений. В диалоговом окне ничего не меняйте, нажмите OK:

Рис. 11.21. Оставьте диалоговое окно Замена значений пустым

В строке формул появится код:

Функция высшего порядка Table.ReplaceValue вызвала функцию Replacer.ReplaceValue. Теперь нужно указать oldValue, который мы ищем, и newValue, которым мы хотим заменить значение в родительском выражении. Это позволит передавать эти значения в функцию замены вместо пустых текстовых строк.

Первая часть проста: мы находимся в таблице, и для каждой строки этой таблицы мы хотим заменить значение, которое в данный момент находится в этой ячейке. Поэтому нам нужно заменить первую, пустую текстовую строку, которая представляет собой набор двойных кавычек, на: each [Department]

Второй набор двойных кавычек предназначен для значения замены. Здесь мы хотим использовать Table.SelectRows для выбора одной строки из вложенной таблицы, чтобы извлечь отдел:

На рисунке 11.22 показаны значения, полученные из вложенной структуры:

Рис. 11.22. Ожидаемый результат

Если вы не до конца понимаете код M, вернитесь к главе 8, которая посвящена работе с вложенными структурами и более подробно рассматривает эти типы сценариев.

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

Replacer.ReplaceText фокусируется на подстановке подстрок в тексте, а Replacer.ReplaceValue – на замене содержимого ячейки. Оба заменителя используются функциями высшего порядка, такими как Table.ReplaceValue и List.ReplaceValue.

Пользовательские заменители

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

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

Чтобы эффективно управлять процессом замены, можно на лету создать встроенную анонимную функцию замены, которая принимает три параметра:

Пользовательская логика замены аналогична заменителям по умолчанию и требует три параметра: currentValue (x), oldValue (y) и newValue (z). На месте функции (x, y, z) => if y then z else x может быть и иная функция, например customReplacer:

Функция customReplacer определена без типа и принимает три параметра: currentValue, oldValue и newValue. Функция оценивает, совпадает ли текущее значение currentValue с oldValue; если да, заменяет на newValue. В противном случае currentValue остается неизменным.

Пользовательские функции замены можно использовать в функциях высшего порядка Table.ReplaceValue и List.ReplaceValue для отработки конкретных требований по замене. Рассмотрим таблицу:

Рис. 11.23. Необходимо заменить несколько значений даты

Несколько дат требуют замены на null. Чтобы избежать множественных преобразований ReplaceValue, мы разработаем стратегию, позволяющую заменить их все за один шаг.

На самом деле, следующий код M иллюстрирует три немного отличающихся друг от друга метода. Первый – Anonymous, нетипизированная встроенная функция. Второй – Typed, типизированная встроенная функция. CF – типизированная пользовательская функция, назначенная переменной dateReplacer:

Подробнее о коде.

datesToReplace: список с тремя значениями даты, которые необходимо заменить в нескольких столбцах таблицы.

dateReplacer: типизированная пользовательская функция, которая проверяет, находится ли currentValue (x) в списке дат для замены. Если да, функция возвращает newValue (z), если нет, то currentValue (x) остается без изменений.

colsList: выбирает только те столбцы из исходной таблицы, имя которых начинается с Date. Это динамический способ идентификации всех столбцов дат в таблице.

Source: таблица данных.

Anonymous: показывает, как применить встроенную нетипизированную пользовательскую замену.

Typed: иллюстрирует, как применить встроенную типизированную пользовательскую замену.

CF: показывает, как применить пользовательскую замену, назначенную переменной dateReplacer.

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

Мы представили концепцию пользовательских заменителей, демонстрируя три различных метода: нетипизированную встроенную функцию, типизированную встроенную функцию и типизированную функцию, назначенную переменной. Они позволяют осуществлять динамическую замену значений в зависимости от заданных условий. Пользовательские заменители должны быть определены как функции с тремя параметрами: currentValue, oldValue и newValue. Если вы хотите узнать больше о пользовательских функциях, см. главу 9 Параметры и пользовательские функции.

Соединители

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

Для перевода Combiner, я использую два синонима Соединитель и Объединитель.

В языке М Power Query есть несколько библиотечных соединителей – функций высшего порядка –Table.ToList и Table.CombineColumns. Эти функции используются для обработки каждой строки в таблице и создания одного значения для каждой строки.

Распространенной операцией, выполняемой с помощью интерфейса, является слияние столбцов. Опция находится на вкладке Преобразование и использует Table.CombineColumns и Combiner.CombineTextByDelimiter. Чтобы обеспечить успешную работу, вложенный Table.TransformColumnTypes включается в преобразование при обнаружении типа, отличного от текста. Это гарантирует, что будет получено либо одно текстовое значение, либо ошибка на уровне ячейки. Это дает пользователям возможность соединять различные примитивные значения: текст, числа, даты и др. Рассмотрим таблицу, которую генерирует код:

Когда вы выбираете более одного столбца, в данном случае все (как показано на рис. 11.24), и на  вкладке Преобразование (1) выбираете Объединить столбцы (2), появляется окно (3):

Рис. 11.24. Объединение столбцов в графическом интерфейсе

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

В результате получим:

Рис. 11.25. Результат объединения двух столбцов с рис. 11.24

Если вы хотите сохранить входные столбцы, выберите Объединить столбцы на вкладке Добавление столбца. В этом случае код M использует функцию Text.Combine.

В языке M пять функций соединителей, которые позволяют объединять текстовые значения различными способами.

Combiner.CombineTextByDelimiter

Возвращает функцию, объединяющую список текстовых значений в один текст с использованием указанного разделителя. Параметры: Delimiter (text) – разделитель, указывающий символы, используемые в качестве разделителя; QuoteStyle (number) – управляет тем, как обрабатываются кавычки.

Разделитель – значение текстового типа, определяющее символ или символы, используемые в качестве разделителей между объединенными текстовыми значениями. quoteStyle – это перечисление, которое управляет тем, как кавычки обрабатываются в текстовой строке во время операций объединения текста.

В качестве разделителя можно использовать три дефиса подряд ---. Если применить к списку текстовых строк {"a", "b", "c"}, получим a---b---c:

QuoteStyle.None означает, что символы кавычек в строке обрабатываются так же, как и любой другой символ:

А вот QuoteStyle.Csv означает, что кавычки должны рассматриваться как специальные символы для обозначения начала и конца строки в кавычках. Вложенные кавычки внутри строки обозначаются двумя последовательными кавычками:

Combiner.CombineTextByDelimiter возвращает функцию, которую можно применить к списку текстовых значений, объединив их в один текст с помощью указанных аргументов. Если вы опустите необязательный аргумент quoteStyle или передадите null, QuoteStyle.Csv будет применен по умолчанию.

Combiner.CombineTextByEachDelimiter

Возвращает функцию, которая объединяет список текстовых значений в один текст с использованием каждого указанного разделителя в последовательности. Параметры: Delimiter (list) – указывает символы, используемые в качестве разделителей; QuoteStyle (number) – управляет тем, как обрабатываются кавычки.

Последовательность разделителей определяется их количеством и порядком, указанным в первом аргументе. Если количество объединяемых текстовых значений превышает число разделителей, оставшиеся текстовые значения объединяются без разделителя. Поведение quoteStyle аналогично ранее описанному.

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

Здесь inputList – список, содержащий шесть текстовых значений. delimiterPattern – список с двумя разделителями. delimitersList повторяет delimiterPattern заданное количество раз, чтобы каждый элемент в inputList получил разделитель. closure – функция-замыкание, создаваемая путем вызова функции объединителя с предоставленным delimiterList. Result вызывает это замыкание с параметром inputList, чтобы получить a---b_c---d_e---f.

Функция Combiner.CombineTextByEachDelimiter возвращает функцию, которая объединяет список текстовых значений в один текст, используя разделители в указанном порядке и количестве.

Combiner.CombineTextByLengths

Возвращает функцию, которая создает одну текстовую строку путем объединения списка текстовых значений в один текст с указанной длиной. Параметры: Lengths (list) – указывает длину каждого текстового раздела; Template (text) – необязательный аргумент, задает начальную последовательность символов.

В списке длин указывается количество символов, извлекаемых из каждого соответствующего текстового значения. Шаблон – задает начальные символы для объединенного вывода. Если опущено или задано значение null, функция использует строку пробелов, количество которых соответствует сумме указанных длин. Символы шаблона будут использованы, если в исходной строке недостаточно символов.

Шаблон перезаписывается извлеченными текстами в процессе объединения. Если шаблону не хватает символов, он дополняется пробелами. Лишние символы шаблона будут отображаться в объединенном тексте:

Соединитель получает список длин { 4, 4, 4, 4 } и шаблон, состоящий из 4×4=16 символов. Далее объединение вызывается для списка пяти строк. Основываясь на списке длин, объединитель получит 4 символа из 4 строк для перезаписи 16-символьного шаблона. Если строка в списке содержит менее 4 символов, отображаются символы шаблона. Лишние символы в строках исходного списка при объединении отбрасываются. Также в объединение не включаются дополнительные строки (в нашем случае – пятая), для которых не указана длина. Если же длина превышает количество символов в строке, используются символы шаблона.

Combiner.CombineTextByPositions

Возвращает функцию, которая объединяет текст из указанных позиций из списка текстовых значений. Параметры: Positions (list) – указывает позиции для извлечения; Template (text) – начальная последовательность символов.

Список позиций представляет собой кумулятивное количество символов, которое должно быть извлечено из соответствующего текстового значения. Каждая последующая позиция должна либо совпадать, либо превосходить предыдущую. Необязательный шаблон – это строка, определяющая базовый шаблон, который будет перезаписан в процессе объединения. Когда количество извлекаемых символов превышает длину соответствующего текстового значения, часть этого шаблона не перезаписывается и становится частью выходных данных. Лишние символы игнорируются. Однако, если есть дополнительные символы, для которых не указаны позиции, включается только первый полный текст, а все последующие текстовые значения исключаются:

Вот как работает этот процесс:

Рис. 11.26. Процесс Combiner.CombineTextByPositions

Combine.CombineTextByRanges

Возвращает функцию, которая объединяет текстовые сегменты с использованием заданных позиций и длины. Параметры: Ranges (list) – указывает позицию и количество символов для извлечения; Template (text) – начальная последовательность символов.

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

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

Мы рассмотрели соединители языка М Power Query. Они используются функциями высшего порядка. Например Table.ToList и Table.CombineColumns используют соединители для обработки строк таблицы, получая одно значение для каждой строки. Прекрасным примером является операция слияния столбцов в пользовательском интерфейсе.

Разделители

Разделители разбивают одно текстовое значения на несколько на основе разделителя или шаблона. Они часто используются стандартными библиотечными функциями высшего порядка, например Table.SplitColumn, которая предназначена для разделения одного столбца на несколько столбцов или строк. Это распространенная операция при обработке строк, требующих синтаксического анализа или преобразования.

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

Посмотрим на таблицу, в которой мы хотим разбить Column1 на столбцы:

Рис. 11.27. Пример данных

В интерфейсе выберите Столбец1. Перейдите на вкладку Главная или Преобразование и выберите Разделить столбец –> По разделителю. В диалоговом окне выберите Пользовательский, введите запятую и пробел. Убедитесь, что в Расширенных параметрах выбрано Разбить на столбцы и количество столбцов для разделения равно 2. Нажмите OK.

Рис. 11.28. Результат операции Split Column

В строке формул вы увидите, что была вызвана функция высшего порядка Table.SplitColumn, которая передала аргументы в Splitter.SplitTextByDelimiter. Полностью код:

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

Возвращаемая функция, замыкание, выполняет фактическую операцию разбиения. Этот двухэтапный процесс обеспечивает гибкость, поскольку вы можете задать разделитель один раз с определенными аргументами и повторно использовать его несколько раз с различными входными текстовыми строками, что делает его эффективным для пакетной обработки текста. Язык M включает 10 разделителей.

Четыре из них принимают необязательный параметр quoteStyle. К ним относятся: Splitter.SplitTextByDelimiter, Splitter.SplitTextByAnyDelimiter, Splitter.SplitTextByEachDelimiter, Splitter.SplitTextByWhitespace.

Параметр quoteStyle – это перечисление, которое управляет обработкой кавычек в строке во время операции разделения:

• None указывает, что кавычки в строке не имеют особого значения и обрабатываются так же, как и любой другой символ в тексте.
• Csv подразумевает, что кавычки следует рассматривать как специальный символ для обозначения начала и конца строки в кавычках. Вложенные кавычки внутри строки представлены двумя последовательными кавычками. Если опустить необязательный аргумент quoteStyle или передать null, по умолчанию будет использоваться QuoteStyle.Csv.

SplitByNothing

Возвращает функцию, которая предотвращает разделение, заключая свой аргумент в список. Функция не имеет параметров.

При вызове Splitter.SplitByNothing возвращает функцию, специально предназначенную для управления поведением разделения в операциях Power Query, сохраняя при этом исходную структуру данных. Это достигается путем заключения аргумента в список, создавая список с одним элементом, который предотвращает разделение. Это гарантирует, что данные останутся неизменными при операциях разделения, выполняемых функциями высшего порядка.

Power Query по умолчанию использует запятые в качестве разделителей. Например, если есть два элемента в списке и мы хотим преобразовать их в таблицу с одним столбцом, мы можем использовать функцию Table.FromList. Однако выполнение кода, показанного на рисунке 11.29, приводит к тому, что первая строка разбивается на столбцы, возвращая таблицу из двух столбцов:

Рис. 11.29. Разбиение по умолчанию в Table.FromList

Чтобы настроить поведение разделения, в качестве второго значения аргумента можно передать Splitter.SplitByNothing. Это заключит каждое значение в список, гарантируя, что каждая строка будет рассматриваться как отдельный элемент, возвращая таблицу с одним столбцом:

Рис. 11.30. Переопределение поведения разбиения по умолчанию

Ниже приведен код для этих примеров, а также дополнительный, демонстрирующий процесс инкапсуляции путем помещения аргумента в список, создавая одноэлементный список, предотвращающий операцию разделения:

Splitter.SplitTextByAnyDelimiter

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

• delimiters (list): указывает символы, используемые в качестве разделителей,
• quoteStyle (number): управляет обработкой кавычек,
• startAtEnd (logical): определяет направление разбиения.

Разделитель возвращает функцию, которая может быть применена к текстовому значению, разбивая его на список текстовых значений на основе указанных аргументов. delimiters является обязательным аргументом и принимает список текстовых значений, каждое из которых выступает в качестве разделителя для операции разделения. Эти разделители могут состоять из одного символа или нескольких символов, и функция будет разделять текст при любом появлении разделителя.

quoteStyle является необязательным аргументом и определяет, как функция должна обрабатывать кавычки в тексте.

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

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

Рис. 11.30а. Влияние направления разделения на результат

Splitter.SplitTextByCharacterTransition

Возвращает функцию, которая разбивает текст на список текстовых значений в точках, где переход символов удовлетворяет условиям. Параметры: before (list или function): вычисляет символ как истинный или ложный; after (list или function): вычисляет символ как истинный или ложный.

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

Параметр before имеет значение true для любого символа. Параметр after использует функцию isEven для проверки того, представляет ли символ четное число. Когда оба условия выполнены, строка разделяется, гарантируя, что входная текстовая строка будет разделена перед каждым символом, представляющим четное число. Функцию isEven можно заменить списком, содержащим четные числа {«0», «2», «4», «6», «8»}.

При использовании функций в качестве аргументов важно обрабатывать исключения. В этом примере ключевые слова try и else используются для предотвращения сбоя функции при попытке преобразовать нечисловой символ в число.

Splitter.SplitTextByDelimiter

Возвращает функцию, которая делит текст на список текстовых значений с помощью указанного разделителя. Параметры: delimiter (text) – указывает символы, используемые в качестве разделителя; quoteStyle (number) – управляет обработкой кавычек.

Для разделителя требуется значение текстового типа, определяющее символ или символы, используемые в качестве разделителей для разделения текстовой строки. quoteStyle –перечисление, которое определяет, как кавычки в тексте обрабатываются в процессе разделения.

В отличие от функции Splitter.SplitTextByAnyDelimiter, эта функция не принимает список разделителей и не включает параметр startAtEnd, что делает ее менее гибкой, но более простой. По сравнению с родственной функцией Text.Split отличительной особенностью Splitter.SplitTextByDelimiter является ее способность управлять стилями кавычек. Однако, если вам не требуется QuoteStyle.Csv, простота и удобство использования Text.Split делают ее идеальным выбором для задач по разделению текста.

Рис. 11.30б. Работа разделителя Splitter.SplitTextByDelimiter: слева – QuoteStyle.None, справа – QuoteStyle.Csv

Здесь в качестве разделителя используется запятая. Перечисление QuoteStyle.None  указывает, что кавычки в тексте не должны считаться специальными символами при разбиении. В результате входная строка разбивается на каждую запятую, игнорируя двойные кавычки. Однако, если использовать QuoteStyle.Csv, любой текст, заключенный в двойные кавычки, считается единым целым; даже если он включает разделитель. Такая строка не будет разделена.

Splitter.SplitTextByEachDelimiter

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

• delimiters (list): указывает символы, используемые в качестве разделителей,
• quoteStyle (number): управляет обработкой кавычек,
• startAtEnd (logical): определяет направление разбиения.

Последовательность и количество разделителей, предоставленных в качестве первого аргумента, будут определять, где и сколько раз будет разделена текстовая строка. Поведение quoteStyle и startAtEnd эквивалентно описанному выше.

Рис. 11.30в. Работа разделителя Splitter.SplitTextByEachDelimiter

Здесь startAtEnd имеет значение true, указывая, что функция инициирует операцию разделения с конца текстовой строки. Затем разделение следует точному порядку и количеству разделителей, которые предоставляются для управления процессом разделения.

SplitTextByLengths

Возвращает функцию, которая разбивает текст на список текстовых значений по заданной длине. Параметры: lengths (list) – определяет длину каждого текстового раздела, startAtEnd (logical) – определяет направление разбиения.

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

Символы, для которых не указана соответствующая длина, игнорируются. Однако, если длина превышает общее количество символов, элемент списка с нулем символов добавляется для каждого элемента избыточной длины без возникновения ошибки.

Функция Splitter.SplitTextByLengths особенно полезна при обработке строк из баз данных или неструктурированных файлов с заранее определенной длиной фиксированной ширины для каждого поля или столбца. Это гарантирует, что каждое поле занимает согласованное количество символов, что упрощает процесс синтаксического анализа. Например:

Рис. 11.30г. Работа разделителя Splitter.SplitTextByLengths

Идентификация данных фиксированной ширины не вызывает затруднений. Перейдите на вкладку Просмотр и включите Моноширинный.

Splitter.SplitTextByPositions

Возвращает функцию, которая делит текст в указанных позициях, чтобы получить список текстовых значений. Параметры: positions (list) – указывает позицию для разделения; startAtEnd (logical) – определяет направление разбиения.

Список позиций представляет собой совокупное количество символов для разбивки. Каждый пункт в списке позиций должен соответствовать или превышать предыдущий и не может быть отрицательным.

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

Рис. 11.31а. Работа разделителя Splitter.SplitTextByPositions

Splitter.SplitTextByRanges

Возвращает функцию, которая делит текст в соответствии с указанными диапазонами, определяемыми начальной позицией и длиной. Параметры: ranges (list) – указывает позицию и количество символов для принятия; startAtEnd (logical) – определяет направление разбиения.

Список диапазонов, содержащий вложенные списки с двумя значениями, определяет порядок вывода. Эти значения представляют начальную позицию и количество извлекаемых символов или null для включения всех оставшихся символов в строку. Если позиции разделения перекрываются с предыдущими, функция повторно извлечет этот текстовый сегмент.

В следующем примере для каждого элемента в списке диапазонов, где позиция превышает общее количество символов, элемент с нулем символов добавляется в список результатов без возникновения ошибки:

Рис. 11.31б. Работа разделителя Splitter.SplitTextByRanges

Splitter.SplitTextByRepeatedLengths

Возвращает функцию, которая многократно разбивает текст на список текстовых значений заданной длины. Параметры: length (number) – определяет длину каждого текстового раздела; startAtEnd (logical) – определяет направление разбиения. Длина – это положительное число, представляющее длину сегмента, на которые разбивается текст.

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

Рис. 11.31в. Работа разделителя Splitter.SplitTextByRepeatedLengths

Возможность заполнения отсутствует, поэтому окончательный элемент результирующего списка может содержать меньше символов, чем указанная длина.

Splitter.SplitTextByWhitespace

Возвращает функцию, которая разделяет текст на список текстовых значений на основе пробельных символов. Один параметр quoteStyle (number) – управляет обработкой кавычек. Пробелы в языке М Power Query представлены множеством символов, включая пробелы и управляющие символы, такие как возврат каретки, перевод строки и табуляция.

При работе с текстовыми значениями необходимо использовать escape-последовательности символов для внедрения управляющих символов. В частности, если вы хотите вставить возврат каретки, у вас есть три альтернативы: вы можете использовать короткий 4-значный шестнадцатеричный формат #(000D), выбрать более длинное 8-значное шестнадцатеричное представление Юникода #(00000000D) или использовать простое escape-сокращение #(cr).

Как правило, предпочтительнее использовать escape-сокращение. Например, для табуляции – #(tab), для перевода строки – #(lf). Несколько escape-кодов могут быть включены в одну escape-последовательность через запятые –  #(cr,lf), что эквивалентно #(cr)#(lf).

Рис. 11.31г. Работа разделителя Splitter.SplitTextByWhitespace

Разделители полезны, когда вам нужно разбить строку на части в зависимости от определенного шаблона, разделителя, длины или положения. Они обычно используются в сценариях, связанных с синтаксическим анализом строк, содержащих объединенные значения, например в CSV-файле, в текстовом файле с разделителями или в строках, содержащих другие значения, например даты или числа. Предоставленные выше фрагменты кода предназначены для использования в качестве справочных материалов и для изучения их функциональных возможностей.

Практические примеры

Итак, вы получили представление о функциях сравнения, замены, соединения и разделения языка M. Теперь, когда вы обладаете этими базовыми знаниями, пришло время закрепить навыки на практике. Примеры улучшат ваше понимание того, как интегрировать эти функции в проекты.

Не стесняйтесь разработать свою собственную стратегию для этих сценариев, прежде чем углубляться в них. Если вам неудобно переводить это в код M в данный момент, это совершенно нормально. Вы все еще можете сформулировать план и рассмотреть потенциальные препятствия, которые необходимо преодолеть. Это позволит вам сравнить свой подход с предложенным здесь. Однако имейте в виду, что существует множество решений любой проблемы. В книге представлен только один из возможных методов.

Удаление управляющих символов и лишних пробелов

Распространенной задачей при очистке и преобразовании текстовых строк является удаление управляющих символов и лишних пробелов. В отличие от функции обрезки в Excel, ее эквивалент в Power Query, Text.Trim, удаляет только начальные и конечные пробелы из текстового значения, хотя можно указать и другие символы для обрезки. Вот пример таблицы:

Мы хотим убедиться, что вводимые пользователем данные соответствуют ожидаемым форматам и обеспечить единообразие, устранив вариации интервалов и устранив непечатные символы.

Функция cleanTrim. Создадим пользовательскую функцию cleanTrim. Улучшим функцию Text.Trim, чтобы она также удаляла управляющие символы и все лишние пробелы внутри строки. Это относится к области действия функции Splitter.SplitTextByWhitespace, которая разделяет текст по каждому пробелу, возвращая список текстовых значений:

Пробелы в M включают как пробелы, так и управляющие символы. При вызове разделителя он возвращает список с текстовыми значениями. Далее символы объединяются пробелом для преобразования обратно в строку. Мы можем использовать такие функции, как Combiner.CombineTextByDelimiter или Text.Combine. Обе дают один и тот же результат.

Однако важно понимать, что если список содержит пустую текстовую строку в качестве начального или конечного элемента, операция объединения вставит предшествующий или конечный пробел. Эту проблему можно решить с помощью Text.Trim. Стоит отметить, что, основываясь на ограниченном тестировании, операция простой обрезки Text.Trim и наша функция cleanTrim показывают одинаковую производительность.

Функцию cleanTrim можно вызвать для Column1, добавляя столбец в таблицу:

Рис. 11.32. Результат вызова cleanTrim для образца данных

Извлечение адресов электронной почты из строки

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

Цели: извлечение адресов электронной почты из смешанных текстовых данных; повышение точности за счет выявления и анализа закономерностей

Разработка функции getEmail. Видно, что строки 3 и 5 структурно недопустимы из-за отсутствия имени домена первого уровня или наличия более одного символа @ в адресе. Нам также требуется более одного разделителя для эффективного сегментирования каждой строки.

Мы хотим создать функцию getEmail, но сначала разработаем логику, которая позволит извлекать адреса электронной почты из записей. Нажмите на значок мини-таблицы, расположенный слева от заголовка столбца Contact info, и выберите Добавить пользовательский столбец. В диалоговом окне можно присвоить столбцу имя. Поскольку это временный столбец, мы оставим имя без изменений. Так как требуется несколько разделителей, введите Splitter.SplitTextByAnyDelimiter() в область Настраиваемая формула столбца. В круглых скобках укажите список с разделителями: {" ", ",", ";", ":"}. Чтобы выполнить эту функцию, добавьте еще один набор скобок после закрывающей и выберите поле Contact info из списка Доступные столбцы. Код ниже был отформатирован для удобства чтения, но в строке формул код, сгенерированный этими действиями, будет напечатан в одну строку:

Просмотрите содержимое выходных списков, щелкнув пробел рядом со значениями вложенных списков. Например, значения списка второй строки: { "john.darling@nevergrowup.co.uk", "", "UK", "john.boy@abc.com"}.

Рис. 11.32а. Содержимое списков после разделения строк на элементы

Эти списки нужно отфильтровать. Вернитесь в окно Настраиваемый столбец, щелкнув значок шестеренки, расположенный справа от имени шага в разделе Примененные шаги. Перед формулой добавьте List.Select(. Затем перейдите к концу формулы и введите запятую и закрывающую скобку. Вы увидите предупреждение о том, что запятая не может предшествовать закрывающей скобке. В качестве аргумента укажите функцию, которая будет определять критерии выбора.

Структурно допустимый адрес электронной почты должен содержать символ @, за которым следует точка (.), именно в таком порядке. Попытаемся снова разделить каждый элемент списка с помощью Splitter.SplitTextByEachDelimiter с аргументом: {"@", "."}. Чтобы вызвать разделитель, добавьте набор скобок с подчеркиванием (_).

Несмотря на то, что предупреждение об ошибке устранено, наше выражение не является полным. Этот разделитель выдаст новый список, но нас интересуют только списки, содержащие три элемента. Поэтому добавляем List.Count( … ) = 3. Код в поле формулы должен выглядеть следующим образом:

Просмотрите содержимое еще раз, щелкнув пробел рядом со значением вложенного списка. Дополнительные критерии необходимы для исключения структурно недопустимых адресов электронной почты. Например, случаи, включающие более одного символа @, или случаи, когда за символом @ сразу следует точка (.)

Вернитесь в окно Настраиваемый столбец. Разверните выделение в качестве аргумента функции, введя ключевое слово and, за которым следует выражение: Text.Length( Text.Select(_, "@")) =1. Это решает одну потенциальную проблему. Чтобы обратиться к другой, введите еще одно ключевое слово and, за которым следует not Text.Contains(_, "@."), как показано ниже:

Последний шаг включает в себя извлечение и объединение оставшихся элементов списка. Откройте диалоговое окно и оберните его Text.Combine( …, ", "). Как только вы будете удовлетворены результатом, можете скопировать код M отсюда целиком.

Выберите Создать источник –> Пустой запрос. Откройте Расширенный редактор, удалите всё содержимое, и замените его скопированным кодом. Чтобы преобразовать код в пользовательскую функцию, начните запрос с инициализатора функции. Поместите курсор перед кодом и добавьте (stringas text) as text =>.

Эта функция принимает входные данные текстового типа и возвращает значение текстового типа. Закройте запрос и присвойте ему подходящее имя, например getEmail:

Проверьте функциональность этой функции, применив ее к первоначальной таблице с одним столбцом Contact info. Перейдите на вкладку Добавление столбца и кликните Вызвать настраиваемую функцию. В качестве параметра string выберите Contact info:

Рис. 11.33. Вызов getEmail для примера данных

Разбиение объединенных значений ячеек на строки

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

Однако, если вы столкнетесь с несколькими столбцами с объединенными значениями ячеек, могут возникнуть трудности. Рассмотрим пример:

Ячейки в столбцах Priority и Schedule содержат несколько значений, которые необходимо разделить на строки:

Рис. 11.34. Пример данных объединенных значений ячеек

Чтобы выполнить действия, описанные далее, перенесите код для этого примера в новый пустой запрос. Итак, мы хотим подготовить данные для анализа так, чтобы каждая запись занимала одну строку.

Очевидно, что мы имеем дело с пробельными символами, которые часто невидимы (не графические). К ним относятся пробел (часть класса Zs Юникода), горизонтальные и вертикальные табуляции, символы перевода страницы и последовательности символов новой строки. Последовательности новой строки включают возврат каретки, перевод строки, комбинацию возврата каретки и перевода строки и символы разделителя абзацев. Эти символы могут быть артефактами извлечения данных или пользовательского ввода.

В предыдущем разделе мы рассмотрели, как разделители возвращают функцию для разделения текстовой строки на список текстовых значений. Функцию Splitter.SplitTextByWhitespace можно использовать для разделения и, следовательно, удаления пробелов из строки. Ее необязательный параметр quoteStyle определяет, как обрабатываются кавычки в тексте. По умолчанию применяется QuoteStyle.Csv. Строки с кавычками не разделяются. Рассмотрим, как мы можем преобразовать таблицу на рис. 11.34.

Пройдите Добавление столбца –> Настраиваемый столбец. В диалоговом окне введите имя нового столбца temp. Помните, что вызов функции представляет собой набор круглых скобок, которые могут включать необязательный список аргументов. В области Настраиваемая формула столбца введите Splitter.SplitTextByWhitespace(), чтобы инициировать разделитель и получить его возвращаемое значение, которое является новой функцией. Внутри вызова функции выберите Schedule из раздела Доступные столбцы в правой части окна:

Рис. 11.35. Диалоговое окно Настраиваемый столбец

Нажмите OK. Новый временный столбец содержит список текстовых значений. При нажатии на пробел рядом со значением списка появится вторичный предварительный просмотр, показывающий содержимое списка.

Этот процесс можно повторить и для столбца Priority, но управление несколькими столбцами со списками может быть сложным, так как развертывание каждого из них по отдельности приведет к дублированию строк в таблице. Объединив все списки в одну таблицу, мы можем создать единую структуру, которая упрощает и контролирует процесс расширения.

В разделе Примененные шаги щелкните значок шестеренки у шага Добавлен пользовательский объект, чтобы снова открыть диалоговое окно Настраиваемый столбец. Измените код, чтобы создать таблицу, содержащую значения из столбцов Priority и Schedule:

Рис. 11.36. Обновление кода в диалоговом окне Настраиваемый столбец

Чтобы создать таблицу из нескольких списков, используйте функцию Table.FromColumns. В качестве первого аргумента укажите список списков, разделенных запятыми, представляющих значения столбцов. В качестве необязательного второго аргумента укажите список имен столбцов.

Мы также можем присвоить типы столбцам вложенных таблиц, чтобы в будущем не пришлось добавлять шаг Измененный тип. В строке формул измените код этого шага:

Прежде чем разворачивать вложенные таблицы в столбце temp, давайте удалим ставшие ненужными столбцы Priority и Schedule. Для этого существует несколько методов, например выбор и удаление столбцов. Однако, поскольку нас интересуют только три столбца, применение проекции будет наиболее кратким методом. Эта концепция подробно обсуждалась в главе 6 Структурированные данные. В строке формул после закрывающей скобки функции Table.AddColumn вставьте набор квадратных скобок. В этих квадратных скобках выделите каждый столбец, который вы хотите сохранить, сославшись на его имя в другом наборе квадратных скобок. В нашем случае – [[ID], [Code], [temp]].

Наконец, извлеките поля, развернув вложенную таблицу в столбце temp с помощью боковых стрелок, доступных в заголовке.

Полный код:

В результате получается преобразованная таблица:

Рис. 11.37. Объединенные значения ячеек, разбитые на строки

Замена нескольких значений

Часто задаваемый вопрос на многих форумах звучит примерно так: «В моем текущем проекте я имею дело с набором данных, который требует обширной замены значений во многих столбцах. Я использовал отдельные шаги ReplaceValue, что приводит к медленному и громоздкому запросу. Существует ли более эффективный метод управления массовыми заменами?»

Да, есть. Вот наш пример набора данных. Вы можете перенести этот код в Расширенный редактор и переименовать запрос rawData:

Вот небольшая часть таблицы:

Рис. 11.38. Часть исходной таблицы

Следующий запрос создаст таблицу Replacements, которая поможет визуализировать процесс замены.

Столбец Old содержит oldValue, подстроку, которую мы стремимся заменить на newValue, найденную в соседнем столбце New:

Рис. 11.39. Полная таблица замен Replacements

Мы хотим добиться единообразия (например, заменив N/A, na и none одним согласованным значением), исправить опечатки, орфографические ошибки и неточности.

Вместо таблицы Replacements мы используем два списка, по одному на каждый столбец этой таблицы. Это означает, что oldValue и newValue должны быть выровнены и использовать один и тот же позиционный индекс в своих списках. В следующем коде используется более продвинутая функция List.Accumulate, которая подробно будет рассмотрена в главе 13 Итерация и рекурсия.

Целью этого запроса является подстановка определенных подстрок в столбцы, имя которых начинается с Description. Мы заменим элементы из списка Old соответствующими элементами из списка New в выбранных столбцах. Вот как работает запрос:

Старый список содержит подстроки, предназначенные для замены. Использование List.Buffer гарантирует, что список будет вычислен один раз и сохранен в памяти для повышения производительности. Однако буферизация значений в памяти не гарантирует прироста производительности и должна быть протестирована.

Новый список содержит подстроки, заданные для замены соответствующих подстрок в старом списке. List.Buffer включен для повышения производительности, хотя и не гарантирует этого.

Список итераций создает список индексов и загружает его в память.

Эта функция выполняет итерацию по списку Iterations, по сути, предоставляя индексы как для старого, так и для нового списков. Она применяет преобразование к таблице rawData, которая ссылается на запрос, содержащий образец набора данных. Если вы назвали этот запрос с образцом данных по-другому, необходимо соответствующим образом обновить эту ссылку.

Начальное значение s – это таблица rawData. Аккумулятор a будет принимать каждое значение в списке {0.. List.Count(Old)-1}, по сути, индексы, для передачи всех элементов в списках Old и New.

Эта функция заменяет все экземпляры подстроки Old{a} на New{a} в указанных столбцах таблицы.

Это выражение выбирает, к каким столбцам применить замену. Оно выбирает все столбцы, имена которых начинаются с Description.

Рис. 11.40. Часть преобразованной таблицы

Это решение эффективно и динамично для замены нескольких текстовых значений в определенных столбцах таблицы. List.Accumulate обеспечивает производительную построчную замену значений без жесткого кодирования, что делает запрос более удобным в обслуживании. Буферизация старых и новых списков – это шаг оптимизации, который гарантирует, что эти списки оцениваются только один раз, что, вообще говоря, повышает производительность, хотя это всегда требует тестирования.

Условное объединение строк

Вот еще одно распространенное требование: преобразование многострочных записей, в данном случае из банковской выписки, в одну строку. Мы должны объединить все строки столбца Details, относящиеся к отдельной трансакции, и получить таблицу из шести строк:

Рис. 11.41. Пример банковской выписки

Следующий код был использован, чтобы получить эти исходные данные:

Цель – подготовить данные к анализу, чтобы каждая запись занимала одну строку.

Соединитель Group By спешит на помощь. В подобном сценарии можно использовать несколько стратегий, при этом универсальная функция Table.Group часто оказывается наиболее эффективной, особенно когда все связанные записи имеют общий ключ. Однако трудности возникают, когда ключи отсутствуют и их генерация не является очевидной.

В нашем примере количество строк в блоке, колеблется; нет фиксированного шаблона, на который можно было бы положиться. С другой стороны, использование столбца даты в качестве ключа недостаточно для разделения и объединения записей. В одну дату может быть несколько транзакций. Нужен дополнительный маркер, позволяющий различать блоки. Тем не менее, Table.Group предоставляет дополнительные возможности для сравнения.

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

Рис. 11.42. Диалоговое окно Группировать по

Конкретная операция в столбце Description на данный момент не имеет значения, так как мы изменим ее позже. Для столбца Debit выберите Мин, либо Макс (подойдет любая). Нажмите OK.

Теперь мы можем изменить код в строке формул. Понятно, что текстовые значения не могут быть суммированы, поэтому мы заменим функцию List.Sum на Text.Combine и предоставим пробел в качестве второго аргумента, разделителя. Поскольку для каждой транзакции всегда существует только одна сумма дебета, не имеет значения, выберете ли вы List.Min или List.Max; оба значения дадут один и тот же результат.

Table.Group принимает необязательный четвертый аргумент, groupKind. Мы можем передать GroupKind.Local, чтобы исходный порядок данных внутри одного блока не изменился.

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

Вот наша пользовательская функция сравнения: (x, y)=> Number.From(y[Date] <> null). Ей передаются два значения за раз, текущее значение (x) и значение следующей строки (y), и создается новая группа, если Date в следующей строке не равно null. Другими словами, если это значение равно false, Number.From возвращает 0 (ноль) и присваивает строку текущей группе. Однако, если значение Number.From имеет значение true, оно возвращает значение 1 и создает новую группу.

Вот код, который мы только что создали:

Рис. 11.43. Преобразованные данные

Саммари

Эта глава началась с того, что была заложена основа для ключевых концепций языка M, которые влияют на понимание сравнения, замены, соединения и разделения. Мы изучили эти методы, используя примеры. Эти методы окажут неоценимую помощь в повышении качества данных. Достижение мастерства потребует самоотверженности и регулярной практики в вашей повседневной жизни. Это инвестиция, которая стоит усилий, чтобы получить вознаграждение.

В следующей главе вы узнаете, что такое ошибка и как ошибки могут быть вызваны, ограничены и обнаружены, а также какие инструменты доступны в вашем арсенале для обработки ошибок.