Power query добавить столбец с именем файла

Обновлено: 06.07.2024

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Power Query дает нам новую вкладку в ленточном интерфейсе Excel, где мы сможем импортировать, преобразовывать и объединять данные из различных источников. В дополнение к стандартным источникам данных, таких как Microsoft Access, SQL и текстовым файлам, Power Query позволяет импортировать из Active Directory, Azure, OData и Hadoop.

Power Query – это бесплатная надстройка для Excel 2010 и Excel 2013, которую можно скачать здесь.

Обратите внимание, что Power Query ранее был известен под кодовым именем “Data Explorer”. Аналогично, Power Map ранее была известна как проект “GeoFlow”.

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

1. Основы использования Power Query

После установки надстройки, вы увидите новую вкладку Power Query на ленте Excel.

В группе Get External Data в левой части вкладки можно выбрать источники данных. В этом первом примере я выберу From Web и введу интересующий меня сайт.

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

Вы можете удалить данные из таблицы непосредственно в окне запроса. На картинке внизу, я щелкнул правой кнопкой мыши по заголовку столбца и выбрал Use First Row As Header, что означает Использовать первую строку в качестве заголовка.

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

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

2. Работа с каналами данных

Инструмент Power Query имеет механизм, поиска данных в интернет. К примеру, вам захотелось посмотреть календарь соревнований зимних Олимпийских игр в Сочи. Для этого вы можете воспользоваться Power Query.

Щелкаем по кнопке Online Search, в появившемся диалоговом окне вводим поисковый запрос Sochi. Результатом работы поисковой машины Excel станет список статей, где встречается слово Sochi. Если мы щелкнем по любому из них, данные появятся на рабочем листе.

3. Получаем список файлов в папке

Появлялась ли когда-нибудь у вас нужда выгрузить список файлов в папке? У меня да. И для этого я писал отдельный макрос. Ну, это дела давно минувших дней, теперь Power Query позволяет сделать тоже самое в несколько щелчков.

Выбираем From File в группе Get External Data и определяем папку, из которой мы хотим извлечь список.

Excel активирует окно запроса, где будет виден список всех файлов в папке.

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

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

4. Консолидация данных, или как создать одну таблицу из нескольких Excel файлов

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

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

Изначально синтаксис выглядит безнадежно непроницаемым. Но немного поигравшись с Power Query, вы поймете, как он работает. Ключ в том, что необходимо сделать какие-либо действия в запросе, а затем посмотреть, как изменится код. Через некоторое время вы сможете создавать собственные запросы для выполнения сложных действий.

Вы также можете посетить страницу формул Power Query для большего понимания.

Для примера предположим, что мне необходимо получить данные с двух файлов Excel и поместить их в одну таблицу. Структура файлов одинакова (в каждом одинаковые колонки).

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

Для начала необходимо включить расширенные возможности редактирования запросов, во вкладке Power Query в группе Machine Settings щелкаем по кнопке Options -> Enable Advanced Query Editing.

Теперь мы готовы писать запрос. В группе Get External Data выбираем From Other Sourses -> Blank Query.

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

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

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

Вам также могут быть интересны следующие статьи

7 комментариев

Спасибо за идею, думаю в ближайшем будущем можно будет ожидать статью на тему надстройки NodeXL в Excel

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

К сожалению, с SharePoint не работал, не могу ответить

вы описали пример как подкачать данные из двух файлов.
А как настроить, чтобы данные закачивались из всех файлов определенной папке?

Автоматический поиск файла источника в папке с помощью Power Query.

При создании запроса к данным другой книги в Power Query путь к файлам статичен, и представляет собой полный путь C:\Users\Пользователь\Documents\отчёт май\продажи.xlsx

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

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

В ячейку А2 пишем формулу которая будет показывать адрес папки в которой находиться файл

2. Оставить только путь к папке, убрав всё, что после квадратной скобки [ : C:\Users\Oksana\Documents\май\

Добавить имя файла к которому будет подключаться запрос: C:\Users\Oksana\Documents\май\продажи.xlsx

создаем пустой запрос: Данные → Создать запрос →Из других источников → Пустой запрос

пишем в строке формул запрос к ячейке содержащей путь к файлу

3.Заменяем статический адрес на сделанный запрос

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

Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.

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

Power Query в меню Excel

В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).

Работа с данными в Power Query

Power Query умеет:

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

Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).

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

В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.

Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).

Для примера добавим в Power Query данные из таблицы.

— в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
— для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).

В открывшемся окне поставьте галочку «Таблица с заголовками».

Таблица с данными при этом превратится в «умную» smart-таблицу.

Откроется окно редактора запросов, в котором будет наша таблица. Нажимаем кнопку в меню Главная → Закрыть и загрузить. Готово!

Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.

Редактор запросов Power Query

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

Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.

Итак, в редакторе Power Query есть:

Лента редактора запросов для вкладок меню: Главная, Преобразование, Добавить столбец, Просмотр.
Перечень созданных запросов, который можно свернуть / развернуть.
Строка формул.
Название самого запроса.
Примененные шаги запроса: записанные шаги получения или преобразования данных. Их можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
Область предварительного просмотра, в которой выводится результат преобразования данных для каждого шага.
Меню для данных, которое открывается при нажатии правой кнопкой мышки.
При выборе правой кнопкой мыши названия шага появляется его контекстное меню.

Преобразование данных

Посмотрим на простом примере, как преобразовать данные в Power Query.

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

Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:

выделите таблицу (можно выделить таблицу целиком или одну из ячеек);
выберите в меню Данные → Из таблицы (Из таблицы/диапазона);
в появившемся окне поставьте галочку рядом с «Таблица с заголовками» → ОК;
в открывшемся редакторе запросов выделите столбцы «показатель» и «город», нажав мышкой на названия столбцов с зажатым Ctrl;
в меню нажмите Преобразование → Заполнить → Вниз.

При создании запроса Power Query сам автоматически записывает его шаги. Их можно увидеть в области справа Параметры запроса → Примененные шаги.

Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.

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

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

Если вы импортируете данные в Power Query или Power BI из файла Excel, обращаясь к листу целиком, будьте осторожны – вас может поджидать ловушка.

При подключении к стороннему файлу Excel нам доступны три варианта извлечения данных:

Таблица (форматированный как таблица диапазон ячеек на листе)
Именованный диапазон (диапазон ячеек, которому присвоено пользовательское имя)
Лист целиком

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

Данные с неразмеченного листа

Листы Excel доступны в качестве источника наравне с таблицами и именованными диапазонами

Однако возникает вопрос, какие данные попадут в таблицу для этого листа? На листе Excel 17 179 869 184 ячеек (16 384 столбцов и 1 048 576 строк). Если бы Power Query пытался загрузить их все, это привело бы к безнадежным «тормозам» при импорте данных таким образом. Однако мы можем убедиться, что обычно количество строк и столбцов примерно соответствует заполненным.

Как же Power Query определяет нужный диапазон данных? Ответ может быть достаточно очевиден, если у вас есть достаточный опыт программирования на VBA и вы хорошо знакомы с объектной моделью Excel (и ответ вас не обрадует). А именно, Power Query использует специальный диапазон ячеек листа, который называется UsedRange.

Непредсказуемый UsedRange

Если вы не настолько хорошо знакомы с VBA и UsedRange, ниже я привел пояснение того, как работает этот объект.

Мы не видим UsedRange в списке пользовательских имен, и не можем к нему обратиться иначе, как с помощью редактора Visual Basic. Чтобы узнать его адрес на текущем листе, нажмите Alt-F11, затем Ctrl-G, и в окошке Immediate введите следующую команду:

UsedRange – это диапазон, определенный автоматически на основе содержимого ячеек, их форматов и истории редактирования. Его левая верхняя ячейка определяется как пересечение:

самой верхней строки, имеющей какое-то значение, формулу или формат ячейки, и
самого левого столбца, имеющей какое-то значение, формулу или формат ячейки

Аналогично определяется правая нижняя ячейка UsedRange:

Диапазон включает в себя все использованные ячейки

При этом предсказать на основании видимых глазами деталей, какие именно ячейки Excel будет считать измененными, очень сложно, так как даже форматирование соседних ячеек и прочие неявные причины могут привести к изменению UsedRange. Например, измененная высота строки влияет на UsedRange, а измененная ширина столбца – скорее нет. Самый простой пример: если установить цвет ячейки (любой, в том числе «нет заливки»), она считается отформатированной и включается в UsedRange.

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

Ничего не предвещало беды, мы только изменили толщину границы

Так где ловушка?

Такое непредсказуемое поведение UsedRange при импорте с неразмеченного листа в Power Query или Power BI необходимо учитывать. Нам вряд ли помешают пустые строки и столбцы, находящиеся после данных, но пустые строки и столбцы, находящиеся перед данными – настоящая проблема , которую нельзя упускать из виду.

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

Представьте, что у вас есть несколько (или много) файлов Excel, которые заполняют разные пользователи. Файлы имеют одинаковую структуру данных, по крайней мере, в той части, которую вы хотите извлечь. Например, искомые данные всегда находятся в диапазоне E3:H9 (4 столбца и 7 строк). Первые четыре столбца и первые две строки – пустые, не содержат значений (как на рисунках выше).

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

Выбрать конкретный лист,
Оставить только столбцы с пятого по восьмой (E:H),
Удалить первые две строки (так как данные начинаются с третьей строки),
Оставить первые 7 строк в получившейся таблице.

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

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

Соответственно, если Power Query загрузит лист начиная со столбца A, то нужно удалять четыре первых столбца – они пустые и лишние. Если же в первых 4 столбцах листа нет данных или форматирования (то есть они не попали в UsedRange), они не загрузятся в Power Query. Первым загруженным окажется пятый столбец E, с которого начинается нужный диапазон данных. Но тогда четыре первых столбца нельзя удалять !

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

Как видите, на листе Sample4 импортируемый диапазон начинается в ячейке А1 – это четыре лишних пустых столбца. Если при разворачивании столбца [Data] мы оставим только 4 первых столбца (как нам предложит Power Query по умолчанию), данные с листа Sample4 полностью потеряются:

Куда делись данные с четвертого листа?

Если попросить Power Query загрузить все заголовки столбцов и развернуть таблицы полностью, то мы увидим, куда исчезли наши данные:

Данные совсем не там, где ожидалось их увидеть!

Можно заметить, что последний блок не только «уехал» вправо, но и расстояние между блоками непредсказуемо. Ну и как теперь объяснить Power Query, что диапазон, имеющий точную позицию на листе, может оказаться где угодно?

Резюме

Теперь вы можете представить глубину проблемы. Хорошо, если у нас есть какие-то признаки, по которым мы можем опознать левую верхнюю ячейку нужного диапазона. Однако если ее значение не фиксировано и может быть любым (например, изменяющаяся дата или имя менеджера), задача в рамках Power Query становится почти невыполнимой.

В любом случае, решить ее средствами Power Query на данный момент очень сложно. Даже если написать функцию, проверяющую первые столбцы/строки на пустоты и удаляющие их, пока не наткнемся на заполненную строку, мы не избавлены от ситуации, когда ненужные нам строки или столбцы на самом деле не пустые и содержат какие-то значения: тогда мы не сможем удалить строки/столбцы по признаку пустоты. Каким-то способом заставить Power Query получать данные, начиная с ячейки А1, на данный момент невозможно.

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

Относительно практичный способ для предварительной обработки неразмеченных листов – вручную или посредством макроса присвоение имен нужным диапазонам. Это может быть сделано несколькими строками кода VBA, но подходит уже не для всех, и далеко не всегда возможно (что если нужный файл недоступен для редактирования?).

Еще одним трюком может стать обязательное размещение в ячейке А1 хоть какой-то информации или формата: можно написать маленький макрос, проверяющий адрес UsedRange и при необходимости маркирующий ячейку А1 как «использованную». Но это тоже «костыль», которому не место в таком серьезном инструменте, как Power Query или Power BI.

Я считаю использование UsedRange для определения диапазона импорта ошибочным решением. Power Query не различает форматы ячеек, и смысла в UsedRange никакого нет.

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

PS. Если вы хотите поднять свои знания о Power Query для Excel и Power BI, и научиться применять этот инструмент правильно, очень рекомендую следующие книги:

Читайте также: