Глава 3. Доступ к данным и их объединение

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. Учитывая, что M – язык для извлечения и преобразования данных, естественно, что в нем большое количество функций, предназначенных для извлечения данных из различных систем, таких как файлы, папки, базы данных, веб-страницы, а также стандартизированные структуры данных и протоколы, такие как расширяемый язык разметки (XML), JavaScript Object Notation (JSON) и протокол открытых данных (OData).

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

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

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

В M более 100 стандартных функций для подключения к данным. М также предоставляет возможность написания расширений для получения данных из систем собственной разработки, например, системы управления взаимоотношениями с клиентами (CRM) или системы управления складом (WMS). Создание таких расширений описано в главе 16 Включение расширений. Очевидно, что ни одна книга не может охватить столько функций. В этой главе мы дадим общее описание распространенных функций доступа к данным с примерами, применимыми к большинству других подобных функций.

В дополнение к извлечению данных M также предоставляет возможности для объединения данных из нескольких источников. В М различают:

• добавление данных (append);
• объединение данных (join/merge). Объединение возможно даже когда значения данных в объединяемых ключевых столбцах похожи, но не идентичны – нечеткое сопоставление.

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

Рис. 3.0. Два варианта соединения данных

Мы рассмотрим следующие вопросы:

• Доступ к файлам и папкам
• Получение веб-содержимого
• Бинарные функции
• Доступ к базам данных и кубам
• Работа со стандартными протоколами передачи данных
• Дополнительные коннекторы
• Добавление и объединение данных

Для выполнения примеров вам потребуется Power BI Desktop. Исходные файлы включены в репозиторий GitHub.

Доступ к файлам и папкам

Многие пользователи работают с M, получая доступ к файлам и папкам на локальном компьютере, сетевом диске или в облачном хранилище. Часто данные извлекают из файлов Excel, папок, файлов с разделителями-запятыми (CSV). Ниже перечислены форматы, поддерживаемые M и функции, отвечающие за извлечение данных:

Рис. 3.0а. Функции М для различных форматов файлов

Форматы данных могут быть довольно разнообразными, и предпочитаемые форматы менялись с течением времени. Кроме того, существуют специализированные форматы, например, RData, используемый в языке R для статистического анализа. В связи с распространением больших данных появилась система HDFS, Hadoop Distributed File System. Наконец, появление озер данных и озерных домиков привело к тому, что стали популярными такие форматы файлов, как Parquet.

Начнем с основной функции доступа к файлам – File.Contents.

File.Contents

Функция File.Contents является базовой для доступа к файлам и папкам. Редко используемые сами по себе, выходные данные функции File.Contents обычно служат входными данными для первого аргумента других функций доступа к содержимому файлов: Csv.Document, Excel.Workbook, Json.Document, Xml.Document и Xml.Tables.

Функция File.Contents принимает два параметра: обязательный – путь к файлу и необязательный options – запись с параметрами, которая содержит варианты обработки файла. Если вы знакомы с другими языками программирования, то концепция параметров, используемых с функциями, точно такая же. Если вы не знакомы с функциями и параметрами, об этом будет рассказано в главе 9 Параметры и пользовательские функции. Записи рассматриваются в главе 6 Структурированные данные.

Выходными данными функции File.Contents является двоичное содержимое файла.

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

Рис. 3.0б. Описание функции File.Contents в документации Microsoft

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

В области предварительного просмотра редактора Power Query возвращается документация по функции:

Рис. 3.1. Документация по File.Contents в редакторе Power Query

Механизм, лежащий в основе предоставления этой документации, описан в главе 7 Концепция M.

На рис. 3.1 видно, что имеется один необязательный параметр PreserveLastAccessTimes. ChatGPT сообщил: если параметр установлен в true, то при обновлении данных через функцию File.Contents в свойствах файла будет сохраняться время последнего обращения к файлу. Если параметр установлен в false (по умолчанию), при обновлении данных через функцию File.Contents время доступа к файлу не будет обновляться. Экспериментируя мне не удалось обнаружить эффект…

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

В М представлено более 10 функций *.Contents, которые возвращают двоичное содержимое. Например, если файл размещен на веб-сайте (у него есть URL), следует воспользоваться функцией Web.Contents, сведения о которой буду даны ниже в этой главе.

Рассмотрим использование File.Contents для извлечения данных из CSV-файла.

Текст/CSV

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

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

Чтобы создать запрос к CSV-файлу в редакторе Power Query пройдите Главная –> Создать источник –> Текстовый или CSV-файл. Найдите на диске файл Avocado Prices.csv, кликните Открыть. В диалоговом окне Авокадо Prices.csv нажмите Ok.

Откройте расширенный редактор. Шаг Источник будет иметь следующий вид:

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

Функция File.Contents используется для получения содержимого файла Avocado Prices.csv в виде двоичных данных. Выходные данные функции File.Contents используются в качестве первого параметра для функции Csv.Document. В коде 3.2 второй параметр функции Csv.Document – это запись с параметрами:

• Разделитель (Delimiter) указывает, что столбцы разделяются запятой.
• Столбцы (Columns) указывает, что данные содержат 14 столбцов.
• Кодировка (Encoding) указывает кодовую страницу 1251 (кодировка символов Windows). Кодовая страница – это спецификация того, как печатные и непечатаемые символы (например, возврат каретки и перевод строки) связаны с числами (кодами).
• Стиль кавычек (QuoteStyle) указывает, как обрабатывать разделители внутри кавычек; значение QuoteStyle.None требует использовать все разделители, даже если они находятся внутри фрагментов текста с кавычками.

На самом деле работа параметра QuoteStyle довольно сложная тема. Я разобрал её в разделе Опциональный аргумент quoteStyle заметки Функция Table.FromList M Power Query. Но, похоже, требуется еще один подход))

У функции Csv.Document существует и иной формат.

Подробнее см. Крис Уэбб. Функция Csv.Document M Power Query. Два варианта синтаксиса:

Функция Csv.Document определяет, как вести обработку в зависимости от типа значения второго параметра. Если это запись, то обрабатывается как 3.3. Если это не запись, то, обрабатывается как 3.4. Рассмотрим вариант 3.4 подробнее.

Параметр columns может быть null, типом таблицы, списком имен столбцов, количеством столбцов.

Параметр delimiter по умолчанию использует символ запятой (,). Значение разделителя пустой текстовой строки ("") указывает, что строки должны быть разделены на столбцы на основе последовательных пробельных символов.

Для использования специальных символов, например табуляции, применяют escape-последовательность. Формат #(<символ>). Некоторые символы имеют специальные имена, например tab (табуляция), cr (возврат каретки) и lf (перевод строки). Для файлов с разделителями табуляции укажите #(tab). В качестве разделителя можно использовать любой символ, указав четырехзначный шестнадцатеричный Юникод, например #(2605) для символа звезды ★.

Официальная документация по функции Csv.Document утверждает, что в качестве разделителя можно предоставить список символов. Тем не менее, указание { ",", "|" } возвращает ошибку  Значение типа List не может быть преобразовано в тип Text. Более одного символа можно задать escape-последовательностью, например, #(tab)#(2605) для табуляции и звезды.

Параметр extraValues определяет, как функция Csv.Document обработает непредвиденные столбцы. Этот параметр принимает значения в соответствии с перечислением ExtraValues.Type:

Рис. 3.1а. Допустимые значения ExtraValues.Type

Английский термин enumeration официальная документация переводит, как «перечисление» или «перечисляемый тип». Перечисление обычно используется для определения ограниченного набора значений, которые могут принимать переменные этого типа. Каждое значение в перечислении обычно имеет имя, что делает код более читаемым. Вот пример определения перечисления на языке M:

Здесь DayOfWeek – это перечисление, определяющее дни недели, и каждый день недели имеет имя в этом перечислении.

Значение по умолчанию – ExtraValues.Ignore. Если в файл Avocado Prices.csv добавить пятнадцатый столбец (напомню, что в коде 3.2 указано Columns = 14), то этот дополнительный столбец будет проигнорирован (не будет импортирован). Это полезно, если пользователи создают комментарии в исходных файлах. Все они не помешают импортировать структурированные данные. И наоборот, если вы хотите получать оповещения о любых изменениях в формате данных, используйте ExtraValues.Error. Импорт вернет ошибку, и вы изучите, что изменилось в исходных данных.

Может быть полезно вовсе не использовать параметр Columns, чтобы обеспечить робастную работу запроса. В этом случае будут импортированы все столбцы, и вы сможете принять решение, что с ними делать непосредственно в редакторе Power Query.

Параметр encoding определяет тип кодировки файла. Наиболее часто используются следующие значения TextEncoding.Type:

Рис. 3.1б. Типы кодировки текста

Существует множество иных кодировок. Диалоговое окно Avocado Prices.csv предоставляет опцию Источник файла, в которой можно увидеть все доступные значения и описания кодировок.

Рис. 3.1в. Меню выбора кодировок в диалоговом окне Avocado Prices.csv

Вернемся к синтаксису функции Csv.Document, использующему параметры внутри записи (код 3.3). В этом случае третий, четвертый и пятый параметры должны иметь значение null или иным образом не включаться в вызов функции. В запись могут входить параметры, описанные ранее: Delimiter, Columns, Encoding, QuoteStyle, а также параметр CsvStyle.

Перечисление CsvStyle.Type принимает два значения: CsvStyle.QuoteAfterDelimiter (значение 0) – кавычки в поле учитываются только сразу после разделителя и CsvStyle.QuoteAlways (значение 1) – кавычки учитываются всегда независимо от того, где они находятся. Пример см. по ссылке.

Кавычки в CSV-файле обозначают начало и конец текстовой строки, поэтому при импорте в PQ они опускаются. Значение CsvStyle.QuoteAlways позволяет убрать кавычки, встречающиеся в любом месте, а CsvStyle.QuoteAfterDelimiter – только те кавычки, что идут сразу после разделителя.

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

Учитывая все сказанное, вместо кода 3.2, сгенерированного интерфейсом, можно использовать

Однако это альтернативное выражение работает только потому, что внутри столбцов нет запятых. Если бы они были, то запрос отработал бы неправильно. Рекомендуется использовать параметры в виде записи для текстовых/CSV-файлов.

Последняя фраза на английском звучит так: «However, this alternative Source expression only works because there are no commas within our column values. If there were, then those rows would be parsed incorrectly. Thus, it is recommended to always use an options record for text/CSV files as the options». Не понятно, что авторы имели ввиду, так как запятые внутри столбцов некорректно отработают в обоих синтаксисах. Возможно, имелось ввиду, что наличие двойных кавычек и дополнительный параметр CsvStyle.Type, который есть только в синтаксисе с записью, смогут корректно обработать запятую-разделитель внутри столбца!?

Перейдем к импорту данных из файла Excel.

Excel

В языке M есть две функции Excel.Workbook и Excel.CurrentWorkbook.

Начнем с Excel.Workbook. Чтобы изучить эту функцию, откройте файл Avocado Prices.csv в Excel и сохраните в формате *.xlsx. Закройте файл.

Поскольку файл с GitHub выполнен для локальных настроек US, я подготовил файл Avocado Prices new.xlsx для локальных настроек RU.

Откройте новый файл Excel. Запустите редактор Power Query. Для этого в Excel пройдите Данные –> Получить данные –> Запустить редактор Power Query. В редакторе Power Query пройдите Главная –> Создать источник –> Файл –> Книга Excel. В окне навигации найдите и выберите файл Avocado Prices new.xlsx, нажмите Импорт.

В диалоговом окне Навигатор выберите лист Avocado Prices new, нажмите Ok:

Рис. 3.3. Импорт листа из файла Excel

Эти действия сгенерировали код М, чтобы увидеть его, пройдите Главная –> Расширенный редактор:

Рис. 3.3а. Код М в расширенном редакторе

Напомню, что автоматическое определение типов (строка 4) можно отключить. Для этого в редакторе Power Query пройдите Файл –> Параметры и настройки –> Параметры запроса и в области ГЛОБАЛЬНЫЕ выберите Загрузка данных. Переключатель Определение типов установите Никогда не определять типы и заголовки столбцов для неструктурированных источников. Я так и сделал.

Функция Excel.Workbook (строка 2) имеет три параметра. Как и в случае с Csv.Document, первый является выходными данными функции File.Contents, которая обращается к файлу Excel и возвращает двоичные данные.

Второй параметр – useHeaders – может принимать значения null, false (по умолчанию) или true (этот параметр также может быть записью, если третий параметр null). Указание true приводит к тому, что функция Excel.Workbook использует первую строку таблицы, в качестве имен заголовков столбцов. Можете изменить в коде null на true или повысить уровень заголовков на следующем шаге запроса. Обратите внимание, что PQ автоматически не распознал первую строку в качестве заголовка из-за того, что с столбцах 5 и 6 в первой строке числа, а не текст (см. рис. 3.3).

Третий параметр – delayTypes – может принимать значения null, false (по умолчанию) или true. true приводит к тому, что функция Excel.Workbook не присваивает типы столбцам. Многие аналитики данных ратуют за назначения типов данных вручную. Для бизнес-пользователей автоматическое назначение типов данных может сэкономить время.

Если useHeaders имеет формат записи, то доступны три параметра: UseHeaders, DelayTypes и InferSheetDimensions. Последний также принимает значения null, false (по умолчанию) или true. Этот параметр поддерживается только современным форматом файлов Excel – Open XML (с версии 2007). Указание true приводит к тому, что функция Excel.Workbook игнорирует метаданные измерений, включенные в файл Excel, и определяет область листа путем чтения листа.

Обратимся к строке 3 кода на рис. 3.3а:

Это выражение ссылается на выражение Источник (строка 2), обращаясь к таблице, возвращенной на этом шаге. Таблица имеет заголовки столбцов Name, Data, Item, Kind и Hidden:

Рис. 3.3б. Результат шага Источник

Таблица, возвращена функцией Excel.Workbook. Если файл Excel содержит несколько листов, таблиц, именованных диапазонов и/или динамических массивов, то в этой таблице будет несколько строк для каждого элемента. Эта информация используется редактором Power Query для создания окна Навигатор (см. рис. 3.3).

Фигурные скобки говорят о том, что это список, а прямые скобки – что список состоит из записей. Код извлекает запись, для которой Item = Avocado Prices, а Kind = Sheet. Фактически таблица на рис. 3.3б фильтруется по столбцам Item и Kind.

Если список включает несколько записей, это соответствует отбору по нескольким фильтрам.

И наконец, для отфильтрованной строки берется поле Data в качестве выходных данных шага. Обратите внимание, что содержимое этого поля – таблица (Table).

Поскольку наш файл Excel содержит только один лист и не содержит таблиц или именованных диапазонов, попробуйте переключить выражение после ключевого слова in на Источник{[]}. Одна строка в таблице возвращается в виде записи:

Рис. 3.3в. Запрос возвращает запись

Если присутствует несколько строк, эта нотация вернет ошибку. Дополнительные сведения об этой нотации и выборе элементов в таблицах см. в href=»https://baguzin.ru/wp/?p=27728″>главе 6 Структурированные данные.

Рассмотрим функцию Excel.CurrentWorkbook. Она ссылается на текущий файл Excel, поэтому ей не нужны параметры. В отличие от функции Excel.Workbook, эта функция возвращает не листы, а только таблицы, именованные диапазоны и динамические массивы. Кроме того, для каждого элемента возвращаются два столбца: Content и Name. В остальном доступ к элементам осуществляется аналогично функции Excel.Workbook, например:

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

Папка

В М представлены две функции для работы с папками файловой системы: Folder.Contents и Folder.Files. Обе функции принимают путь в виде текста в качестве первого параметра и запись опций в качестве второго параметра. Как и в случае с File.Contents, запись опций поддерживает единственный параметр PreserveLastAccessTimes, который может принимать значения true / false.

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

Для изучения этих функций мы воспользуемся файлом Atlantic and Pacific Hurricanes 1851-2014.zip, включенным в репозиторий GitHub для этой книги. Загрузите zip-файл и извлеките из него четыре файла в одну папку на локальном компьютере.

В редакторе Power Query пройдите Главная –> Создать источник –> Папка. Выберите нужную паку и кликните Открыть. Отобразится предварительный просмотр четырех файлов:

Рис. 3.6. Превью файлов

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

В результате выполнения кода получаем таблицу:

Рис. 3.6а. Импорт файлов из папки

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

Создайте новую папку в папке, в которую вы поместили четыре файла, и обновите запрос. Никаких дополнительных строк не появилось. Отредактируйте строку Источник, заменив Folder.Files на Folder.Contents. В таблицу добавится строка, для которой Content = Table, а Extension пусто.

В строке atlantic.csv кликните Binary. В запрос добавится несколько шагов:

Любопытно, что плагин, отвечающий за оформления кода на моем сайте, не распознает идентификаторы с решеткой, и оформляет их как комментарии))

В результате появится таблица, импортированная из CSV-файла. В выражении #"atlantic.csv" доступ к данным осуществляется аналогично доступу к листам Excel с помощью функции Excel.Workbook. Сначала обращаемся к списку записей, далее строки таблицы фильтруются по полю Name, и возвращается содержимое столбца Content.

Выражение #»Импортированные значения CSV» использует выходные данные предыдущего шага в качестве первого параметра функции Csv.Document.

Типичным сценарием является объединение файлов в папке с одинаковым форматом. Чтобы изучить этот вариант, повторите шаги предыдущего примера, но дойдя до шага на рис. 3.6, кликните Объединить и преобразовать данные. Откроется диалоговое окно Объединить файлы. В списке Пример файла по умолчанию используется первый файл, но можно выбрать и иной. Обратите внимание на флажок Пропускать файлы с ошибками. А пока просто кликните Ok.

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

Рис. 3.7. Запросы, функции и параметры, созданные командой Объединить и преобразовать данные

Чтобы увидеть как связаны запросы, в редакторе PQ пройдите Просмотр –> Зависимости запроса:

Рис. 3.8. Зависимости запросов

Запрос Пример файла содержит следующий код:

Выражение Источник использует функцию Folder.Files для возврата таблицы файлов. Выражение Навигация1 обращается к содержимому первого файла в таблице, доступ к которому осуществляется через элемент списка {0}. Изменение 0 на 1 приведет к доступу ко второму файлу. Если бы мы выбрали файл в окне Объединить файлы с помощью раскрывающегося списка Пример файла, вторая строка была бы аналогична второй строке запроса 3.8, т.е., содержала бы список записей с указанием фильтра по столбцу Name:

Теперь перейдем к запросу Параметр1 (Пример файла). В нем просто присваивается ссылка на запрос Пример файла.

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

Рис. 3.8а. Предупреждение при попытке открыть функцию Преобразовать файл в Расширенном редакторе

Нажмите Ok. В Расширенном редакторе вы увидите код:

Функция приимает один аргумент, для которого задано значение по умолчанию – Параметр1. Параметр1 – это ссылка на запрос Power Query Параметр1, для которого в настоящее время задано значение Пример файла (см. запрос 3.11). Указание значения по умолчанию позволяет функции работать и возвращать таблицу значений для предварительного просмотра, а также позволяет функции работать с данными, передаваемыми в функцию через аргумент.

Код M во внутреннем выражении let запроса 3.12 совпадает с кодом запроса Преобразовать пример файла, в чем вы можете убедиться, открыв запрос Преобразовать пример файла в Расширенном редакторе:

Строка Источник ссылается на Параметр1, который в свою очередь ссылается на запрос Пример файла и использует функцию Csv.Document, поскольку первый файл в папке является CSV-файлом. Если бы мы выбрали в качестве примера один из PDF-файлов, функция доступа к данным была бы иной. Второй шаг запроса 3.13 повышает первую строку до заголовка с помощью функции Table.PromoteHeaders. Запрос Преобразовать пример файла (3.13) можно редактировать для выполнения дополнительных преобразований, например удаления или переименования столбцов. Эти преобразования данных отразятся в обновленной функции Преобразовать файл.

Откроем основной запрос Atlantic and Pacific Hurricanes 1851-2014 в Расширенном редакторе:

Выражение #»Вызвать настраиваемую функцию1″ использует функцию Table.AddColumn для добавления столбца с именем Преобразовать файл, содержимое которого является результатом вызова функции Преобразовать файл со столбцом Content для каждой строки. По сути, возвращается строка со столбцом Преобразовать файл, содержимым которого являются выходные данные функции Преобразовать файл со столбцом Content для строки, переданной в качестве параметра, что означает, что преобразования из запроса Преобразовать пример файла применяются к содержимому каждого файла, возвращенного из выражения Источник, которое использует функцию Folder.Files для перечисления файлов в папке.

Последующие два выражения запроса 3.14 переименовывают и удаляют столбцы таким образом, что остаются только столбцы Source.Name и Преобразовать файл. Эти выражения используют функции Table.RenameColumns и Table.SelectColumns. Следующее выражение разворачивает все строки в столбце Преобразовать файл с помощью функции Table.ExpandTableColumns.

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

Если вы загрузите этот запрос на лист Excel, строки для PDF-файлов будут содержать кракозябры.

Рис. 3.8б. Строки pdf-файла обработанные функцией Csv.Document

Это связано с тем, что файлы PDF обрабатываются неправильно, так как преобразования предназначены для файлов CSV. Чтобы исправить это, вернитесь в редактор Power Query перейдите к запросу Atlantic and Pacific Hurricanes 1851-2014, и в области Примененные шаги кликните шаг Источник. В области Предварительного просмотра будет таблица с файлами. Отфильтруйте по столбцу Extension, оставит строки CSV. В код 3.14 добавилась вторая строка:

Формат pdf

Разработанные в 1992 году и стандартизированные как ISO 32000, файлы Portable Document Format (PDF) используются для обмена документами, включающими форматированный текст, таблицы, изображения, формы, мультимедийные материалы и т.д. Учитывая их долгую, более чем 30-летнюю историю в качестве свободного и открытого стандарта обмена документами, независимого от аппаратного обеспечения, операционной системы и прикладного программного обеспечения, в документах PDF хранится множество потенциально полезных данных. Например, PDF-документы часто используются для месячных или квартальных финансовых отчетов. Импорт PDF-файлов в единую семантическую модель дает возможность отслеживать изменения во времени, а также анализировать тренды. К счастью, язык M включает в себя функцию для извлечения данных из PDF-файлов – PDF.Tables.

Мы используем PDF-файлы, включенные в архив Atlantic and Pacific Hurricanes 1851-2014.zip. Откройте новый файл Excel. Для запуска редактора Power Query пройдите Данные –> Получить данные –> Запустить редактор Power Query. В редакторе Power Query пройдите Главная –> Создать источник –> Файл –> PDF. Найдите и выберите файл atlantic.pdf, нажмите Импорт. Обратите внимание, что в диалоговом окне Навигатор отображается несколько элементов – таблицы и страницы. Функция Pdf.Tables пытается автоматически идентифицировать таблицы в документе PDF и включает их в качестве потенциальных объектов для извлечения наряду с полными страницами PDF-файла. Выберите Table001 (Страница 1), нажмите Ok.

После выполнения этих шагов генерируется код M, который можно увидеть в Расширенном редакторе:

Функция Pdf.Tables имеет два параметра. Первый – вывод функции File.Contents, которая обращается к PDF-файлу и возвращает двоичные данные. Второй – необязательная запись опций. Доступны следующие параметры:

Implementation. Корпорация Майкрософт реализовала ряд алгоритмов для идентификации таблиц в PDF-файлах. Этот параметр определяет, какой алгоритм будет использоваться. Допустимы значения 1.3, 1.2 и 1.1 и null. Используйте алгоритм с наибольшим номером. Другие значения оставлены для обратной совместимости.

StartPage по умолчанию имеет значение 1 и указывает первую страницу, включаемую в извлекаемые данные.

EndPage по умолчанию использует последнюю доступную страницу для включения в данные.

MultiPageTables указывает, следует ли рассматривать таблицы, занимающие несколько страниц, как одну таблицу или несколько таблиц. Это значение может быть true (по умолчанию) и false. Обратите внимание, что идентификация таблиц, занимающих несколько страниц, не всегда успешна.

EnforceBorderLines указывает, должен ли Power Query учитывать явные линии границ ячеек при извлечении таблиц из PDF-документа. Включение этого параметра поможет в ситуациях, когда таблицы имеют четко очерченные границы, что делает извлечение данных более точным. Допустимые значения true и false (по умолчанию).

Теперь перейдем к XML-файлам.

XML

eXtensible Markup Language (расширяемый язык разметки) является широко используемым форматом файлов для хранения произвольных данных. Гибкость и стандартизация делают его подходящим для обмена данными между организациями. Например, Health Level 7 (HL7) – это глобальный стандарт на основе XML для обмена данными между приложениями. Другим примером является OpenTravel Alliance, консорциум туристических и гостиничных компаний, который использует стандартные XML-файлы для обмена данными между системами.

Семейство функций XML включает Xml.Tables и Xml.Document. Обязательный первый аргумент определяет содержимое файла. Последним аргументом является необязательный номер кодировки – TextEncoding.Type. Функция Xml.Tables также имеет необязательный второй параметр – запись с опциями.

Несмотря на то, что обе функции анализируют XML-файлы, их внутренняя реализация отличается. Xml.Tables анализирует XML и возвращает таблицу с содержимым, а Xml.Document возвращает иерархическую структуру в таблице с заголовками столбцов Name, Namespace, Value и Attribute.

Воспользуемся файлом books.xml, который представлен в репозитарии GitHub. Для удобства приведем фрагмент файла:

Файл содержит корневой узел catalog. В каталоге несколько узлов книг. Каждый узел книги содержит свойства, таких как автор, название, жанр, цена и др.

Xml.Tables

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

Рис. 3.9. Окно Навигатор при импорте файла Book.xml

Сгенерится код:

Шаг Источник извлекает содержимое файла с помощью File.Contents, а затем обрабатывает содержимое файла с помощью Xml.Tables. Элементы, находящиеся под корневым узлом, возвращаются в виде списка. Таким образом, шаг Table0 обращается к первому элементу в списке {0}, а затем часть [Table] использует выбор поля и возвращает содержимое поля записи с именем Table. В результате выполнения кода XML-документ превращается в таблицу с заголовками столбцов – свойствами. Данные по каждой книге представлены в отдельной строке:

Рис. 3.9а. Результат кода 3.17

Второй параметр функции Xml.Tables недостаточно документирован. Исследования показывают, что поддерживается одно поле записи, NavigationTables, которое по умолчанию имеет значение true. Необязательный третий параметр указывает тип кодировки (перечисление TextEncoding.Type), например TextEncoding.Windows или число 1252.

Xml.Tables позволяет всего за несколько строк кода M сгенерировать таблицу с названиями и атрибутами книг. Чего не скажешь об использовании Xml.Document.

Xml.Document

Чтобы получить таблицу, аналогичную предыдущей, потребуется больше ухищрений:

Шаг Source возвращает однострочную таблицу:

Рис. 3.9б. Функция Xml.Document возвращает таблицу

Эта строка – корневой элемент XML-документа – catalog. Чтобы вернуть элементы books, мы сначала удаляем столбцы Name, Namespace и Attributes (выражение #"Removed Columns"). Затем раскрываем столбец Value, оставляя только два столбца – Value и Attributes (выражение #»Expanded Value»). На этом шаге возвращается строка для каждого элемента book с заголовками столбцов Value.1 и Attributes. Оба столбца содержат таблицы для каждой строки.

Рис. 3.9в. Промежуточная таблица после шага #»Expanded Value»

Далее мы раскрываем столбец Attributes, оставляя только столбец Value (выражение #"Expanded Attributes"). Затем мы раскрываем столбец Value.1, оставляя столбцы Name и Value (выражение #"Expanded Value.1"). Порядок этих двух шагов не имеет значения. Единственное отличие заключается в небольшом изменении автоматически генерируемых имен столбцов.

Далее мы сводим таблицу, чтобы значения из первого столбца Name.1 стали заголовками столбцов новой таблицы (выражение PivotedColumn). Наша таблица теперь имеет тот же базовый формат, что и таблица, возвращенная функцией Xml.Tables. Осталось переименовать некоторые столбцы и изменить типы данных столбцов.

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

Служба хранилища Azure

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

Библиотека M содержит несколько функций для доступа к этим объектам хранения: AzureStorage.Blobs, AzureStorage.Tables, AzureStorage.BlobContents, AzureStorage.DataLake, AzureStorage.DataLakeContents. Например:

Вместо mystorageaccountname следует указать имя учетной записи хранения Azure. При аутентификации вы можете выбрать опцию Access keys.

Функция возвращает таблицу из двух столбцов, состоящую из всех контейнеров blob-объектов, присутствующих в учетной записи хранения. Эта таблица состоит из столбца Name для имени контейнера blob-объектов и столбца Data, возвращающего объект Table. Вы можете перейти к этому объекту Table, чтобы просмотреть папки и файлы в контейнере blob-объектов. Интерфейс очень похож на использование функций Folder.Contents или Folder.Files. На самом деле, на уровне blob-объекта (файла) возвращаемые столбцы идентичны.

Второй необязательный параметр функции AzureStorage.Blobs – запись опций, которая может содержать параметры BlockSize, RequestSize и ConcurrentRequests. Значения по умолчанию для них 4МБ, 4МБ и 16 соответственно. Эти параметры можно настроить таким образом, чтобы потенциально ускорить загрузку за счет большего использования памяти. Например, параметр ConcurrentRequests можно изменить на 32, что означает, что будет 32 одновременных запроса данных, каждый из которых запрашивает 4МБ (при использовании RequestSize по умолчанию). Таким образом, используемая память составит 32*4МБ или 128МБ.

Функция AzureStorage.Tables работает так же, как и функция AzureStorage.Blobs, за исключением того, что она возвращает таблицы вместо контейнеров blob-объектов. Второй аргумент поддерживает только параметр Timeout. Значение по умолчанию предоставляется источником.

Функция AzureStorage.BlobContents принимает URI в качестве первого параметра и необязательную запись параметров с теми же параметрами, что и функция AzureStorage.Blobs. Универсальный код ресурса (URI) должен быть полным универсальным кодом ресурса (URI) большого двоичного объекта в учетной записи хранения Azure.

Функция Azure.DataLake принимает конечную точку озера данных для учетной записи хранения Azure в качестве первого параметра. Это URI типа https://mystorageaccountname.dfs.core.windows.net/.

Эта функция работает так же, как семейство функций Folder, предоставляя возможность Объединить и преобразовать данные в редакторе Power Query. Эта функция также принимает необязательный второй параметр – запись опций BlockSize, RequestSize и ConcurrentRequests (значения по умолчанию 4МБ, 4МБ и 16) + HierarchicalNavigation, принимающий значения null, true или false (по умолчанию).

Функция Azure.DataLakeContents возвращает содержимое отдельного файла, хранящегося в конечной точке озера данных учетной записи хранения Azure. Первый параметр – это полный URI файла. Запись параметров может быть предоставлена в качестве второго параметра и имеет те же параметры и значения по умолчанию, что и функция AzureStorage.Blobs.

Дополнительные форматы файлов

Стандартная библиотека M содержит дополнительные функции для доступа к файлам:

Hdfs.Contents извлекает таблицу папок и файлов из файловой системы Hadoop. Принимает один параметр, url, в виде текста. Это универсальный код ресурса (URI) для папки Hadoop. Аналогично Folder.Contents.

Hdfs.Files извлекает таблицу файлов из файловой системы Hadoop. Принимает один параметр, url, в виде текста. Это универсальный код ресурса (URI) для папки Hadoop. Аналогично Folder.Files.

HdInsights.Containers извлекает таблицу контейнеров из хранилища Azure HDInsights. Azure HDIninsights – это управляемая служба Azure, которая предоставляет возможность использовать платформы с открытым кодом, такие как Hadoop, LLAP, Apache Hive, Apache Spark и Apache Kafka. Принимает один параметр, account, в виде текста. Это универсальный код ресурса (URI) учетной записи для хранилища Azure HDInsights. Каждая строка возвращает ссылку на blob-объекты контейнера.

HdInsights.Contents также извлекает таблицу контейнеров из хранилища Azure HDInsights. Принимает один параметр, account, в виде текста. Это универсальный код ресурса (URI) учетной записи для хранилища Azure HDInsights. Каждая строка возвращает ссылку на blob-объекты контейнера.

HdInsights.Files извлекает таблицу blob-файлов из хранилища Azure HDInsights. Принимает один параметр, account, в виде текста. Это универсальный код ресурса (URI) учетной записи для хранилища Azure HDInsights. Каждая возвращаемая строка содержит свойства файла и ссылку на содержимое файла.

Json.Document возвращает содержимое документа JSON. Имеет два параметра: jsonText (часто выходные данные File.Contents или Web.Contents) и encoding со значениями TextEncoding.Type.

RData.FromBinary используется для возврата записи кадров данных из файла RData. Принимает stream, в качестве двоичного содержимого файла.

Parquet.Document не является частью основной библиотеки M, а реализован как расширение (коннектор). Общие сведения о расширениях и коннекторах см. в главе 16 Включение расширений. Извлекает таблицу из содержимого документа Parquet. Имеет два параметра: двоичное содержимое файла (часто вывод File.Contents или Web.Contents) и запись необязательных параметров MaxDepth, Compression, PreserveOrder, LegacyColumnNameEncoding и TypeMapping.

На этом мы завершаем исследование форматов файлов и папок. Теперь обратим внимание на базы данных и кубы.

Получение веб-содержимого

В предыдущем разделе мы рассмотрели доступ к файлам, хранящимся на локальном компьютере или в сетевой файловой системе. Еще одним распространенным источником данных является Интернет. Библиотека M располагает рядом функций для получения данных из Интернета: Web.BrowserContents, Html.Table, Web.Page, Web.Contents, Web.Headers, WebAction.Request.

На момент написания заметки у меня установлен Excel 365 версия 2404 сборка 17531.20140. Функция Web.BrowserContents не поддерживается. В Power BI Desktop все Ok.

Чтобы увидеть эти функции в действии, в Power BI Desktop пройдите Главная –> Преобразование данных. В редакторе Power Query пройдите Главная –> Создать источник –> Из Интернета. В диалоговом окне Из Интернета укажите URL-адрес https://subscription.packtpub.com/search. Нажмите Ok. В окне Доступ к веб-содержимому выберите Анонимно, нажмите Подключение. В диалоговом окне Навигатор в папке Текст выберите HTML-код. Нажмите Ok. Выполнив эти действия, вы создали код M, который можно увидеть в Расширенном редакторе:

Выходные данные запроса возвращают необработанный HTML-текст для веб-страницы. В качестве необязательного второго параметра функция Web.BrowserContents может содержать запись опций. Доступны параметры ApiKeyName и WaitFor. Подробнее см. здесь.

Повторите шаги запроса, но на этот раз выберите Отображаемый текст вместо HTML-кода. На этот раз генерируется следующий код M:

В этом запросе по-прежнему используется Web.BrowserContents для получения необработанного HTML-кода. Функция Html.Table принимает этот HTML-код в качестве первого параметра. Второй параметр – columnNameSelectorPairs – включает список списков пар (в нашем примере одна пара): имя столбца выходной таблицы / селектор каскадных таблиц стилей (CSS). В нашем примере выбран селектор BODY. Отобранное содержимое отразится в столбце Column1 выходной таблицы. Обсуждение CSS выходит за рамки этой книги, но обратите внимание, что все веб-страницы имеют следующую базовую структуру:

Этот HTML-код, если рассматривать его на корневом уровне (html), представляет собой таблицу с одним столбцом, состоящей из заголовка (head) и тела (body). Таким образом, селектор BODY выбирает часть тела веб-страницы. Html.Table возвращает не исходный HTML, а то, что фактически видно на странице как текст.

Третий параметр – запись опций – поддерживает одно поле, RowSelector, используемое для возврата только определенных строк из HTML-таблицы.

Функция Html.Table используется всякий раз, когда вы выбираете таблицу или выбираете опцию Добавление таблиц с использованием примеров окна Навигатор при использовании веб-коннектора. В этих сценариях редактор Power Query использует селекторы CSS в HTML-документе для извлечения данных таблицы.

Рис. 3.9г. Опция Добавление таблиц с использованием примеров окна Навигатор

Теперь рассмотрим функцию Web.Page.

Web.Page

Создайте следующий запрос:

Функция Web.Page принимает один параметр – html. В нашем случае для этого параметра мы использовали выходные данные функции Web.BrowserContents. Выходные данные функции Web.Page похожи на Xml.Document, рассмотренную ранее, как и взаимодействие пользователя при навигации по документу.

Выходные данные запроса представляют собой таблицу из двух строк и четырех столбцов. Возвращаются столбцы Kind, Name, Children и Text, а строки в столбце Name имеют значения HEAD и BODY. Вы можете продолжить навигацию по иерархической древовидной структуре, возвращаемой этим запросом, щелкнув любую ячейку, содержащую таблицу.

Рис. 3.9д. Таблица, созданная кодом 3.22

Web.Contents

Функция Web.Contents возвращает содержимое веб-запроса в виде двоичных данных, аналогично функции File.Contents, возвращающей содержимое файла в виде двоичных данных. Это означает, что везде, где мы использовали File.Contents в разделе Открытие файлов и папок этой главы, мы могли бы использовать и Web.Contents. Функция Web.Contents имеет два параметра: первый – текстовый URI (URL); второй – запись опций:

Query добавляет параметры в запроса, которые будут присоединены к URL-адресу.

ApiKeyName укажет имя (не значение) ключа API, используемого в URL-адресе. Значение ключа указывается в учетных данных.

Headers позволяет указать заголовки HTTP-запроса.

Timeout указывает максимальное время ожидания ответа от сервера (по умолчанию 100 секунд).

ExcludedFromCacheKey дает список ключей заголовков HTTP, которые должны быть исключены из вычислений для кэширования.

IsRetry = null / true / false (по умолчанию). Указание true игнорирует любой присутствующий в кэше ответ.

Chat GPT описывает последний параметр с акцентом на API. IsRetry используется для указания того, является ли текущий запрос повторным. Этот параметр может быть полезен в сценариях, где вам необходимо повторить запрос в случае неудачи или ошибки. Сервисы или API могут обрабатывать повторные запросы по-разному, и иногда требуется явно указать, что запрос повторяется.

Если значение установлено в true, это означает, что запрос является повторной попыткой. Если значение установлено в false или не указано, это считается первым запросом.

Контекст использования IsRetry. Использование параметра IsRetry может быть важно в случае ошибки или сбоя в запросе. Если первоначальный запрос завершился с ошибкой (например, из-за временной недоступности сервера), вы можете повторить запрос, установив IsRetry = true, чтобы API знал, что это повторный запрос. Некоторые API могут иметь специальные обработки для повторных запросов (например, возврат кэширующихся данных или обход ограничений по частоте запросов).

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

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

Функция MakeRequest принимает параметр retry, который указывает, является ли запрос повторным. Первоначальный запрос выполняется с IsRetry = false. Если первоначальный запрос завершается с ошибкой (initialResponse[HasError]), выполняется повторный запрос с IsRetry = true. Результат успешного запроса или повторного запроса сохраняется в finalResponse.

ManualStatusHandling – список кодов состояния HTTP. Ответы с этими кодами не будут автоматически обрабатываться функцией Web.Contents. Эти коды статуса HTTP должны обрабатываться вручную

RelativePath добавляет указанное текстовое значение к базовому URL-адресу перед выполнением запроса.

Content позволяет отправить данные в теле запроса, обычно используется для HTTP-методов, таких как POST. Указанное двоичное значение отправляется в качестве содержимого запроса.

Чтобы изучить эти параметры в действии, создайте запрос:

Этот запрос указывает 30-секундный тайм-аут и возвращает двоичное содержимое ресурса: https://subscription.packtpub.com/search?query=power+query&products=Book

Значение RelativePath добавляется к указанному базовому URL-адресу. Строка запроса (часть после ?) строится автоматически из значения записи параметра Query.

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

Бинарные функции

Как мы уже обсуждали, File.Contents и Web.Contents служат основными функциями доступа к данным. Один принимает путь к файлу, а другой – URI, и возвращают файл или веб-страницу в виде двоичных данных. В базовой библиотеке M существует большая группа функций, предназначенных для обработки и управления двоичными данными. Все они начинаются со слова Binary. Их более 40. Хотя многие из этих функций являются довольно специфическими, некоторые из них часто можно увидеть в запросах.

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

Рис. 3.9е. Таблица

Здесь длинная строка текста и чисел – это сжатый текст в двоичной кодировке.

Двоичное кодирование и сжатие являются сложными предметами. Тем не менее, основную концепцию достаточно легко объяснить. Представьте себе, что в тексте слово the встречается десятки или даже сотни раз. Вместо того, чтобы хранить слово the для каждого экземпляра, мы можем сопоставить the с числом 1. Таким образом, нам нужно хранить только одну цифру, 1, для каждого экземпляра слова the, а также таблицу, которая сопоставляла бы число 1 со словом the.

Функция Binary.FromText имеет два параметра: текст, который нужно преобразовать в двоичные данные, и кодировку этого текста – перечисление BinaryEncoding.Type.

После преобразования двоичные данные распаковываются с помощью функции Binary.Decompress. Ее первый параметр – это двоичные данные для распаковки. Второй – тип сжатия – перечисление Compression.Type. Допустимые значения:

• None
• GZip
• Deflate
• Snappy
• Brotli
• LZ4
• Zstandard

Использование того или иного формата сжатия сводится к тому, что важнее: степень или скорость сжатия. Например, LZ4 и Snappy намного быстрее, чем Gzip, но создают файлы большего размера. С другой стороны, сжатие Brotli было разработано для сжатия видеопотоков на лету и на самом деле работает лучше, чем Gzip, и создает файлы меньшего размера.

После распаковки текста функции Json.Document и Table.FromRows используются для преобразования данных в таблицу столбцов и строк.

Кому-то может быть любопытно, откуда взялась длинная строка цифр и букв. Чтобы выяснить это, нам сначала нужно точно знать, какой текст был сжат и закодирован. Мы можем сделать это с помощью функции Text.FromBinary следующим образом:

В этом коде вместо обработки распакованных двоичных данных с помощью функций Json.Document и Table.FromRows мы используем функцию Text.FromBinary для возврата следующего текста: [["Один","1"],["Два","2"],["Три","3"]].

Функция Text.FromBinary имеет два параметра. Первый – это двоичные данные, второй – перечисление TextEncoding.Type.

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

Поскольку в строке присутствуют двойной кавычки, мы должны их экранировать. Этот код реконструирует то, как была создана исходная сжатая и закодированная строка. Во-первых, функция Text.ToBinary превращает текстовую строку в двоичную. Text.ToBinary принимает три параметра: текст, который преобразуется в двоичные данные; перечисление TextEncoding.Type, параметр includeByteOrderMark, который может иметь значение true или false.

После преобразования текста в двоичный файл для сжатия данных используется функция Binary.Compress с двумя параметрами: двоичные данные для сжатия; перечисление Compression.Type.

Теперь мы можем использовать функцию Binary.ToText для возврата сжатого текста в двоичной кодировке в виде текста. На выходе запроса 3.27 будет текстовая строка:

i45W8s9LVdJRMlSK1YlWCinPB7KNIOyMolSQjLFSbCwA

Теперь давайте рассмотрим еще один класс функций извлекающих данные.

Семейство функций Lines

В дополнение к семейству функций Binary существуют функции для работы с двоичными данными и содержимым файлов: Lines.FromBinary, Lines.FromText, Lines.ToBinary, Lines.ToText. Семейство функций Lines преобразует списки текста в одиночные текстовые значения или в двоичные данные и обратно. По умолчанию эти функции используют символы возврата каретки и перевода строки в качестве разделителей.

Чтобы увидеть, как можно использовать эти функции, рассмотрим файл MultipleJSONDocs.json, включенный в репозиторий GitHub, содержащий несколько документов JSON, по одному в строке:

Хотя каждая строка является документом JSON, попытка проанализировать этот файл с помощью функции Json.Document завершится ошибкой, так как файл в целом не является допустимым JSON:

Рис. 3.9ж. Импорт файл MultipleJSONDocs.json с помощью коннектора JSON возвращает ошибку

К счастью, мы можем использовать функцию Lines.FromBinary, которая поможет нам преобразовать данные в таблицу, используя код:

Функция Lines.FromBinary используется для чтения каждой строки файла и возврата этих строк в виде списка:

Рисунок 3.11. Выходные данные функции Lines.FromBinary

Этот список преобразуется в таблицу с одним столбцом с помощью функции Table.FromColumns, а затем преобразуется с помощью функции Json.Document для обработки каждой строки как отдельного документа JSON. Делается это с помощью функции Table.TransformColumns. В этом случае функция Json.Document возвращает запись для каждой строки, которую мы можем разложить на столбцы с помощью функции Table.ExpandRecordColumn.

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

Доступ к базам данных и кубам

Термин куб мы используем для обозначения систем баз данных, классифицируемых как онлайн системы аналитической обработки (OLAP). Термин база данных относится к реляционным базам данных, классифицируемым как системы оперативной обработки транзакций (OLTP). Системы OLAP подходят для многомерного анализа данных, в то время как системы OLTP подходят для транзакционных операций.

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

Рис. 3.11а. Функции языка М для доступа к базам данных и кубам

Почти все функции, оканчивающиеся на .Database предназначены для работы с реляционными базами данных (OLTP), а оканчивающиеся на .Cubes – для доступа к системам аналитики (OLAP). Исключение составляет семейство функций AnalysisServices.

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

Query – запрос SQL, MDX, DAX или иной запрос, поддерживаемый исходной системой.

CommandTimeout – продолжительность выполнения запроса до его прерывания (тип duration, по умолчанию десять минут).

ConnectionTimeout – продолжительность, в течение которой следует пытаться установить соединение до остановки (тип duration, значение по умолчанию зависит от драйвера).

CreateNavigationProperties указывает, следует ли создавать свойства навигации: true (по умолчанию) или false.

NavigationPropertyNameGenerator указывает функцию, используемую для создания имен для свойств навигации.

HierarchicalNavigation указывает, группируются ли объекты по имени схемы: true или false (по умолчанию).

В качестве эксперимента создайте два запроса:

и

Замените ServerName и DatabaseName в коде фактическим именем SQL Server и базы данных SQL. Первый запрос возвращает таблицу из пяти столбцов, в которой перечислены таблицы, представления и функции в базе данных. Возвращаются столбцы Name, Data, Schema, Item и Kind. Второй запрос возвращает таблицу из двух столбцов, в которой перечислены схемы в базе данных. Возвращаются столбцы Schema и Data.

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

Здесь мы получаем данные из таблицы dbo.DimCustomer базы данных AdventureWorksDW2019, доступной на локальном экземпляре SQL Server (localhost). Поле Query используется для выполнения инструкции SQL SELECT. Пример базы данных AdventureWorks можно загрузить здесь.

Функции кубов

Функции, обращающиеся к кубам, образуют семейство, начинающееся с Cube. Таких функций 16. При работе с кубами в редакторе Power Query на вкладке Управление появится специальный инструмент Средства для кубов. На ленте отображаются кнопки Добавить элементы и Свернуть столбцы. Первая вызывает функцию Cube.AddAndExpandDimensionColumn, вторая – Cube.CollapseAndRemoveColumns.

Рис. 3.11б. Вкладка Средства для кубов в редакторе Power Query

При использовании редактора Power Query для доступа к набору данных Power BI, опубликованному в облаке с помощью служб Analysis Services, код M выглядит примерно так:

В строке Source для подключения к рабочей области используется функция AnalysisServices.Database. Формат строки подключения для доступа к конечной точке XMLA для рабочей области принимает следующий формат:

power-bi://api.powerbi.com/v1.0/<tenant>/<workspace>

Здесь <tenant> – имя клиента Microsoft 365, а <workspace> – имя рабочей области Power BI.

Для функции AnalysisServices.Database использованы две опции CommandTimeout и ConnectionTimeout, рассмотренные ранее. Помимо них могут также использоваться:

TypeMeasureColumn указывает, использовать ли типы в кубе для определения типов добавленных столбцов мер. Допустимые значения true и false (по умолчанию, в этом случае все добавляемые столбцы мер имеют тип number).

Culture указывает язык и региональные параметры, используемые для данных. Аналогично свойству Local Identifier, доступному при подключении служб Analysis Services.

SubQueries управляет поведением вычисляемых элементов во вложенных кубах или вложенных выборках. Может принимать значения 0, 1 или 2 (по умолчанию). Аналогично свойству SubQueries, доступному при подключении служб Analysis Services.

Implementation указывает версию реализации.

Следующие три выражения кода 3.32 после выражения Source переходят к нужному кубу, используя стандартный синтаксис доступа к данным. Функции Cube.Transform и Cube.AddAndExpandDimensionColumn возвращают требуемую информацию из куба.

Подобно тому, как File.Contents и Web.Contents являются базовыми функциями доступа к данным для файлов и веб-сайтов, функция Cube.Transform дает доступ к данным из кубов. Она принимает два параметра: куб для преобразования и список преобразований, применяемых к кубу. В коде 3.32 используется одно преобразование задаваемое функцией Cube.AddAndExpandDimensionColumn.

Дополнительные сведения о семействе функций Cube. можно найти на сайте powerquery.how в разделе Operations.

На этом мы завершаем исследование доступа к базам данных и кубов. Далее мы рассмотрим стандартные протоколы передачи данных.

Работа со стандартными протоколами передачи данных

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

Рис. 3.11в. Функции М для стандартных протоколов передачи данных

Каждый компьютер с Windows поставляется с несколькими драйверами ODBC. Драйверы – это программные компоненты, которые выступают в качестве посредников между различными форматами баз данных и файлов. Windows поставляется с драйверами ODBC для файлов dBASE, Excel, Microsoft Access, CSV, SQL Server и др. Дополнительные драйверы ODBC также доступны от большого числа сторонних производителей.

Существуют 32-разрядные и 64-разрядные драйверы ODBC. Необходимо использовать соответствующий драйвер, совместимый с прикладным программным обеспечением (64-разрядная версия для Power BI Desktop).

Чтобы использовать ODBC, необходимо сначала установить соответствующий драйвер в системе. Затем вы создаете Имя источника данных ODBC (Data Source Name, DSN), которое определяет сведения о подключении и параметры для конкретного источника данных. Это можно сделать, введя ODBC Data Sources в строке поиска Windows. Затем это Имя источника данных используется в строке подключения для ODBC:

Рис. 3.12. ODBC DSN

Например, после настройки 64-разрядной DSN с именем MyODBCDataSource с помощью текстового драйвера Microsoft Access, указывающего на папку для набора данных Atlantic and Pacific Hurricanes 1851-2014, мы можем использовать следующий запрос для получения содержимого atlantic.csv файла с помощью ODBC:

Первый параметр функции Odbc.DataSource – строка подключения, второй – запись параметров. Опции включают ранее описанные CreateNavigationProperties, HierarchicalNavigation, ConnectionTimeout и CommandTimeout, а также SqlCompatibleWindowsAuth, которая указывает, совместима ли строка подключения с проверкой подлинности Windows. По умолчанию – true.

OData – еще один распространенный и популярный стандарт протокола передачи данных. Мы можем получить данные из конечной точки OData с помощью следующего запроса:

Функция OData.Feed используется для получения данных из универсального кода ресурса (URI), настроенного для OData. В нашем случае мы пользуемся одним из справочных сервисов, предоставляемых компанией odata.org. Функция Odata.Feed имеет три параметра: URI подключения, запись заголовков (обычно null) и запись опций. Эта запись поддерживает многие параметры функция Web.Contents, а также ряд специфичных для OData. Подробнее см. здесь.

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

Дополнительные коннекторы

В глобальной среде M в Power BI Desktop существует гораздо больше функций доступа к данным. Эти функции обеспечивают возможность подключения к широкому спектру специализированных бизнес-систем, таких как системы управления взаимоотношениями с клиентами (CRM), системы управления ресурсами предприятия (ERP) и другие. Эти дополнительные функции доступа к данным поступают из внешних коннекторов, входящих в состав Power BI Desktop.

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

Коннекторы для популярных программ

Стандартная библиотека М включает несколько таких функций:

Рис. 3.12а. Функции для подключения к программным системам

Например, следующий запрос вернет все контакты для почтового ящика из Exchange Online:

Семейство функций SharePoint заслуживает пояснений. Рассмотрим следующий запрос:

Функция SharePoint.Files возвращает таблицу в том же формате, что и функции Folder.Contents. Каждый файл на указанном сайте, независимо от библиотеки документов, включается в таблицу. И наоборот, в следующем запросе…

… функция SharePoint.Contents вернет таблицу, в которой отдельные библиотеки возвращаются в виде строк. Вы можете перейти к папкам и файлам, содержащимся в определенной библиотеке, щелкнув Table в столбце Content. И папки, и файлы возвращаются в виде строк.

Наконец, в запросе…

… функция SharePoint.Tables вернет таблицу, в которой каждая строка представляет собой список или библиотеку документов на указанном сайте SharePoint. Таблица состоит из трех столбцов: Id, Title и Items. Столбец Id является уникальным идентификатором, столбец Title содержит имя списка или библиотеки документов, а столбец Items содержит объект Table, который можно раскрыть для извлечения отдельных элементов в списке или библиотеке документов.

Функции идентификации

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

GoogleAnalytics.Accounts возвращает аккаунты Google Analytics, доступные с текущими учетными данными.

Identity.From создает удостоверение с учетом поставщика удостоверений в виде функции и значения.

Identity.IsMemberOf возвращает значение true или false в зависимости от того, является ли указанное удостоверение членом коллекции удостоверений, указанной в качестве записи.

IdentityProvider.Default для текущего узла возвращает поставщика удостоверений по умолчанию.

Даже продвинутые пользователи M редко сталкиваются с вариантом использования этих функций. Дополнительные сведения о них можно найти на сайте powerquery.how в разделах Operations и External Integrations.

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

Добавление и объединение данных

Помимо доступа к данным, одной из сильных сторон языка M является возможность преобразования и объединения данных. М позволяет объединять данные из нескольких источников, например, данные о запасах с данными о продажах или данные о клиентах из нескольких CRM-систем. Мы кратко рассмотрим пять основных функций: Table.Combine, Table.NestedJoin, Table.Join, Table.FuzzyNestedJoin, Table.FuzzyJoin.

При чтении этого раздела будет полезно обратиться к статье Рика де Гроота Типы соединений. Для удобства итоговая шпаргалка приведена здесь:

Рис. 3.13. Выходные данные функции Table.NestedJoin

Начнем с функции Table.Combine.

Table.Combine

Функция Table.Combine добавляет (appends) две или несколько таблиц. Вернемся к файлам, извлеченным из архива Atlantic and Pacific Hurricanes 1851-2014.zip. В редакторе Power Query пройдите Главная –> Создать источник –> Файл –> PDF. Найдите и выберите файл atlantic.pdf,  нажмите Импорт. В окне Навигатор кликните Несколько элементов, в затем выберите две таблицы:

Рис. 3.13а. Выбор таблиц для импорта в файле atlantic.pdf

Нажмите Ok. Переименуйте запросы в atlanticPDFTable1 и atlanticPDFTable2:

Рис. 3.13б. Переименование запросов в редакторе Power Query

Поскольку функция Pdf.Tables не поняла, что таблица занимает две страницы, мы можем использовать функцию Table.Combine, чтобы исправить недоразумение. Создайте новый запрос:

Функция Table.Combine принимает список таблиц в качестве первого аргумента. Второй, необязательный аргумент позволяет указать возвращаемые столбцы. Запрос 3.40 вернет один столбец, Column1, вместо семнадцати, как в коде 3.39.

Переименуйте запрос 3.40 в atlanticPDFTableCombined.

Теперь давайте изучим слияние таблиц.

Table.NestedJoin и Table.Join

Функции Table.NestedJoin и Table.Join объединяют (merge) или соединяют (join) таблицы. Эти функции похожи, но имеются отличия.

Здесь термины объединение (merge) и соединение (join) используются как синонимы, а не как два понятия. А вот второе понятие в М – это добавление (appends). Соединение таблиц в PQ – отличная альтернатива ВПР в Excel. Мы можем дополнить данные в основной таблице информацией из таблицы подстановки, используя уникальное поле.

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

Продолжая пример 3.40, переименуйте Column1 в выходных данных запроса atlanticPDFTableCombined в Column0, а затем создайте два запроса:

Различие между Table.NestedJoin и Table.Join видно по тому, что возвращают эти два запроса. Запрос 3.41 возвращает таблицу из двух столбцов с заголовками Column0 и atlanticPDFTable1:

Рис. 3.14. Работа функции Table.NestedJoin

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

Запрос 3.42 вернет столбец Column0 из запроса atlanticPDFTableCombined и все столбцы из запроса atlanticPDFTable1:

Рис. 3.15. Работа функции Table.Join

Обе функции выполняют так называемое соединение (join), при котором выбирается ключевой столбец в обеих таблиц, и эти ключевые столбцы сравниваются для поиска совпадающих строк. Таблицы и ключевые столбцы являются первыми четырьмя параметрами функций Table.NestedJoin и Table.Join. Первый параметр – левая таблица, а третий – правая. Ключевые столбцы (второй и четвертый параметры) указываются в виде списка. Это означает, что слияние может произойти и по нескольким ключевым столбцам. Важно: столбцы соединения должны иметь один и тот же тип данных, например, текст, число или дата.

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

Шестой параметр функции Table.NestedJoin, он же пятый параметр функции Table.Join – тип выполняемого соединения (joinKind). Это перечисление JoinKind.Type. Допустимые значения:

Рис. 3.15а. Перечисление JoinKind.Type

Я дополнил перевод двумя новыми значениями, не вошедшими в книгу, а также альтернативной формулировкой от Chat GPT.

Любой, кто знаком с SQL, знает эти типы соединений.

Шестой параметр функции Table.Join – joinAlgorithm. Он позволяет выбрать алгоритм в процессе слияния. Это перечисление JoinAlgorithm.Type. Допустимые значения:

Рис. 3.15б. Перечисление JoinAlgorithm.Type

Использование алгоритма JoinAlgorithm.SortMerge может значительно ускорить операции слияния (на порядки). Тем не менее, вы должны следить за тем, чтобы ваши таблицы были правильно отсортированы, иначе вы получите неверные результаты. Можно использовать SortMerge в нашем примере:

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

Функции Table.NestedJoin и Table.Join требуют, чтобы ключевые столбцы точно совпадали друг с другом. Но в M есть функции и для приблизительного слияния.

Table.FuzzyNestedJoin и Table.FuzzyJoin

Функции Table.FuzzyNestedJoin и Table.FuzzyJoin позволяют выполнять слияние таблиц, даже если значения ключей в обеих таблицах точно не совпадают. Это называется нечетким соответствием. Параметры обеих функций близки их точным аналогам – Table.NestedJoin и Table.Join, за исключением того, что Table.FuzzyJoin не поддерживает параметр joinAlgorithm.

Алгоритм, используемый Power Query для измерения сходства между парами, называется алгоритмом подобия Жаккара. Он берет количество элементов, встречающихся в обеих парах (пересечение или внутреннее соединение), и делит его на общее количество элементов в обеих парах (объединение). В результате получается число от 0 до 1, где 0 указывает на отсутствие общих элементов (менее похожих), а 1 указывает на то, что обе пары имеют одинаковые общие элементы (более похожие).

Рис. 3.16. Алгоритм подобия Жаккара

Коэффициент Жаккара:

где а – количество элементов в первой корзине, b – количество элементов во второй корзине, с – количество общих (одинаковых) элементов.

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

ConcurrentRequests задает количество параллельных потоков, используемых для нечеткого сопоставления – число от 1 до 8 (по умолчанию 1).

Culture указывает должно ли сопоставление основываться на региональных настройках, которые определяются в соответствии со стандартом ISO 639, например, ja-JP, en-US, en-UK (по умолчанию используется «», что является инвариантом для региональных настроек английского языка).

IgnoreCase указывает следует ли игнорировать регистр букв при сопоставлении. Принимает значения true (по умолчанию) или false. Если выбран true, то значения «Grapes», «gRapes», «GrApEs» и «grapeS» будут считаться совпадающими.

IgnoreSpace принимает значения true (по умолчанию) или false. Позволяет комбинировать части текста, чтобы найти совпадения. Например, «grapes», «gra pes» и «grape s» будут совпадать.

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

SimilarityColumnName указывает имя столбца, возвращающего вычисленное сходство совпадающих строк (значение от 0,00 до 1,00). По умолчанию null, что означает, что такой столбец не возвращается. Строки со сходством выше указанного в параметре Threshold считаются совпадающими.

Threshold – пороговое значение для сопоставления строк на основе алгоритма нечеткого сопоставления, десятичное число от 0,00 до 1,00, по умолчанию 0,80.

TransformationTable – таблица для сопоставления строк со столбцами From, To. Например, таблица преобразования, в строке с виноградом в ключевых столбцах может содержать значения виноград или вкусный виноград.

Саммари

Библиотека M содержит множество функций, предназначенных для извлечения данных из широкого спектра систем, а также функции добавления и объединения данных. Практические примеры, включенные в главу, дают читателю возможность поэкспериментировать с этими функциями и посмотреть на их результаты. Мы рассмотрели функции доступа к данным для различных форматов файлов, папок, веб-контента, баз данных, кубов и стандартных протоколов данных OData и ODBC. Мы рассмотрели пять функций для добавления и объединения данных, включая возможность выполнения нечеткого сопоставления.

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