Power query excel преобразование запроса в функцию

Обновлено: 06.01.2025

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

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

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

Создание пользовательской функции из ссылки на таблицу

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

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

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

Создайте новый параметр с параметром name File. Используйте запрос Sample File в качестве текущего значения, как показано на следующем рисунке.

Рекомендуется ознакомиться со статьей о параметрах , чтобы лучше понять, как создавать параметры и управлять ими в Power Query.

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

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

Щелкните правой кнопкой мыши параметр файл на панели запросы . Выберите параметр ссылка .

Переименуйте созданный запрос из параметра файла (2) , чтобы преобразовать пример файла.

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

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

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

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

Применение преобразований к образцу запроса

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

Первое преобразование, которое должно произойти в этом запросе, — это тот, который будет интерпретировать двоичный код. Можно щелкнуть правой кнопкой мыши двоичный файл на панели предварительного просмотра и выбрать параметр CSV для интерпретации двоичного файла в CSV-файле.

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

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

Удаление первых четырех строк — Это действие приведет к отсечениям строк, которые считаются частью заголовка файла.

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

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

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

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

Функция файла преобразования основана на шагах, выполненных в запросе на выполнение примера файла преобразования . Однако при попытке вручную изменить код для функции преобразования файла вы будете сталкивается с предупреждением, которое считывает The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Вызов пользовательской функции в качестве нового столбца

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

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

Дополнительные сведения о выборе или удалении столбцов из таблицы см. в разделе Выбор или удаление столбцов.

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

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

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

Рекомендуется также прочитать статью Общие сведения о объединении файлов и объединить CSV-файлы , чтобы понять, как работает объединение файлов в Power Query и роль, которую играют пользовательские функции.

Добавить новый параметр в существующую пользовательскую функцию

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

Чтобы это требование было выполнено, создайте новый параметр с названием Marketing с типом данных Text. Для текущего значения введите значение Панама.

С помощью этого нового параметра выберите запрос преобразовать образец файла и отфильтруйте поле Country (страна ), используя значение параметра " рыночный ".

Дополнительные сведения о фильтрации столбцов по значениям см. в разделе значения фильтров.

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

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

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

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

Создание пользовательской функции из многократно используемого фрагмента логики

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

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

Начните с параметра со значением, которое служит примером. В этом случае это будет значение PTY-CM1090-слабое.

Из этого параметра вы создадите новый запрос, в котором будут применяться необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-слабый на несколько компонентов:

Ниже показан код M для этого набора преобразований.

Дополнительные сведения о языке формул Power Query M см. в разделе язык формул Power Query m .

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

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

В нашем менеджерском труде есть много хорошего и не очень. Но ничто не портит нам жизнь так, как многочисленные эксельки, в которых постоянно нужно что-то ВПР-ить, СУММ-ировать, СУММПРОИЗВ-одить и заниматься прочими рутинными операциями, отнимающими кучу времени и сил. Есть инструмент, позволяющий если не убрать, то минимизировать рутину везде, где есть паттерны данных. Итак, вашему вниманию предлагается чуть более глубокое погружение в принципы работы PowerQuery.

Сразу же хочу сориентировать по поводу того, что уже есть на Хабре по данной теме:

— Погружение в функции языка «M» на примере локализации списка месяцев. Автор кратко коснулся мощнейшей функции List.Accumulate описание которой заслуживает отдельного материала на Хабре. В качестве тизера к такой публикации, могу сказать, что List.Accumulate принимает на вход абсолютно любой тип аргументов.

Что такое Power Query (PQ)

Дабы не повторять уже сказанное в материалах, ссылки на которые указаны выше, буду краток: Power Query — это инструмент для преобразования формы представления любых логически организованных данных. То есть на входе может быть любая каша, но в которой есть хоть какая-то логика. А на выходе — таблица. По мнению некоторых экспертов, PQ является ETL инструментом. То есть служит для Extract – извлечения, Transform – преобразования и Load – выгрузки данных. По опыту, данная работа занимает до 60% рабочего времени, например, менеджера по контекстной рекламе и до 90% времени аналитика. Важная особенность данной работы состоит в том, что её, как правило, нужно повторять изо дня в день, неделю за неделей. Бесконечно переносить данные, копипастить, вычислять. Для особо тяжёлых случаев программисты пишут решение, в котором всё это делается автоматически. Проблема состоит в том, что решения дороги, а программисты почти всегда заняты более насущными вопросами, чем автоматизация рутинных операций менеджера. PQ позволяет решить указанные трудности:

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

На сегодняшний день Power Query доступен только для MS Excel, работающего под ОС Windows. До Excel 2013 включительно, распространялся в виде подключаемого модуля. Начиная с Excel 2016 стал встроенным.

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

Кнопка «Расширенный редактор»

Как устроен язык «M»

Важно всегда иметь в виду, что конструкция let …. in … является просто “syntactic sugar”. let по факту представляет из себя запись с множеством пар вида «имя=значение», а in это просто ссылка на некоторое значение в этой записи (причём не обязательно последнее). Сложновато. Но давайте попробуем разобраться. В языке «M» есть следующие типы значений:

Абсолютна идентична этой:

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

A1=A2+A3
A2=A3+2
A3=5

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

Коротко о секциях

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

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 умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.

Таблицы Excel

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

Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.

Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.

Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.

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

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

Именованный диапазон Excel

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

Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

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

Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.

Динамический именованный диапазон Excel

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

Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:

Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.

Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.

Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.

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

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

Название запроса не подхватывается, поэтому придется изменить самостоятельно.

Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.

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

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

Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

Консолидация данных из разных таблиц Excel

Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

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

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

Затем в строке формул вводим знакомую команду

Power Query показывает все таблицы в текущей книге.

Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

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

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

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

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

Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

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

Подчеркивание удаляется из названия.

Поиск и замена здесь работает так же, как и в обычном Excel.

Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.

Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
Главная → Закрыть и загрузить.

Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.

Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

Снова выгружаем таблицу в Excel и на этот раз все в порядке.

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

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

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

А вот, как это выглядит при использовании Power Query.

Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).

На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.

Вот за это мы так любим Power Query.

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