Power query добавить столбец с именем файла
В большинстве случаев создание запросов PowerQuery создается на основании файлов Excel, отличных от того файла, в котором сам запрос. Как пример - одна из статей на этом сайте: План-фактный анализ в Excel при помощи Power Query. Там идет обращение к некоторым вспомогательным файлам с данными и запрос ко всем файлам определенной папки. Т.е. мы можем знать только папку относительно файла с самим запросом и имена некоторых вспомогательных таблиц. И здесь как раз возникает нюанс: если впоследствии переместить или переслать файл с запросом (или даже полностью всю модель данных со всеми файлами) - то придется для каждого запроса к отдельному файлу менять источник. Это можно сделать либо через Параметры источника данных самого запроса:
- Для пользователей Excel 2010-2013:
Перейти на вкладку Power Query -группа Настройки (Options) -Параметры источника данных (Data Source Settings) - для пользователей 2016 и выше:
Перейти на вкладку Данные (Data) -Создать запрос (New Query) -Параметры источника данных (Data Source Settings)
либо изменив текст каждого запроса в редакторе запросов, изменив там строку источника:
И в том и в другом случае после смены каждого источника придется ждать обновления запросов. Как ни странно, но стандартно, без танцев с бубном, сделать некий относительный путь(указав лишь часть пути, как это делается в web-программировании) к источнику данных нельзя.
Как же вообще сделать возможность обновления источника данных при смене расположения файлов?
Есть два варианта:
Вариант 1
Не совсем автоматический, но способный чуть облегчить жизнь - использовать возможность создания параметров для запросов. Параметр - это некая именованная константа, которая может быть как одним значением(число, текст, дата и т.д.), так и целой таблицей(возможно впоследствии добавят возможность создания вычисляемых параметров, но на момент написания статьи это не поддерживалось). В нашем случае это будет одно значение - путь к основной папке. Чтобы создать новый параметр необходимо перейти в редактор запросов(выбрать любой запрос в книге -Правая кнопка мыши -Изменить) и на вкладке Главная выбрать Управление параметрами -Создать параметр.
В появившемся окне задаем необходимые значения:
- Обязательно - читать как Обязательный, если флажок установлен и Необязательный, если флажок снят. Отвечает за необходимость указывать значение. Если флажок установлен - то параметр не будет создан/изменен, пока мы не укажем Текущее значение. Если флажок снят - то значение параметра допускается оставить пустым.
- Имя параметра - Без комментариев, я указал здесь sPath
- по желанию добавляем описание к параметру, ровным счетом это ни на что не влияет
- Тип данных параметра - в нашем случае это Текст. Здесь лучше не рисковать, указывая Любой(Any), т.к. в этом случае можем получить ошибку запроса.
- Предлагаемые значения - выбираем Любое значение(логичнее было бы назвать этот пункт "Одно значение". Т.к. это больше подходит на мой взгляд).
- Текущее значение - это как раз то значение, которое и будет хранить наш параметр. Здесь я указываю путь к основной папке: G:\Готовая модель ПланФактного анализа\ . Указываю со слешем на конце пути, чтобы не добавлять его в каждый источник после.
Теперь останется для каждого запроса добавить ссылку на этот параметр вместо жестко прописанного пути. Для этого в расширенном редакторе запросов(Главная -Расширенный редактор) для каждого запроса необходимо изменить переменную часть пути к файлам на наш параметр sPath:
Обращаю внимание, что после имени параметра(sPath) идет амперсанд(&). Он необходим для объединения двух текстовых строк в одну.
А теперь пара ложек дегтя для этого способа:
- Самый очевидный недостаток: при переносе файлов в любом случае необходимо будет заходить в редактор запросов (Главная -Управление параметрами -Изменить параметры) и изменять путь, указанный в параметре на новый. Так же это можно сделать напрямую из редактора запросов, раскрыв в левой части область запросов, выбрав параметр и вписав новое значение:
- И не очевидный недостаток: частенько такой подход приводит к ошибке получения данных при слиянии связанных запросов. Что делает этот способ не жизнеспособным в полной мере для большинства распространенных задач
Вариант 2
Создать еще один запрос в основной книге, на основании формул в Excel. Решение основано на возможностях встроенной функции Excel ЯЧЕЙКА (CELL) . С её помощью можно получить полный путь к файлу, имя листа и книги. Чуть более подробно синтаксис этой функции и способы получения имени листа и книги я описывал в этой статье: Как получить имя листа формулой.
Итак, для начала нам необходимо на новом листе создать новую таблицу с двумя столбцами: значение и описание. В качестве значения в первой строке у нас и будет как раз формула с функцией ЯЧЕЙКА:
=ПСТР(ЯЧЕЙКА("filename";A1);1;ПОИСК("[";ЯЧЕЙКА("filename";A1))-1)
данная формула возвращает только путь к файлу, в котором записана. Что нам в общем-то от неё и надо.
При желании можно дописать подсказку к значению. Я это сделал для демонстрации и чтобы было понятно - что мы сможем потом дополнять эту таблицу другими переменными значениями при необходимости.
Теперь из этой таблицы необходимо сделать динамическую, или как их еще называют - умную: выделяем ячейки с данными( A1:B2 ) -вкладка Вставка (Insert) и выбрать Таблица (Table) . Галочку "Мои данные содержат заголовки" оставляем включенной, даем понятное имя таблице - Parameters:
Далее выделяем любую ячейку внутри созданной таблицы и создаем новый запрос: вкладка Данные -Из таблицы(для пользователей PowerQuery 2013 и 2010 - вкладка PowerQuery -Из таблицы). Имя запроса у нас будет совпадать с именем таблицы - Parameters и этот запрос будет содержать как раз все значения нашей умной таблицы.
И теперь нам надо из этого запроса получить значение только одной конкретной ячейки - первой ячейки столбца "Значение"(в которой у нас формулой возвращается путь к папке). Для этого придется чуть пошаманить. Нам необходимо получить ссылку на таблицу "Parameters" и уже из неё получить значение нужной ячейки. Все это придется проделать на языке M, но звучит страшнее, чем выглядит - это всего одна строка:
Excel.CurrentWorkbook()[Content][Значение]
Теперь разберем по шагам:
-
Excel.CurrentWorkbook() - непосредственно функция, которая получает данные обо всех умных таблицах(и именованных диапазонах) внутри книги Excel, в которой создан этот запрос (CurrentWorkbook - текущая книга).
- так мы даем понять функции Excel.CurrentWorkbook , что нам нужны данные исключительно из объекта с именем "Parameters"(это наша умная таблица). На всякий случай уточню: получить просто ссылки на ячейки листа не получится, т.к. функция Excel.CurrentWorkbook данных о листах не получает вообще. Только именованные диапазоны и умные таблицы.
[Content] - все содержимое таблицы "Parameters"
- пожалуй, самая хитрая часть для "не программистов" :) Это номер строки в указанной таблице("Parameters"). При этом номера строк в запросе начинают отсчет с 0 и заголовки при этом не учитываются. Т.е. наш параметр находится физически во второй ячейке столбца "Значение" таблицы "Parameters" на листе. Но в рамках самой таблицы это первая её строка, т.к. заголовок не учитываем. Но т.к. в таблице отсчет начинается с 1, а в запросах с 0 - то и нам надо указывать, учитывая эту особенность. Если бы мы обращались ко второй строке таблицы - указать необходимо было бы 1. И да - указывать обязательно в фигурных скобках.
[Значение] - здесь в квадратных скобках указывается имя столбца(без всяких кавычек). Если бы столбец в таблице был всего один - то можно было бы его не указывать вовсе. Но т.к. у нас их больше - то указание обязательно, иначе запрос вернет всю строку - т.е. значения всех столбцов таблицы.
Т.е. строка Excel.CurrentWorkbook()[Content][Значение] означает: из книги с запросом обратиться к таблице "Parameters"( ), считать все данные( [Content] ) и отобрать оттуда значение первой строки( ) столбца "Значение"( [Значение] )
И эта строка возвращает исключительно путь к папке - именно тот, который у нас получается в результате вычисления формулы с ЯЧЕЙКА.
Теперь, после того как разобрались что делает чудо-строка - осталось понять как это применить. Надо просто для каждого запроса перейти в редактор и в строке с источником:
Источник = Folder.Files(" C:\Готовая модель ПланФактного анализа \Факт"),
вместо части пути указать созданную строку запроса, добавив амперсанд(&) для объединения разных значений:
Источник = Folder.Files( Excel.CurrentWorkbook()[Content][Значение] & "\Факт"),
Все, теперь при перемещении книги с запросом или всей модели данные будут обновляться без какого-то ручного вмешательства.
В общем-то, такой подход можно использовать для задания даже имен файлов, используемых в модели. Например, можно дополнить таблицу именем файла с данными плана или путем к вспомогательным таблицам.
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
Power Query имеет специальную опцию для импорта всех файлов из папки. Эта глава посвящена импорту двоичных файлов, таких как TXT и CSV. Следующая глава посвящена импорту данных из нескольких книг Excel. Примеры файлов для этой главы разбиты на четыре подпапки: Begin, 2008 – More, 2009, 2010. Большинство скриншотов получено в Excel 365 (июль 2019). Упоминаются отличия, замеченные в Excel 2019.
Создайте новую книгу, пройдите на вкладку Данные, кликните Получить данные –> Из файла –> Из папки:
Рис. 4.1. Меню импорта файлов из папки
В окне Папка выберите папку Begin (рис. 4.2), нажмите Ok.
Рис. 4.2. Выбор папки для импорта файлов
Откроется окно, в котором отражаются все файлы в папке и их тип:
Рис. 4.3. Окно предварительного просмотра содержимого выбранной паки; в Excel 2019 кнопка Преобразовать данные называется Изменить; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Нажмите Преобразовать данные, и список файлов загрузится в редактор Power Query:
Рис. 4.4. Окно загрузки файлов в редакторе Power Query
В этой главе вы будете объединять csv-файлы из четырех папок. Прежде чем идти дальше, вы должны отфильтровать список, чтобы оставить только csv-файлы. Если этого не сделать, а кто-то в будущем поместит в вашу папку Excel-файл, это может сломать загрузку.
Для начала застрахуйтесь от того, что расширение может быть набрано в верхнем CSV или нижнем csv регистре (поскольку текстовые фильтры чувствительны к регистру). Щелкните правой кнопкой мыши на заголовке столбца Extension (расширение) –> Преобразование –> нижний регистр. Далее щелкните стрелку фильтра в столбце Extension, пройдите по меню Текстовые фильтры –> Равно, и в открывшемся окне фильтрация строк, установите равно csv (рис. 4.5). Поскольку у вас в папке есть только csv-файлы, то кликнув на стрелку фильтра вы увидите Выбрать все. Не переживайте, в строке формул отразился правильный фильтр (см. строку формул на рис. 4.4). Обратите внимание, что в фильтре перед csv нужна точка.
Рис. 4.5. Установка фильтра для файлов загрузки
Объединение файлов
Посмотрите на значки в первых трех столбцах текущего запроса:
Рис. 4.6. Пиктограммы первых трех столбцов текущего запроса
В столбце Name отображается значок невыбранного фильтра, в столбце Extension – примененный фильтр. Но самым замечательным является значок в столбце Content. Эта пиктограмма появляется на столбцах, содержащих двоичные файлы, и при ее нажатии происходит объединение файлов. Сначала откроется окно предварительного просмотра (рис. 4.7), а после нажатия Ok, все файлы из папки Begin объединятся в один запрос, и будут импортированы в редактор Power Query (рис. 4.8). Любопытно, что в Excel 2019 при импорте был также добавлен столбец Source.Name, содержащий имя файла в каждой строке трансакции. Таким образом, можно было бы обойтись без части ухищрений, описанных ниже))
Рис. 4.7. Окно предварительного просмотра Объединить файлы
Переименуйте столбцы, измените тип данных с использованием локали, удалите ошибки, переименуйте запрос в Transactions, кликните Закрыть и загрузить (подробнее см. главу 2).
Рис. 4.8. Объединенные данные в окне редактора Power Query
Убедитесь, что таблица в Excel включает данные за три месяца. Создайте сводную:
Рис. 4.9. Сводная таблица на основе объединенных данных из трех файлов из папки Begin
Добавление новых файлов
Добавить новые файлы в запрос довольно просто:
Рис. 4.10. Добавление новых файлов в запрос
Перетащите папки 2009 и 2010 в папку Begin. Повторите операции обновления запроса и обновления сводной таблицы.
Рекурсия. Для добавления новых файлов в запрос не потребовалось перемещать файлы в корень папки Begin. Power Query по умолчанию использует рекурсию, т.е. проверяет корневую и все вложенные папки на наличие файлов с правильным расширением. Это позволяет сохранить файлы в отдельных папках, например, по годам (если это необходимо).
Если вы откроете редактор запросов и перейдете на шаг Источник, то увидите, что для каждого файла указан путь к папке. Если требуется, вы можете применить фильтры к этому столбцу, чтобы включить только корневую папку или исключить определенные папки.
Рис. 4.11. С помощью фильтрации можно включать в запрос только нужные папки
Создание пользовательского столбца
На вкладке "Добавить столбец" выберите "Настраиваемый столбец".
Откроется диалоговое окно "Настраиваемый столбец ". В этом диалоговом окне определяется формула для создания столбца.
Диалоговое окно "Настраиваемый столбец" содержит следующее:
Начальное имя настраиваемого столбца в поле "Новое имя столбца ". Столбец можно переименовать.
Раскрывающееся меню, в котором можно выбрать тип данных для нового столбца.
Список доступных столбцов справа под выбранным типом данных .
Поле формулы настраиваемого столбца, в котором можно ввести формулу Power Query M.
Чтобы добавить новый настраиваемый столбец, выберите столбец из списка доступных столбцов . Затем нажмите кнопку "Вставить столбец " под списком, чтобы добавить его в формулу настраиваемого столбца. Вы также можете добавить столбец, выбрав его в списке. Кроме того, можно написать собственную формулу с помощью языка формул Power Query M в формуле настраиваемого столбца.
Сохранение свойств файла при импорте
Допустим ваша бухгалтерская система настроена так, что экспортирует список транзакций в файл и присваивает ему имя месяца и года (например, Feb 2008.csv). Система не включает даты транзакций в файл, поскольку все они относятся к дате окончания месяца. При импорте нескольких файлов жизненно важно указать дату в каждой строке.
Вернитесь в редактор запросов. Перейдите на шаг Отфильтрованные скрытые файлы1. Выберите столбец Name, удерживая нажатой клавишу Ctrl, выберите столбец Content. Щелкните правой кнопкой мыши один из выделенных столбцов, выберите Удалить другие столбцы.
Power Query обрабатывая каждый шаг перед переходом к следующему. Это означает, что в отличие от Excel, он не требует наличия столбцов, в которых установлен фильтр. Учитывая это, и поскольку вы уже отфильтровали csv-файлы, вы можете удалить и этот столбец.
Во-первых, вы удалили все посторонние столбцы самым надежным способом. Это способ лучше, чем выделение столбцов, которые нужно удалить. Если в будущем в таблице появятся дополнительные столбцы, они всё равно будут удалены, так как команда фактически говорит: «оставь только столбцы Name и Content». Во-вторых, из-за порядка, в котором вы выбрали столбцы, вы перевернули порядок, в котором они представлены в редакторе Power Query:
Рис. 4.12. Два оставшихся столбца расположены в порядке обратном, к тому что был до команды Удалить другие столбцы
Следующая задача – преобразовать имена файлов в допустимые даты конца месяца:
- Щелкните правой кнопкой мыши столбец Name, выберите Замена значений
- Замените .csv на пусто
- Еще раз щелкните правой кнопкой мыши столбец Name, выберите Замена значений
- Замените ˽ (пробел) на ˽1,˽ (пробел, единица, запятая, пробел)
Рис. 4.13. Промежуточный результат преобразований
Теперь от первого числа месяца можно перейти к последнему дню месяца:
- Щелкните правой кнопкой мыши столбец Name –>Тип изменения –>Дата
- Перейдите на вкладку Преобразование –>Дата –>Месяц –>Конец месяца
- Щелкните правой кнопкой мыши столбец Name –>Переименовать –>Date
Рис. 4.14. Преобразование первого числа месяца в последнее число месяца
Нет необходимости этот набор дат импортировать с использованием локали, так как Power Query правильно распознает полнотекстовую дату.
Добавление столбца Total Sale after Discount
Формула, которую можно использовать для создания итоговой продажи до скидки [Total Sale before Discount]* (1-[Discount]) . Задайте для этого нового столбца тип данных Currency.
В результате этой операции в таблицу добавляется новый столбец Total Sale после столбца "Скидка ".
Объединение файлов
Посмотрите на значки в первых трех столбцах текущего запроса:
Рис. 4.6. Пиктограммы первых трех столбцов текущего запроса
В столбце Name отображается значок невыбранного фильтра, в столбце Extension – примененный фильтр. Но самым замечательным является значок в столбце Content. Эта пиктограмма появляется на столбцах, содержащих двоичные файлы, и при ее нажатии происходит объединение файлов. Сначала откроется окно предварительного просмотра (рис. 4.7), а после нажатия Ok, все файлы из папки Begin объединятся в один запрос, и будут импортированы в редактор Power Query (рис. 4.8). Любопытно, что в Excel 2019 при импорте был также добавлен столбец Source.Name, содержащий имя файла в каждой строке трансакции. Таким образом, можно было бы обойтись без части ухищрений, описанных ниже))
Рис. 4.7. Окно предварительного просмотра Объединить файлы
Переименуйте столбцы, измените тип данных с использованием локали, удалите ошибки, переименуйте запрос в Transactions, кликните Закрыть и загрузить (подробнее см. главу 2).
Рис. 4.8. Объединенные данные в окне редактора Power Query
Убедитесь, что таблица в Excel включает данные за три месяца. Создайте сводную:
Рис. 4.9. Сводная таблица на основе объединенных данных из трех файлов из папки Begin
Импорт из папки
Источником в Power Query могут быть не только отдельные файлы, но и целая папка со всем ее содержимым. Далее дело следующей техники.
Выглядит подозрительно, т.к. вместо данных что-то непонятное. Действительно, сейчас видны только файлы, содержащиеся в папке, и информация о них: название, расширение, время создания, изменения и т.д. Переходим к извлечению данных из этих файлов.
Следующий шаг не является обязательным, но он позволит избежать некоторых возможных проблем в будущем. Нужно понимать, что все содержимое указанной папки будет извлечено с помощью запроса Power Query. И если туда попадет какой-нибудь, например, файл Excel, то запрос «поломается» и выдаст ошибку. Поэтому опытные пользователи создают «защиту», чтобы файлы с другим расширением отфильтровывались.
Идея в том, чтобы в столбце Extension поставить фильтр на расширение .csv. Чтобы случайно не отфильтровать файлы .CSV, сделаем все буквы для столбца Extension маленькими. Для этого выделяем столбец, далее через правую кнопку мыши Преобразование – нижний регистр. Переходим к установке фильтра. Если в выпадающем списке фильтра поставить переключатель на значение .csv, то у нас ничего не получится, т.к. при единственном типе файлов будет автоматически выделен пункт Выбрать все. Поэтому выбираем Текстовые фильтры – Равно… и указываем .csv (обязательно с точкой впереди).
Теперь случайное добавление в указанную папку файлов Excel не повлияет на работу запроса. Этот шаг, повторюсь, необязательный, но лучше прислушаться к совету опытных пользователей.
Приступим к извлечению данных. Содержимое файлов скрыто в колонке Content за значением Binary.
Перед тем, как развернуть содержимое этого столбца, избавимся от лишней информации. Выделяем столбец Content и через правую кнопку мыши выбираем Удалить другие столбцы.
Наступило время сеанса магии с разоблачением. В верхнем правом углу находится кнопка с двумя стрелками, направленными вниз.
Это кнопка загрузки двоичного (бинарного) файла. Жмем. И о чудо! Содержимое всех трех файлов один за другим выгружается в единую таблицу.
Однако на этот раз потребуется вручную внести некоторые корректировки.
• Удалим последний шаг Измененный тип
• Преобразование – Использовать первую строку в качестве заголовков
• Правой кнопкой мыши по полю Дата – Тип изменения – Дата
• Удерживая Shift, выделяем два столбца Наименование и Менеджер, затем через правую клавишу мыши Тип изменения – Текст
• Через Shift выделяем остальные столбцы Цена, Стоимость, Комиссия – правая клавиши мыши – Тип изменения – Десятичное число
• Правой кнопкой мыши по полю Дата – Удалить ошибки
• Главная – Закрыть – Закрыть и загрузить
Таким образом, мы получаем таблицу с единым заголовком, сделанную из трех файлов. На ее основе создадим сводную таблицу.
Сводная таблица построена по 116 строкам. Таким же образом можно было бы объединить и 10 файлов с сотнями тысяч строк.
Изменение существующего настраиваемого столбца
Power Query добавляет настраиваемый столбец в таблицу и добавляет настраиваемый шаг в список примененных шагов в параметрах запроса.
Чтобы изменить настраиваемый столбец, выберите добавленный пользовательский шаг в списке примененных шагов .
Откроется диалоговое окно "Настраиваемый столбец " с созданной вами формулой настраиваемого столбца.
В зависимости от формулы, используемой для пользовательского столбца, Power Query изменяет поведение параметров шага для более упрощенного и собственного интерфейса. В этом примере добавленный пользовательский шаг изменил его поведение с стандартного настраиваемого шага столбца на интерфейс умножения , так как формула из этого шага умножает только значения из двух столбцов.
На примере файлов бюджетов покажу как можно собирать данные со всех этих файлов в одну итоговую таблицу и просуммировать все присланные данные по статьям из каждой таблицы.
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Модель агрегации файлов.zip (53,5 KiB, 1 206 скачиваний)
Видеоинструкция:
Для ведения бюджета применяется таблица такого вида:
Сама таблица преобразована заранее в так называемую "умную" таблицу: выделяем таблицу -вкладка Вставка (Insert) и выбрать Таблица (Table) :
Для каждого филиала отдельный файл только с одним этим листом. После заполнения филиалы присылают эти файлы в головной офис, где их необходимо объединить в одну такую же таблицу, но суммировать данные по каждой статье и каждому месяцу, чтобы получить единый файл бюджета с суммированием по каждой статье от всех филиалов.
Все действия будут производиться при помощи Power Query и лишь в самом конце на лист будет выгружена итоговая таблица, которую потом надо будет только обновлять(пара кликов мыши), если данные изменятся или будут присланы файла от за другие месяцы или от других филиалов. Никаких макросов использовать не надо.
Перейдем к реализации.
Создаем новую пустую книгу, переходим на вкладку Данные(или Power Query) -Получить данные -Из файла -Из папки:
В появившемся окне указываем путь к папке, в которую были помещены файлы бюджетов, присланные филиалами
Нажимаем Ок.
Появится окно, в котором будет список всех файлов в выбранной папке. Нажимаем Изменить и попадем в редактор запросов Power Query. Здесь пошагово мы и будем делать все преобразования отчетов для их объединения и приведения к нужному виду.
Для начала удалим лишние столбцы, оставив только два столбца: Content и Name :
Для этого выделяем лишние столбцы с зажатой клавишей Shift и нажимаем Delete(или правая кнопка мыши -Удалить столбцы).
Теперь надо получить таблицы из файлов. Для этого переходим на вкладку Добавить столбец -Пользовательский столбец. В появившемся окне даем имя новому столбцу(у меня это Данные), а в поле формулы вписываем такую функцию:
=Excel.Workbook([Content])
Нажимаем Ок.
В отчет будет добавлен новый столбец. Необходимо его «развернуть» - получить все данные из каждого файла. Для этого нажимаем на этом столбце значок в виде двух разнонаправленных стрелок, снимаем галочку «Использовать исходное имя столбца как префикс» и нажимаем Ок:
Будет добавлено еще два столбца, из которых аналогичным образом разворачиваем столбец Data(нажатием на значок в виде двух разнонаправленных стрелок). Там будут наименования вроде Column1, Column2 и т.д. – это нормально, выгружаем все как есть. Получится такая картина:
Теперь столбцы Content , Name и Name.1 можно удалить (в столбце Name записано имя файла, поэтому если оно нужно – можно оставить на время отладки запроса. Но впоследствии данные будут объединены и просуммированы и оно все равно будет лишним).
Т.к. у нас реальные данные в таблицах начинаются не с первой строки и имеется шапка – необходимо убрать все лишние строки, чтобы исключить ошибки при дальнейшем суммировании данных. Для этого сначала в Column2 раскрываем меню фильтра и убираем галочки со значений NULL :
А в Column1 в фильтре убираем галочку с пункта «Статьи». Теперь первой строкой данных у нас идут названия месяцев. Делаем их заголовками: вкладка Преобразование -Таблица -Использовать первую строку в качестве заголовков:
Т.к. первый столбец теперь будет иметь не совсем понятное имя вроде Column1 - имеет смысл переименовать его в «Статьи».
Далее выделяем все столбцы месяцев и столбец Итого -вкладка Преобразование -группа Любой столбец -раскрываем список Тип данных и выбираем Десятичное число:
Теперь надо объединить все одинаковые строки статей и просуммировать данные по ним за каждый месяц. Выделяем столбец Статьи вкладка Преобразование -Таблица -Группировать по:
В появившемся окне сразу выбираем режим Дополнительно и указываем параметры группировки:
Группировка – оставляем поле Статьи . Ниже создаем 13 столбцов группировки – по одному на каждый месяц и один для Итого. Для каждого столбца указываем имя(лучше такое же как и имя исходного столбца – название месяца, т.к. именно они будут использоваться в итоговой таблице), Операция – Сумма .
Останется перейти на вкладку Главная -Закрыть и загрузить. Готовая таблица будет выгружена на новый лист текущей книги.
Теперь, если в папку будут помещены другие файлы или имеющиеся будут заменены другими и результирующую таблицу бюджета потребуется обновить – все, что необходимо будет сделать, это на созданной PowerQuery таблице в любой ячейке щелкнуть правой кнопкой мыши и выбрать Обновить:
Все файлы в папке будут просмотрены, преобразованы и просуммированы.
Одним из популярных источников для импорта в Power Query являются файлы типа .csv или .txt. Традиционные методы импорта и объединения текстовых файлов довольно неуклюжи и отнимают много времени. Однако применение Power Query многократно облегчает решение проблемы. Пусть у нас есть отчет о продажах за январь в формате .csv. Задача в том, чтобы импортировать его в Excel.
В открывшемся окне указываем путь к файлу. Далее в предпросмотре убеждаемся, что выбран нужный файл, и нажимаем Изменить, чтобы попасть в редактор Power Query.
Power Query распознал и разделил данные по столбцам. Также автоматически выполнены шаги:
• Первая строка повышена в заголовки
• Изменен тип для каждого столбца
Если обработки не требуется, то результат можно выгружать в книгу Эксель Главная – Закрыть – Закрыть и загрузить.
На новом листе создается Таблица Excel.
Теперь можно проводить анализ внутри Excel. К этому мы еще вернемся. Прошел месяц, и у нас появился такой же файл .csv за февраль. Возникает вопрос, как объединить данные за два месяца? В Power Query для этого есть специальные инструменты.
Уберем пока из листа Excel данные за январь. Для этого в панели запросов через правую клавиши выбираем Загрузить в…, где меняем настройки выгрузки на Только соединение.
Выскочит предупреждение о том, что таблица будет удалена. Нажимаем Продолжить. Таблица из Excel удалилась, запрос остался в виде соединения.
Делаем новый запрос на файл за февраль и сразу создаем только соединение.
Теперь у нас два запроса, из которых нужно сделать общий, объединяющий два файла.
Далее нужно выбрать добавляемые запросы.
Следует понимать две вещи.
• Добавлять можно только запросы (а не Таблицы Excel)
• Запрос в верхнем поле будет первым сверху
Добавить можно было бы и больше запросов, выбрав в верхней строке Три таблицы или больше, но у нас только два. Нажимам ОК. Создается объединенный запрос под названием Append1.
Изменим название на Отчет о продажах и выгрузим данные в Excel (Главная – Закрыть – Закрыть и загрузить). Итого получим три запроса.
Первые два служат источником для объединенного, который выгружен на лист Excel. Создадим по этим данным сводную таблицу.
Через месяц появился файл за март, и вы также решили добавить его в общий отчет и сводную таблицу. Создаем запрос к новому файлу Март.csv. Затем его нужно добавить в запрос Отчет о продажах. Однако, если повторить все шаги с добавлением, то будет создан новый общий запрос, который уже не имеет смысла, т.к. сводная таблица строится по данным из запроса Отчет о продажах. Поэтому нужно зайти в редактирование запроса Отчет о продажах и уже там добавить новый запрос за март. В редакторе Power Query выбираем Главная – Комбинировать – Добавить запросы. В окне добавления теперь только одно поле, т.к. первый запрос уже определен. Нужно выбрать лишь добавляемый.
Нажимаем ОК и снова выгружаем обновленные данные в Excel (Главная – Закрыть – Закрыть и загрузить). Теперь в Таблице Excel находятся данные за три месяца. Нужно только обновить сводную таблицу.
Для импорта новых файлов нужно будет повторить все действия:
• создание нового запроса
• добавление его в объединяющий запрос Отчет о продажах
• выгрузка в Excel
• обновление сводной таблицы.
Несмотря на то, что таким образом можно консолидировать данные из многих файлов, был бы здорово, если бы они сами добавлялись в сводный запрос. Так тоже можно.
Добавление в запрос новых файлов и обновление сводной таблицы
Через два месяца в наше распоряжение поступило еще два файла с данными за апрель и май. Их также требуется добавить в сводную таблицу.
И вот здесь наступает момент истины. Все что нужно, это закинуть новые файлы в указанную папку и на ленте во вкладке Данные нажать Обновить все. Первое нажатие обновит все запросы, второе – сводные таблицы.
Power Query вновь обратился в папку по указанному адресу, затащил к себе все файлы, раскрыл их, повторил все шаги обработки и выгрузил в Excel. Второе нажатие кнопки обновило сводную таблицу.
Следует только помнить, что для объединения подобным образом таблиц из разных текстовых файлов необходимо полное совпадение заголовков, иначе они автоматически разведутся по разным столбцам.
Добавление столбца Total Sale до столбца "Скидка"
Формула, используемая для создания итоговой продажи до столбца [Units] * [Unit Price] "Скидка". В типе данных выберите тип данныхCurrency .
В результате этой операции в таблицу добавляется новый столбец Total Sale перед столбцом "Скидка ".
Если вы используете Power Query Desktop, вы заметите, что поле типа данных недоступно в настраиваемом столбце. Это означает, что после создания столбцов необходимо определить тип данных для любых настраиваемых столбцов. Дополнительные сведения: типы данных в Power Query
Объединение свойств файла с двоичным содержимым
На этом этапе вы готовы объединить даты окончания месяца со всеми строками внутри двоичных файлов. Проблема в том, что стандартное нажатие кнопки объединить двоичные файлы (см. рис. 4.6) выбросит столбец Date, над которым вы так усердно работали. Поэтому вам нужно извлечь содержимое каждого двоичного файла:
- Перейдите на вкладку Добавление столбца –>Настраиваемый столбец
- Введите формулу: =Csv.Document([Content]). Внимание! Формулы Power Query чувствительны к регистру.
- Нажмите Ok
Если вы не хотите вводить длинное имя поля, вы можете просто дважды щелкнуть его в списке полей при создании формулы пользовательского столбца. Это поместит поле в формулу в обрамлении квадратных скобок. В результате появится новый столбец под названием Пользовательская. В каждой строке этого столбца содержится таблица (Table):
Рис. 4.15. В запрос добавлен пользовательский столбец
Csv.Document() – это функция, которую можно использовать для преобразования содержимого двоичного файла в таблицу. [Content] – просто ссылка на имя столбца. Любопытно, что функции Txt.Document() не существует. Для преобразовать содержимое текстового файла в таблицу, также используете Csv.Document().
Чтобы увидеть, что находится внутри любого из этих двоичных файлов, щелкните пустое пространство рядом со словом Table (1 на ри. 4.16). Откроется окно предварительного просмотра (2). Если вы щелкните на само слово Table, Power Query попытается запустить очередной шаг навигации. Кликните Отмена.
Рис. 4.16. Предварительный просмотр таблицы
Правой кнопкой мыши кликните заголовок столбца Content –> Удалить. Нажмите маленький двуглавый символ стрелки в правом верхнем углу столбца Пользовательский:
Рис. 4.17. Окно выбора столбцов Table, которые нужно развернуть
Это диалоговое окно позволяет выбрать, какие столбцы следует развернуть. Если некоторые столбцы вам не нужны, снимите галку. Вы уже видели в предварительном просмотре, что каждый из четырех столбцов содержит полезную информацию, поэтому сохраните их все. Снимите флажок Использовать исходное имя столбца как префикс. Если вы оставите его, Power Query добавит префикс Пользовательская к названию каждого столбца. Так что они будут иметь названия Пользовательская.Столбец1, Пользовательская.Column2, и так далее. В нашем случае, это не так важно, так как на следующем шаге вы выполните операцию Использовать первую строку в качестве заголовков.
При нажатии кнопки Ok (рис. 4.17) все столбцы разворачиваются, но Power Query сохраняет столбец Date (в отличие от функции объединения двоичных файлов, которая удалила его). Еще раз подчеркну. Это произошло потому, что мы развернули не двоичные файлы, а пользовательские таблицы.
Рис. 4.18. При разворачивании таблиц Table столбец Date сохраняется
Повысьте заголовки. Обратите внимание, что первый столбец Date превратился в 29.02.2008. Переименуйте его в Month End. Удалите шаг Столбец расширенной таблицы1. В первом примере он выполнял функцию разворачивания двоичных файлов. Остальные шаги должны продолжить измененную первую часть запроса (переименование, измененный тип с локалью, удаление ошибок).
Рис. 4.19. Все файлы, импортированные из папки, сохранили дату на основе свойств файла
Измените имя запроса на ManualCombine. На вкладке Главная кликните Закрыть и загрузить. Если вы обновите сводную таблицу, то увидите, что она соответствует полученной ранее (см. рис. 4.10), с той лишь разницей, что появилось новое поле Month End.
Резюме. Импорт отдельных файлов, как описано в главе 2, как правило, является стартовым, когда вы только осваиваете работу с Power Query. Этот подход хорошо работает если набор данных стабилен, или требования к преобразованию различных файлов отличаются. Если же вы предполагаете, что решение на основе Power Query может масштабироваться, используйте подход, описанный в этой главе. Даже если сегодня в папке будет только один файл. Зато потом вам просто нужно будет поместить новый файл в папку и нажать Обновить. Иногда немного предвидения может значительно облегчить будущие работы.
Если вам требуется больше гибкости для добавления новых столбцов, чем указанных в поле в Power Query, можно создать собственный настраиваемый столбец с помощью языка формул Power Query M.
Imagine таблицу со следующим набором столбцов.
Используя столбцы "Единицы", "Цена за единицу" и "Скидка" , вы хотите создать два новых столбца:
- Общая продажа до скидки: вычисляется путем умножения столбца "Единицы" на столбец "Цена за единицу ".
- Общая продажа после скидки: вычисляется путем умножения итоговой продажи до столбца "Скидка " на чистое процентное значение (один минус значение скидки).
Цель — создать таблицу с новыми столбцами, содержащими общий объем продаж до скидки и общих продаж после скидки.
Читайте также: