Коллекция кода на языке M для извлечения разных форматов из листов Excel в Power Query / Power BI
Информация на листах в книгах Excel часто имеет дополнительные метаданные, важные при анализе. Эти метаданные могут храниться в различных формах, в большинстве случаев - в виде форматов ячеек, числовых форматов, цвета и так далее. Часто формат строки, столбца или ячейки является критичным элементом набора данных.
В настоящий момент (август 2017) Microsoft Power Query и соответствующий редактор запросов в Microsoft Power BI не позволяет пользователям стандартными средствами извлекать из книг и листов Excel информацию о примененном форматировании листов и ячеек, за исключением некоторых типов данных (например, Дата).
Большое разнообразие форматов и сложность их извлечения сторонними программами (а не нативными средствами Excel), такими как Power BI или Power Query, приводит к потере значительной части информации при импорте. Дополнительной проблемой является сопоставление извлеченных форматов с табличными данными Power Query для дальнейшего использования.
Создаем набор отдельных функций, позволяющих извлечь заданную информацию о формате листа и/или ячейки.
В перспективе - создаем универсальные функции:
- для информации о листе (инфо о строках, столбцах, листе в целом)
- для информации о ячейках (форматирование цветом, шрифт, выравнивание, числовые форматы, отступы и т.п.)
Универсальность обусловлена одинаковыми применяемыми методами (распаковка и разбор XML), различный вывод в перспективе может задаваться параметрами функций.
Основной метод - распаковка файла XLSX/XLSM как архива и работа с XML документами внутри. Распаковка осуществляется функцией UnZip.pq, автор Mike White. В принципе, можно использовать любой аналог для работы с zip-архивами в Power Query.
После распаковки на вход в основную функцию поступает набор XML файлов (binary). Возможные способы разбора - при помощи встроенных функций Xml.Tables или Xml.Document, либо при помощи других способов парсинга XML.
- Основная проблема: внутри книги данные о форматах хранятся отдельно от ячеек, сами ячейки хранятся внутри строк, адрес ячейки задается в формате A1 (необходим дополнительный парсинг в R1C1 или аналог).
- Дополнительная проблема: связывание информации о форматах с позицией ячейки в выводимой таблице Power Query.
(готовые проекты снабжены ссылками)
- Структура листа:
- уровни группировки (вложенности) строк,
- уровни группировки (вложенности) столбцов,
- расширенная информация о строках (видимость, высота, вложенность, состояние группировки и т.д.),
- расширенная информация о столбцах (видимость, ширина, вложенность, состояние группировки и т.д.).
- Отступы и выравнивание ячеек
- Числовые форматы ячеек
- Цвет фона ячейки
- Добавление пустых строк и столбцов сверху-слева от UsedRange/dimension (о ловушке UsedRange см. этот пост)
- Условное форматирование, дополнительные форматы и дальнейшая разработка