Глава 14. Проблемные паттерны данных

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

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

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

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

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

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

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

• извлечение фиксированных шаблонов,
• извлечение и объединение данных.

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

Сопоставление с шаблоном

Сопоставление с шаблоном (Pattern matching) – важный навык, часто связанный с регулярными выражениями (regex), который необходим для идентификации и манипуляции определенными шаблонами в текстовых данных. Хотя язык М не поддерживает регулярные выражения напрямую, он предоставляет множество функций, которые помогают справиться с такими задачами. В этом разделе мы рассмотрим сопоставление с шаблоном в рамках ограничений Power Query, выделяя ключевые техники и стратегии для решения этих задач.

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

Чувствительность к регистру

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

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

Содержит против точного соответствия

При поиске "Power Query" в строке "Power Query is awesome!" вторая строка содержит первую, но точного совпадения нет. Требования к сопоставлению с шаблоном могут различаться – иногда вам нужно подтвердить наличие подстроки, как в случае с типом «contains», в то время как в других случаях необходимо точное совпадение. Функции Text.Start, Text.End, Text.Middle и Text.Range, могут возвращать подстроку для сопоставления.

Text.Contains предлагает простой метод для проверки наличия подстроки в строке. Text.PositionOf можно использовать для нахождения позиции подстроки в строке. Если функция возвращает число большее или равное 0, подстрока существует. Если возвращает -1, подстрока отсутствует.

Допустимые символы

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

Работа с одним или несколькими элементами

Вы работаете с одним словом или с несколькими: строкой/предложением? Если с несколькими, нужно ли их разбить на более мелкие единицы? При работе с несколькими элементами вы будете прибегать к операциям со списками: List.Select, List.Transform и др. Для возврата одного значения часто используется Text.Combine. Эту функция принимает список текстовых значений и объединяет их в одну строку. При необходимости, добавляя разделитель между элементами.

Подстановочные знаки

Подстановочные знаки – символы, используемые для представления одного или нескольких символов в текстовой строке. Они часто используются в операциях поиска и сопоставления с шаблоном. Хотя Power Query изначально не поддерживает подстановочные знаки, это не означает, что подстановочные знаки нельзя имитировать в M. Стандартные библиотечные функции Text.StartsWith и Text.EndsWith упрощают поиск строк, которые начинаются или заканчиваются определенным текстом. Иное использование подстановочных знаков зачастую более сложно и потребует разработки особой логики для их воспроизведения.

Извлечение фиксированных шаблонов

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

Пример 1, префикс

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

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

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

Это имя шага или переменной в запросе. За ним следует знак равенства и присваивается выражение для хранения значения.

Функция используется для создания таблицы из списка.

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

Необязательному второму аргументу присвоен тип таблицы. В этом случае указывают имя столбца и тип значений.

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

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

Пройдите Добавление столбца –> Настраиваемый столбец. Введите Code в поле Имя нового столбца. Инициализируйте выражение записи, введя набор квадратных скобок [ ] в поле Настраиваемая формула столбца. Внутри записи можно создать последовательность пар имя – значение, разделенных запятыми.

Введите n = Text.PositionOf([String], "DGPQ", Occurrence.First) внутри квадратных скобок. Здесь n – имя поля, а формула после знака равно – значение поля. Чтобы точно определить положение префикса DGPQ, мы использовали функцию Text.PositionOf. Если она вернет число, больше или равное 0, подстрока существует. При этом возвращается позиционный индекс первого символа в строке. Если подстрока не найдена, функция вернет -1.

Добавьте запятую и введите вторую пару имя значения в квадратных скобках result = Text.Range([String], n, 9). Функция Text.Range вернет 9 символов, начиная с позиции n из строки текста String.

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

Рис. 14.0. Настройка столбца Code

Нажмите OK.

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

Или, если вы предпочитаете вложенные выражения, то можно записать…

Рис. 14.1. Результат примера 1

Отлично! Хорошей практикой является пересмотр своего подхода и выявление ограничений и областей для улучшения. Предположим, что шаблон устойчив и не подвержен изменениям. Тогда основная проблема – отсутствие обработки ошибок. Обработка ошибок исправит ситуацию, когда подстрока DGPQ не найдена в тексте. В таких случаях функция Text.PositionOf возвращает -1. Если это не учитывать, возникнет ошибка: Expression.Error: Аргумент «offset» выходит за пределы диапазона. Добавим условный оператор…

… или (в учебных целях) немного перепишем код:

Вложение выражения let позволяет присвоить результат выражения переменной. Мы создали переменную с именем n, которая содержит результат функции Text.PositionOf. Теперь мы можем ссылаться на результат этого выражения (n) несколько раз, без необходимости повторять формулу в коде.

Выражение let всегда завершается предложением in. В in вы можете вернуть переменную или написать другое выражение. Здесь мы выбрали условный оператор, который выполняет логическую проверку. Если проверка завершилась значением true выполняется результирующее выражение. Если проверка вернула значение false, выполняется предложение else, и возвращается пустая текстовая строка. Эта проверка позволяет проверить, есть ли подстрока в строке перед выполнением функции Text.Range. Проверка предотвратит ошибку и обеспечит надлежащую работу кода в сценариях, где подстрока может отсутствовать во входной строке.

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

Пример 2, шаблон

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

Рис. 14.1а. Исходный набор данных для примера 2

Добавим столбец Code. Процесс потребует множества функций, и мы используем выражение записи. Скопируйте приведенный выше код, откройте редактор Power Query, пройдите Главная –> Создать источник –> Пустой запрос. Откройте расширенный редактор, удалите заготовку и вставьте текст из буфера обмена. Пройдите Добавление столбца –> Настраиваемый столбец. Укажите Code в поле Имя нового столбца. Введите набор квадратных скобок [ ] в поле Настраиваемая формула столбца. Введите пары имя–значения, разделяя их запятыми:

Допустимые символы – это прописные буквы и цифры. Давайте включим пробел. Этот пробел можно использовать для разделения каждой строки на более мелкие единицы или слова. С помощью функции Text.Select мы отберем только эти символы из входной строки. Добавьте запятую в конце, чтобы создать новое поле в этой записи.

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

Для начала мы выберем любой элемент списка с общей длиной в девять символов.

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

Рис. 14.1б. Настройка столбца Code в примере 2

Нажмите OK. В строке формул появится код для второго шага Code:

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

Рис. 14.2. Результат примера 2

Успех! Но, что мы упустили, и что можно улучшить? Вот о чем следует подумать:

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

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

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

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

Выберите любой элемент из этого списка r = List.Select(w, each, соответствующий шаблону. Т.е., имеющий общую длину в девять символов (Text.Length(_) = 9), где первые четыре символа – это буквы (Text.Start(_, 4) = Text.Select(_, {"A".."Z", "a".."z"})), а следующие пять – цифры (Text.Range(_, 4, 5) = Text.Select(_, {"0".."9"})). Закройте скобку в функции List.Select и добавьте запятую.

Извлеките и объедините все результаты в одну строку. Примените доступ к полю, чтобы получить значение для a. В итоге получим код для шага Code:

Рис. 14.3. Результат улучшенного кода для примера 2

Пример 3, разделители

Функции разделителей (splitter) в языке M особенно полезны для извлечения шаблонов. Эти функции были подробно рассмотрены в главе 11 Сравнение, замена, соединение, разделение. Однако, поскольку форматы данных с фиксированной шириной очень распространены во многих устаревших системах, будет уместно рассмотреть этот конкретный сценарий здесь. В отличие от форматов, где поля разделяются символами, такими как запятые или табуляции, форматы с фиксированной шириной полагаются исключительно на позицию данных в строке. Это различие часто представляет собой вызов: поля встроены в большой блок текста без явных разделителей.

Рис. 14.3а. Пример 3; чтобы увидеть структуру данных включите опции на вкладке Просмотр

Для обработки данных фиксированной ширины используем Splitter.SplitTextByPositions. Этот разделитель принимает список позиционных индексных номеров. Каждый индекс должен быть больше или равен 0, а каждое следующее значение должно быть равно или больше предыдущего. Эти индексы указывают отсчитываемое от нуля место в тексте, где происходит разбиение, что идеально согласуется со структурой, присущей форматам данных фиксированной ширины. В следующей таблице показано, как определить эти позиции на основе известных длин полей устаревшей системы и, что очень важно, также указан порядок полей:

Рис. 14.3б. Определение позиций и порядка полей

Из каждой строки данных мы хотим извлечь код товара (Item Code), код поставщика (Supplier Code) и код производителя (Manufacturer Code). Мы продемонстрируем два метода, первый из которых использует Splitter.SplitTextByPositions. Зная, как определить каждую позицию, и имея порядок полей (рис. 14.3б), мы можем легко решить эту задачу.

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

Splitter.SplitTextByPositions принимает один аргумент – список с позициями, и возвращает функцию. Чтобы вызвать эту функцию, добавьте еще один набор скобок, который является оператором вызова функции, и передайте имя столбца. Не забудьте про запятую в конце.

В таблице 14.3б мы найдем порядок полей и отсчитываемый от нуля индекс каждого поля. Код товара (1), код поставщика (3) и код производителя (4) соответствуют позиции списка в s. Мы можем создать список с этими индексами для итерации, извлекая эти элементы и удаляя лишние пробелы за одну операцию.

Можно вместо х использовать нижнее подчеркивание в качестве единственной переменной:

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

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

Рис. 14.4. Результат примера 3

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

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

В главе 8 Работа с вложенными структурами мы показали, как функция List.Zip формирует новый список списков, связывая элементы из всех своих входных списков с соответствующими индексными позициями, где каждый вложенный список содержит элемент из List 1 (позиции), за которым следует элемент из List 2 (длина поля). Для повышения эффективности, тем более что мы будем обращаться к этому списку неоднократно, мы сохраним его в памяти. Хотя этот конкретный код не ссылается напрямую на переменную из выражения let, мы рекомендуем придерживаться стиля и разместить его над шагом Source:

Используем исходные данные с рис. 14.3а. Сосредоточимся на логике формулы настраиваемого столбца. Инициализируем выражение записи и назовем первое поле r. Мы знаем порядок полей из устаревшей системы, индекс каждого поля, отсчитываемый от нуля, и стремимся извлечь код товара (1), код поставщика (3) и код производителя (4) из текстовой строки. Это можно сделать, поместив их позиции в список для итерации:

Мы извлечем всю длину каждого поля, включая конечные пробелы, и удалим последние:

Внутри Text.Trim используем функцию Text.Range для извлечения трех полей, передавая строку из Column1 в качестве первого аргумента:

Обсудим подробнее второй аргумент. Переменная s представляет собой список списков, соответствующих каждому полю в строке. Мы можем получить соответствующий список с помощью s{x}, где x – текущее значение из списка, который мы итерируем внутри List.Transform. Поскольку результатом s{x} является список с двумя элементами, первый из которых является позицией, а второй – длиной поля, мы можем еще раз применить доступ к элементу для извлечения значения позиции, добавив {0}. Аргумент второго параметра Text.Range должен выглядеть следующим образом: s{x}{0}.

Третий аргумент Text.Range – количество возвращаемых символов. Но ведь это второй элемент в каждом из вложенных списков s{x}. Чтобы получить его значение, укажем: s{x}{1}.

Не забудьте про закрывающую скобку функции Text.Range.

Определите второе поле с именем a и задайте его значение следующим выражением для объединения всех результатов:

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

Полный код альтернативного варианта примера 3:

Пример 4, замена

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

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

В этом сценарии допустимые коды определены следующим образом: они начинаются с четырех букв – заглавных, строчных или их комбинации, за которыми следует разделитель: дефис (-) или точка (.), затем две цифры, еще один разделитель и, наконец, набор из трех цифр. Общая длина шаблона постоянна и равна 11 символам. Целевые коды могут быть такими: ABCD-12-345, efgh-67.890 или IjkL.01.321.

Помимо основной замены значений, оставшиеся шаги схожи с альтернативным подходом с использованием Splitters из Примера 3. Создайте список замен. Он включает значение замены для каждого допустимого символа и загружается в память. Хотя шаг replacements не связан с переменной из выражения let, придерживаясь лучших практик мы разместим его до шага Source:

Добавьте в таблицу пользовательский столбец и инициализируйте выражение записи [ ]. Используйте List.ReplaceMatchingItems для замены всех символов в каждой строке. Для этого требуется список замен в формате {oldValue, newValue}, а также Comparer.OrdinalIgnoreCase, чтобы исключить влияние регистра букв. После проведения замен объедините все элементы списка, чтобы сформировать одну текстовую строку:

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

Используйте список поиска для итерации и извлечения результатов из входной строки:

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

Рис. 14.5. Результат примера 4

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

Добавьте пользовательский столбец Code и инициализируйте выражение записи: [ ]. Разбейте каждую строку на слова:

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

Из оставшихся элементов списка извлеките первые 11 символов:

Извлеките значение из поля a, применив доступ к полю – [а].

Итоговый код шага AddCode:

Пример 5, регулярное выражение

Power Query не поддерживает регулярные выражения, но их можно реализовать с помощью функции Web.Page для выполнения кода JavaScript (JS). Производительность этого метода страдает, особенно при применении к большим наборам данных. Убедитесь, что преимущества метода перевешивают недостатки.

В следующем примере мы хотим извлечь почтовые индексы – 5 цифр подряд:

Создадим пользовательскую функцию на основе Web.Page для запуска JavaScript. Нам потребуется текстовое значение с тегом <script> для вставки кода JavaScript в документ HTML. Все, что находится между <script> и </script> выполняется как JavaScript. Наша задача – создать JS-код, выполняющий операцию с регулярным выражением. Ему необходимо идентифицировать и извлечь последовательности из пяти цифр из входной текстовой строки, переданной пользовательской функции. После этого функция Web.Page выведет таблицу, показывающую содержимое HTML-документа, организованную по основным элементам. Затем можно получить доступ к этой таблице для получения результатов. Хотя мы не являемся экспертами в JS-коде, вот как это может выглядеть. Придерживаясь лучших практик, мы реализуем эту пользовательскую функцию над шагом Source:

Подробнее о коде функции:

Объявляется функция с именем fxRegex , которая принимает один параметр input типа text. Функция вернет значение текстового типа.

… создает веб-страницу в памяти, и внутри себя запускает JavaScript (текстовая строка в паре двойных кавычек), который поддерживает регулярные выражения. Все, что находится между тегами <script> и </script>, обрабатывается и выполняется как код JavaScript.

Объявляет переменную JavaScript с именем a. Одинарная кавычка (‘) после знака равенства инициирует строку в JavaScript. Двойная кавычка (") сигнализирует об окончании текстовой строки в M, временно пропуская нотацию JS. Это позволяет конкатенировать входное значение параметра (& input) в коде M. После внедрения входного значения M конкатенирует новую строку (& ") и продолжает работу в JavaScript, закрывая строку одинарной кавычкой (‘).

Объявляет переменную b в JavaScript и использует метод match JavaScript с шаблоном регулярного выражения, где \d соответствует любой цифре, {5} указывает ровно пять цифр подряд, а g – это глобальный флаг для поиска всех совпадений.

Подробнее о регулярных выражениях см. Джеффри Фридл. Регулярные выражения.

Записывает результат совпадения регулярного выражения в HTML-документ.

Эта часть обращается к выходным данным, сгенерированным Web.Page, и перемещается по иерархии html-документа для получения текста, записанного с помощью document.write(b). Несколько раз применяется доступ к элементам и полям для извлечения содержимого.

Логику функции fxRegex мы применяем к каждой строке в таблице, сохраняя результаты в столбце Postal Code. Поскольку пользовательская функция такая же, как и любая другая функция в M, мы добавим Пользовательский столбец и введем: fxRegex( [Address] ). Шаг InvokedCF:

Рис. 14.7. Результат вызова функции fxRegex

Метод JavaScript поддерживается в Power BI Desktop и Excel, но недоступен в службе Power BI. В службе Power BI регулярные выражения можно реализовать с помощью скриптов на Python или R.

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

Чтобы добавить столбец Postal Code в таблицу, пройдите Добавление столбца –> Настраиваемый столбец. Введите Postal Code в качестве имени столбца. Инициализируйте выражение записи, введя набор квадратных скобок [ ] в поле Настраиваемая формула столбца. Введите три записи:

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

Выберите все элементы из списка, которые имеют общую длину 5 символов.

Объедините результаты в одну строку. Извлеките значение поля, применив доступ к полю к записи […][а]. Итоговый код шага Extracted2:

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

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

Объединение данных

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

Основы объединения данных

Процесс ETL (Extract, Transform and Load; извлечение, преобразование и загрузка) начинается с подключения к источнику данных. Вот некоторые моменты, о которых следует помнить, особенно при работе с несколькими файлами:

Расположение данных. Это уникальный адрес. Например, путь к локальному / сетевому диску или URL-адрес. Местоположение и имена файлов не статичны. Они могут меняться со временем по разным причинам, например, миграция систем, реорганизация данных и др. Здесь вступают в игру параметры. Они действуют как переменные и позволяют хранить и управлять значением, которое можно использовать в запросе. Параметры позволяют обновлять и изменять значение один раз, а не в каждом запросе, в котором используется это значение. Подробнее см. глава 9 Параметры и пользовательские функции.

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

Выбор данных. Данные могут храниться в папке и распределены по нескольким файлам. Важно знать, какие файлы следует рассмотреть, и если требуется отбор, то какие критерии применять. Например, диапазон дат, расширение файла или другой атрибут.

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

Структура данных. Поскольку мы говорим о файлах Excel, изучите, как данные структурированы в каждом файле. Они расположены на листах или в таблицах? Унифицированы ли названия таблиц для удобства отбора? Для данных на листах, как определять, где находятся данные?

Согласованность данных. Проверка на единообразие в разных файлах, например совпадение имен столбцов, согласование типов значений в столбцах с одинаковыми именами и т. д. Могут иметься соглашения, которые делают имена столбцов уникальными. Например, 1-1-2024 Stock On Hand. Столбцы с такими именами будут уникальными. Но вы можете извлечь дату, и унифицировать названия.

Сбор данных. Если данные в выбранных файлах одинаковые, их можно объединить в общую таблицу. Если нет, то вероятно, это различные наборы данных. Помните, что для каждой таблицы должен быть разработан отдельный запрос. Представьте себе файлы из финансового отдела, в каждом из которых есть баланс, бюджет, отчет о прибылях и убытках, таблица расходов и др. Очевидно, что они описывают разное. Какие данные необходимо собрать для анализа?

Преобразование данных. Нужно ли преобразовать данные перед объединением? Есть ли закономерности или проблемы в данных, которые нужно устранить? Можно ли применить динамические преобразования? Нужен отчет об ошибках? Простая и эффективная стратегия оптимизации модели данных – использовать только то, что нужно. Ранний отбор столбцов и строк может дать дополнительное преимущество в виде свертывания запросов. Подробнее см. в главе 15 Оптимизация производительности.

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

Извлечение, преобразование и объединение

Здесь мы обсудим работу с несколькими файлами Excel, хранящимися в одной папке. Рекомендую работать с примерами. Скачайте файлы с репозитория GitHub. Существует множество методов для достижения желаемого результата. Мы проведем вас через одно конкретное решение. Скриншоты взяты из Power BI Desktop версии 2.124.1805.0. Если вы используете другую версию, могут быть некоторые отличия.

Мои скриншоты взяты из Power BI Desktop версии 2.132.908.0 (август 2024).

Получение и проверка данных

Данные содержатся в девяти файлах. Три первых мы объединим, чтобы получить результат:

Рис. 14.9. Файлы для объединения и файл ожидаемого результата

Откройте эти три файла. Все они имеют одинаковую структуру:

Рис. 14.10. Фрагмент одного из файлов для объединения

А вот, что мы хотим получить:

Рис. 14.11. Фрагмент ожидаемого результата

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

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

Параметр местоположения

Допустим мы храним загруженные файлы в папке Sample files. Вместо того чтобы жестко зафиксировать этот путь в запросе, создадим параметр. Значение параметра можно обновить без необходимости изменять код M и даже не открывая редактор Power Query.

Чтобы создать параметр в редакторе Power Query в области Запросы щелкните правой кнопкой мыши на свободном месте и выберите Создать параметр, или пройдите по меню Главная –> Управление параметрами –> Создать параметр:

Рис. 14.12. Быстрое создание нового параметра в области Запросы

Настройте окно Управление параметрами. Убедитесь, что установлен флажок Требуется. В поле Тип выберите Текст и укажите Текущее значение. Каждый параметр хранится и отображается как отдельный запрос в области Запросы. В M имя запроса считается идентификатором. Когда имя запроса содержит пробелы, его нужно заключить в кавычки. Чтобы избежать этого, выберите имя без пробела, например FolderLocation.

Рис. 14.13. Диалоговое окно Управление параметрами

Подключение к данным

В редакторе Power Query пройдите по меню Главная –> Создать источник –> Дополнительно –> Папка. В окне Папка переключите тип ввода с Текст (обозначается значком ABC) на Параметр, выберите FolderLocation в качестве значения. Нажмите OK, отобразится содержимое папки:

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

Кнопки управления предлагают следующую функциональность:

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

Преобразовать данные. Эту кнопку следует выбрать для ручного управления отбором и преобразованием файлов в редакторе Power Query перед объединением данных.

Отмена. Прервет операцию подключения к данным.

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

После настройки подключения пришло время выбрать файлы для анализа.

Фильтрация файлов

Единственное преобразование, которое нам нужно применить к Запросу1 – фильтр для выбора трех верхних файлов, изображенных на рис. 14.9. Начнем с того, что в интерфейсе по столбцу Extension выберем Текстовые фильтры –> Начинается с… и введем .xls. Нажмите OK. Создастся шаг запроса с кодом:

Используя код созданный интерфейсом в качестве базы, дополним его двумя опциями. Чтобы сравнение выполнялось без учета регистра, передадим в функцию Text.StartsWith третий параметр Comparer.OrdinalIgnoreCase. Дополним фильтр отбором файлов, название которых начинается с Daily_:

Общая стратегия

Коннектор папок Power Query использует выбранный пользователем файл-образец для разработки логики преобразования, которая будет одинаковой для всех файлов в папке. Но этому процессу не хватает гибкости для выбора конкретных файлов или извлечения определенных наборов данных (таблиц или листов). Мы создадим решение, похожее на Объединить и преобразовать данные, но привнесем в него гибкость. Шаги нашего решения будут включать:

• удаление пустых строк и столбцов;
• извлечение названия компании из заголовка документа;
• извлечение основной части данных;
• обработку многострочных заголовков.

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

Выберите образец файла

Значение Binary в столбце Content (рис. 14.14) содержит данные файла Excel. Думайте о Binary как о значении, которое нужно преобразовать. Изучив файлы, мы нашли Daily_2.xlsx подходящим для образца. Теперь:

• щелкните правой кнопкой мыши пробел рядом с двоичным значением для xlsx;
• выберите Добавить как новый запрос;
• переименуйте этот новый запрос в BinarySample.

Рис. 14.14а. Бинарное содержимое запроса BinarySample

Параметр файла

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

• пройдите Главная –> Управление параметрами –> Создать параметр;
• дайте параметру говорящее имя BinaryFile;
• поставьте галку Требуется, выберите Тип = Двоичный;
• в полях Значение по умолчанию и Текущее значение установите BinarySample.

Шаблон преобразования

Мы продолжим настройку запроса, который принимает параметр BinaryFile в качестве входного значения:

• щелкните правой кнопкой мыши запрос BinaryFile;
• выберите Ссылка; будет добавлен новый запрос и в области предварительного просмотра отобразится двоичное содержимое файла xlsx, как на рис. 14.14а.
• дважды щелкните файл, чтобы вызвать функцию Workbook;
• переименуйте этот запрос в TransformData.

Код запроса…

… а в окне предварительного просмотра появится таблица:

Рис. 14.16. Содержимое файла образца

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

Чтобы справиться с возможными несоответствиями между файлами, которые затрудняют идентификацию данных, внедрим проактивные меры. Вместо того чтобы сразу раскрыть столбец Data, используем защитный механизм, устраняющий некоторые несоответствия, например регистр букв. Мы применим фильтры к строкам, Kind = Sheet и Name = Daily, и в первом случае выполним сравнение нечувствительное к регистру:

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

Внутри строки формул, после закрывающей скобки, примените доступ к необязательному элементу, {0}?, чтобы получить первую запись из таблицы. Далее укажите необязательный доступ к полю, [Data]?, чтобы вернуть значение из запрашиваемого поля. На панели Примененные шаги переименуйте этот шаг в RAW. В результате возвращается содержимое листа Excel:

Рис. 14.17. Предварительный просмотр данных

Удаление пустых строк

В данных довольно много пустых столбцов и строк. Логично удалить их, и в интерфейсе есть такая функция для строк. Пройдите Главная –> Удалить строки –> Удалить пустые строки. Переименуйте шаг в NoEmptyRows. Команда сгенерит код:

Удаление пустых столбцов

Функции для удаления пустых столбцов в интерфейсе нет. Используем логику шага NoEmptyRows. Скопируйте часть кода M от слова each до предпоследней закрывающей скобки:

Нажмите f_Х перед строкой формул, чтобы вставить шаг вручную. В строке формул вы увидите название предыдущего шага. Поместите курсор перед ним и введите Table.SelectColumns(

Переместите курсор в конец, поставьте запятую. Второй аргумент ожидает списка с именами столбцов. Мы предоставим его выражением, создающим список с именами столбцов на основе условия. Введите List.Select(. Передайте функции имена столбцов текущей таблицы: Table.ColumnNames(NoEmptyRows). Поставьте запятую и вставьте скопированную логику в качестве условия. Эта операция будет выполняться не построчно, а столбец за столбцом, поэтому Record.FieldValues(_) замените на Table.Column( NoEmptyRows, _), создав список со значениями столбцов. Добавьте нужное число закрывающих скобок. Переименуйте шаг в NoEmptyCols. Если вы нигде не ошиблись, у вас должно получиться:

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

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

В коде есть три ссылки на таблицу шага NoEmptyRows. Логично передать эту ссылку в качестве параметра функции. Чтобы создать пользовательскую функцию откроем запрос в расширенном редакторе. Разместим код функции над шагом Source:

• Скопируйте выражение, назначенное шагу NoEmptyCols.
• Добавьте пустую строку над Source и введите: fxNoEmptyCols = .
• Прежде чем мы вставить логику, мы хотим превратить ее в пользовательскую функцию. Инициируем функцию, введя круглые скобки и знак перехода: () =>. Для функции нужен один параметр – входное значение табличного типа. Назовем его tbl и укажем тип: tbl as table. Функция возвращает также значение типа table: (tbl as table) as table =>. Теперь можно вставить скопированный код. Замените все ссылки на таблицу параметром tbl.

Если вы все сделали верно, код функции:

Теперь, когда у нас есть пользовательская функция, нужно обновить код шага NoEmptyCols:

Извлечение данных из заголовков

Мы планируем удалить все строки заголовка документа (рис. 14.18). Но перед этим необходимо извлечь ключевую информацию. Последнее слово во второй строке, Name2, является идентификатором свойства, который мы хотим поместить в новый столбец в итоговом выводе:

Рис. 14.18. Строки заголовка документа

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

Кодируя логику, используем выражение записи, так как оно позволяет заглянуть внутрь и увидеть промежуточные результаты во время разработки и при устранении неполадок. Откройте расширенный редактор, создайте новую переменную за выражением let, назовите ее GetPropertyID. Инициализируйте выражение записи: [ ]. Первому полю PropertyString присвоим выражение для получения второй строки таблицы в виде записи:

Преобразуем запись в список, обернув это выражение функцией Record.ToList(…). Извлечем первый элемент списка, обернув функцией List.First(…). Итого PropertyString вернет текст. Добавьте запятую в конце, чтобы создать новое поле для записи GetPropertyID. Назовем второе поле PropertyID и присвоим ему выражение для разделения строки:

Извлечем последний элемент списка, обернув в List.Last(…). Вернем значение через доступ к полю. Код записи GetPropertyID:

Значение PropertyID мы используем позднее в коде.

Удаление строк

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

Рис. 14.18а. Идентификация колонтитулов

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

На панели Примененные шаги перейдите к последнему шагу NoEmptyCols, и отфильтруйте по любому столбцу. Это сгенерирует новый шаг и базовый код. Замените второй аргумент на этом шаге кодом, скопированным из NoEmptyRows. Внутренний метод Record.FieldValues(_) возвращает список значений полей в том же порядке, что и порядок столбцов в таблице. Чтобы опустить значения из первого столбца, оберните это выражение функцией List.Skip(…). Переименуйте шаг в RemoveRows. Код этого шага:

В самом низу останутся две лишние строки. Пройдите Главная –> Удалить строки –> Удалить нижние строки –> 2, чтобы убрать их, и переименуйте шаг в GetDataRange.

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

Новые заголовки

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

Рис. 14.19. Верхняя часть промежуточной таблицы

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

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

Пройдите Главная –> Сохранить строки –> Сохранить верхние строки –> 3. В строке формул отразится код:

Поместите курсор после знака равно и введите Table.TransformRows(. Поместите курсор в конец, введите запятую, и второй аргумент each Record.ToTable(_). Добавьте закрывающую скобку, чтобы посмотреть на промежуточные результаты:

Рис. 14.19а. Трансформация трех строк в таблицы с двумя столбцами Name и Value

Чтобы заполнить значения в столбце Value, поместите выражение Record.ToTable(_) внутрь функции Table.FillDown( …, {"Value"}). Извлеките столбец Value в виде списка, добавив [Value] после закрывающей скобки функции Table.FillDown и перед закрывающей скобкой функции Table.TransformRows. При этом возвращается список с вложенным списком для каждой строки:

Рис. 14.20. GetHeaderRows создает список списков

Переименуйте шаг в GetHeaderRows. Поместите шаг первым, сразу после let. Его код:

Сохраняя модульный подход, сейчас самое время выделить логику в новый шаг. Вставьте ручной шаг, нажав f_Х. Значения нужно комбинировать по их позиции: List.Zip( GetHeaderRows ). Затем все элементы списка нужно преобразовать в одно текстовое значение для каждого столбца: List.Transform( …, each Text.Combine( _, " | ")):

Рис. 14.21. Список заголовков, показывающий первые пять элементов

Переименуйте шаг в Headers:

Пришло время получить основные данные и назначить столбцам извлеченные заголовки. Перейдите к последнему шагу запроса – GetDataRange. Нажмите f_Х. Пропустите три верхние строки: Table.Skip( GetDataRange, 3). Переименуйте столбцы, используя List.Zip для создания списков имен:

Переименуйте шаг в GetTable. Код шага:

Типы данных

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

Для примера зададим тип столбца Date. Нажмите ABC123 слева от названия столбца, выберите Используя локаль. В открывшемся окне выберите Тип данных = Дата и Языковый стандарт = Английский (США). Это создает следующий синтаксис:

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

Изучите выражение в строке формул. Важно понимать, что мы имеем дело с вложенной структурой – список списков, формат которого { columnName, typeName }. Для каждого столбца, для которого необходимо установить тип, должен быть свой список. Этот шаблон можно сделать динамичным и более кратким:

• в строке формул удалите шаги, связанные с изменением типов;
• нажмите f_Х, введите Transform(.
• передайте функции список Headers в качестве первого аргумента (этот список содержит все имена столбцов);
• преобразуйте каждое имя столбца в формат { columnName, typeName }, например, так: each {_, type text}; не забудьте ввести закрывающую скобку функции.

Переименуйте шаг в SetColTypes. Должно получиться:

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

Добавление поля

На последнем шаге преобразования добавим отдельным столбцом идентификатор свойства, GetPropertyID. Пройдите Добавление столбца –> Настраиваемый столбец. Введите имя столбца Property ID. В разделе формул введите =GetPropertyID. Нажмите OK. Переименуйте шаг в InsertPropertyID. В строке формул присвойте тип этому столбцу, поставив запятую перед закрывающей скобкой, за которой введите type text. Код шага:

Пришло время подумать, стоит ли и как бороться с пустыми файлами и ошибками.

Пустой файл

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

Переименуйте шаг в Result.

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

Нет никаких технических требований для сохранения переменной Result в качестве последнего шага запроса. Это не влияет на работу кода. Тем не менее, такое размещение делает взаимодействие с интерфейсом прозрачным, облегчая доработку запроса и исправление ошибок. Рекомендуем отразить это в комментарии. Щелкните правой кнопкой мыши на имени шага Result, выберите Свойства и введите Описание. Нажмите OK. После этого в разделе Примененные шаги рядом с шагом Result появится знак с информацией.

Рис. 14.23. Вставка комментария к шагу

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

Преобразования запроса в функцию

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

В области Запросы щелкните правой кнопкой мыши TransformData и выберите Создать функцию. В диалоговом окне введите имя функции CollectData. Нажмите OK.

Рис. 14.24. Диалоговое окно Создать функцию

Это действие сгенерирует запрос fxCollectData и поместит параметр BinaryFile, запрос TransformData и функцию fxCollectData в папку с именем fxCollectData.

Организуйте запросы, переместив FolderLocation и BinarySample в папку fxCollectData. Это можно сделать, перетащив их или щелкнув правой кнопкой мыши и выбрав Переместить в группу.

Вернитесь к Запрос1, переименуйте его в CombinedData. Сохраните столбцы Name и Content. Это можно сделать через интерфейс. Выберите столбцы Name и Content и пройдите Главная –> Удалить столбцы –> Удалить другие столбцы. Или, находясь на шаге SelectFiles применить проекцию, добавив [[Name], [Content]] сразу за закрывающей скобкой. Для определенности мы будем считать, что использовали проекцию.

Существует несколько методов преобразования структурированных значений в таблице. Они были рассмотрены в главе 8 Работа с вложенными структурами. Мы используем функцию Table.TransformColumns. Нажмите f_Х. Введите функцию между знаком равенства и именем переменной: Table.TransformColumns(. В качестве второго аргумента функция ожидает список операций преобразования. Введите {«Content», each fxCollectData(_)}. Добавьте закрывающую скобку и переименуйте шаг в InvokedFunction:

Рис. 14.25. Содержимое столбца Content после вызова fxCollectData

После преобразования значений в столбце Content обнаруживается пустой файл. Нужно реализовать процесс мониторинга, когда запрос TransformData обращается к пустому файлу.

Настройка мониторинга

Настроить мониторинг для получения уведомлений о пустых файлах в сервисе Power BI довольно просто. Создайте дубль запроса CombinedData и переименуйте его в EmptyFiles. Затем примените фильтр в столбце Content, чтобы отсечь строки, содержащие сообщение "File is empty.":

Так как запрос EmptyFiles предназначен для облегчения мониторинга, полезно включить путь к файлу в результат. Выберите шаг Source. Можно увидеть столбец, содержащий путь – Folder Path. Теперь выберите шаг SelectFiles. Здесь мы применили проекцию для ограничения столбцов. Чтобы включить столбец Folder Path и поместить его первым в результирующей таблице, добавьте его в проекцию:

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

Эту меру можно поместить в визуальный элемент, например карточку, чтобы установить пороговое значение в сервисе Power BI. Как только пороговое значение будет превышено, вы получите автоматическое уведомление для принятия решения.

Тонкая настройка

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

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

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

• Начните с определения файла, который вызывает ошибку во время обработки.
• Перейдите к запросу BinarySample и выберите шаг SelectFiles.
• Разверните столбец Content для выявленного файла.
• Удалите шаг Imported Excel workbook, если он был сгенерирован автоматически, чтобы отобразить выбранный файл как файл в панели предварительного просмотра.
• Перейдите к запросу CollectData и определите шаг, на котором возникают ошибки.
• Внедрите дополнительную логику или обновите код, чтобы устранить проблему.
• Выберите запрос CombinedData и убедитесь, что проблем больше нет. Если ошибки продолжают возникать, повторите эти шаги, пока все проблемы не будут устранены.

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

Без включения отчетности об ошибках вы можете оставить ошибки, чтобы они вызывали сбои, сигнализируя о наличии проблемы, которую нужно решить. Сейчас мы предполагаем, что отчетность об ошибках уже реализована, и можно безопасно исключить все строки с ошибками. Пройдите Главная –> Удалить строки –> Удалить ошибки, переименуйте шаг в RemoveErrors.

Теперь можно объединить содержимое всех файлов.

Объединение файлов

Нажмите развернуть столбец в заголовке столбца Content. Отключите опцию Использовать исходное имя столбца как префикс внизу окна. Нажмите OK. Это добавит новый шаг в запрос. В строке формул вы увидите, что функция Table.ExpandTableColumn получила два жестко закодированных списка. Первый список содержит columnNames для развертывания из каждой вложенной таблицы, а второй список, newColumnNames, назначает имена новым столбцам. Это помогает избежать конфликтов между именами существующих и новых столбцов. Однако риск того, что файл неожиданно будет включать поля с именами Name или Content, крайне низок, поэтому финальный список можно удалить. Что касается первого списка, его можно заменить функцией, которая динамически предоставляет все имена столбцов из запроса CollectData. После этих изменений выражение в строке формул будет выглядеть так:

Переименуйте шаг в ExpandAllColumns.

Успешно объединив данные из всех трех файлов, каждый из которых соответствует неделе данных, мы получили одну полную таблицу, состоящую из 21 строки и 22 столбцов. Все значения отформатированы как текст, аналогично файлу ExpectedCombined.xlsx. Скомпилировав и структурировав все необходимые данные в соответствии с заданными спецификациями, мы готовы перейти к фазе преобразований для аналитики.

Резюме

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

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