Лучшие 25 приемов Power Query

Есть одна вещь, которая волнует всех — как сэкономить больше времени и работать с умом.

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

POWER QUERY — это то, что может изменить вашу жизнь. Сегодня в этой статье я поделюсь с вами некоторыми удивительными приемами Power Query, которые могут начать использовать прямо сейчас.

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

Прежде чем мы перейдем к приемам, нужно прояснить пару моментов.

Почему я должен использовать Power Query?

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

1. Самый простой способ преобразовать ваши данные

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

2. Power Query в реальном времени

Это вторая важная причина, по которой Power Query выполняется в реальном времени, как разовая настройка.

Напишите запрос один раз, и вы можете обновлять его каждый раз, когда происходит изменение данных, также вы можете определить время автоматического обновления (Совет № 26).

3. Нужно просто несколько кликов

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

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

Как установить Power Query — Шаги

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

И если вы один из тех пользователей Excel, у которых нет надстройки с Power Query, используйте эти шаги для ее установки.

Для Excel 2016 или Office 365:

Если вы используете версию Excel 365 или Excel 2016, она уже находится на вкладке «Данные» — «Скачать и преобразовать».

Для версий 2013 и 2010:

Прежде всего, загрузите надстройку отсюда (официальный сайт Microsoft). Как только вы загрузите файл, откройте его и следуйте инструкциям. После этого автоматически откроется вкладка «Power Query» на ленте Excel.
Если вкладка «POWER QUERY» не появляется, вам не о чем беспокоиться.

Вы можете добавить ее, используя опцию Надстройки. COM.

  • Перейдите на вкладку «Файл» ➜ «Параметры» ➜ «Надстройки».
  • В опциях «Надстройки» выберите «Надстройки COM» и нажмите.
  • После этого отметьте галочкой «Microsoft Power Query for Excel».
  • В конце нажмите ОК.

Все! Теперь у вас есть новая вкладка на ленте с названием «Power Query».

Откройте Power Query и загрузите в его данные

У вас есть разные способы добавить данные в редактор Power Query. Что ж, если у вас есть данные на рабочем листе, вы можете вставить их оттуда.

  • Перейдите на вкладку «Данные» ➜ «Скачать и преобразовать» ➜ Из таблицы.
  • Нажмите OK, чтобы преобразовать эту таблицу в таблицу Excel.
  • И сразу после этого вы получите эту таблицу в редакторе Power Query, как показано ниже.

Лучшие 25 приемов Power Query для экономии времени в повседневной работе

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

1. Заменить значения

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

С помощью Power Query мы можем создать запрос и заменить эти важные значения очень быстро. В приведенном ниже списке я хочу заменить имя «Алена» на «Алёна».

Давайте сделаем:

  • Прежде всего, загрузите список в редактор Power Query.
  • После этого выхода на вкладку «Преобразование» и нажмите «Замена значений».
  • Теперь в поле «Значение для поиска» введите «Алена», а в поле «Заменить на» введите «Алёна» и после этого нажмите ОК.
  • После того, как вы нажмете OK, все значения будут заменены новыми. Теперь можно нажать «Закрыть и загрузить», чтобы загрузить данные в таблицу.

А вот и лучшая часть: Вы только что создали запрос в режиме реального времени. При повторном обновлении запроса он заменит все вновь введенные значения.

2. Сортировка — по возрастанию и по убыванию

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

  • Прежде всего, загрузите данные в редактор
  • В редакторе Power Query у вас есть две кнопки сортировки (по возрастанию и по убыванию).
  • Нажмите на любую из этих кнопок, чтобы отсортировать.

  • В конце нажмите« Закрыть и загрузить », чтобы загрузить данные в таблицу.

Вы, наверное, удивлены:« Зачем мне использовать Power Query, если я могу использовать обычную сортировку на листе? »

Как я уже писал, Power Query — работает в реальном времени. Вы можете создать запрос автообновления (Совет № 26), который будет обновляться через определенное время и автоматически сортировать ваши данные.

3. Удалить столбцы

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

Но с Power Query вы можете создать запрос.

Вот шаги:

  • Прежде всего, откройте данные в редакторе
  • После этого выбора столбца или нескольких столбцов
  • Теперь щелкните правой кнопкой мыши и выберите «Удалить».
  • В конце нажмите «Закрыть и загрузить» , чтобы загрузить данные.

Совет. Также имеется опция «Удалить другие столбцы», в которой можно все невыбранные столбцы.

4. Разделить столбец

Точно так же как опция как «Текст по столбцам» есть в запросе: «Разделить столбец». Сейчас я расскажу, как это работает.

В приведенном ниже списке у вас есть имя и фамилия с дефисом между ними.

Теперь вам нужно разделить их на две колонки. Используйте эти шаги:

  • Прежде всего, откройте список в редакторе
  • После этого выбора столбца и перехода на вкладку «Преобразование» ➜ «Разделить столбец» ➜ «по разделителю».
  • Выберите «Пользовательский» из выпадающего списка и введите «-» в него.
  • Теперь у вас есть три варианта, как разбить столбец.
  1. Самый левый разделитель
  2. Самый правый разделитель
  3. По каждому вхождению разделителя

«У нас есть только один разделитель в ячейке, все три будут работать одинаково, но если у вас более одного» разделителя, вы можете выбрать нужный.

  • В конце нажмите OK и нажмите «Закрыть и загрузить», чтобы загрузить данные.

5. Переименовать столбец

Вы можете просто переименовать столбец, щелкнув правой кнопкой мыши, а затем нажмите «Переименовать».

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

6. Дубликат столбца

В Power Query есть простой способ создания дубликат столбца.

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

7. Объединить столбец

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

Помните, мы разделили список сотрудников (Совет № 4). Теперь давайте объединим его, используя пробел.

Следуй этим шагам:

  • Как только вы добавите данные в редактор, выберите оба столбца.
  • После этого щелкните по ним правой кнопкой мыши и выберите «Объединить столбцы».
  • Теперь в окне слияния столбцов выберите разделитель из выпадающего списка (здесь мы используем пробел) и добавьте имя для нового объединенного столбца.
  • Нажмите OK и загрузите данные в таблицу.

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

8. Транспонировать столбец или строку

В Power Query транспонирование — это проще простого. Да, всего один клик.

  • Как только вы загрузите данные в редактор, вам просто нужно выбрать столбец (столбцы) или строку (и).
  • Перейдите на вкладку «Преобразование» ➜ Таблица ➜ «Транспонировать».

И все.

9. Заменить/удалить ошибки

Это крутая вещь. Обычно для замены или удаления ошибок в Excel вы можете использовать опцию поиска и замены кода VBA. Но в Power Query все намного проще.

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

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

  • Заменить ошибки
  • Удалить ошибки

10. Изменить тип данных

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

В приведенном выше у нас есть столбец дат, но мы видим просто числа. Чтобы преобразовать их в дату, вы можете использовать Power Query. Это просто.

  • Прежде всего, откройте данные в редакторе
  • После этого выбора столбец и выхода на вкладку Преание.
  • Теперь из типа данных выбора «Дата».

Вот, что получилось.

Совет: в большинстве случаев Power Query автоматически определяет тип данных, но если это не так, вы можете изменить его, как в приведенном выше примере.

11. Добавить столбец из примеров

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

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

Вместо использования формулы или другого параметра в Power Query мы можем использовать параметр «Добавить столбец из примеров».

Вот как это сделать:

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

Все! Столбец заполнен.

12. Отменить таблицу

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

  • Прежде всего, выбрать данные кросс- таблицы и перейти на вкладку «Данные».
  • На внутренней стороне интерфейса к Скачать & Преобразовать → Из таблицы.
  • Когда вы щелкнете по нему, он преобразует ваши данные кросс-таблицы в таблицу Excel (если это уже не так ).
  • Данные мгновенно загрузятся в редактор Power Query.
  • Отсюда нам нужно выбрать все столбцы, которые мы хотим отключить.
  • Для этого выберите столбец Янв, нажмите и удерживайте расположение Shift и выберите столбец Дек.
  • После этого щелкните правой кнопкой мыши и выберите «Отменить свертывание столбцов».
  • Теперь все значения 12-ти ст олбцов разделены на два столбца. В одном — месяц, в другом — сумма.
  • Последнее, что вам нужно сделать, это переименовать столбцы. Для этого щелкните правой кнопкой мыши по столбцу и переименуйте их.
  • В конце нажмите «Закрыть и загрузить».

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

13. Изменить регистр

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

  • нижний регистр
  • ВЕРХНИЙ РЕГИСТР
  • Каждое Слово С Прописной

Вы можете сделать это, щелкнув правой кнопкой мыши по столбцу и выбрать любой из трех указанных вариантов. Или перейдите на вкладку «Преобразование» ➜ «Столбец Текст» ➜ «Формат».

14. Усечь и Очистить

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

Шаги просты:

  • щелкните правой кнопкой мыши по столбцу или выберите все столбцы, если у вас несколько столбцов.
  • Переключите опции преобразования и выберите любые из опций:
  1. Усечь : для удаления лишних пробелов из ячеек.
  2. Очистить : для удаления непечатаемых символов из ячеек.

15 . Добавить префикс/суффикс

Итак, у вас есть список значений, и в этот список вы хотите добавить префикс/суффикс в каждую ячейку. В Excel вы можете использовать функцию СЦЕПИТЬ, но в Power Query есть более простой способ.

  • Прежде всего, выберите столбец, который нужно добавить префикс/суффикс.
  • Затем перейдите на вкладку «Преобразование» ➜ Столбец Текст ➜ Формат ➜ Добавить префикс/Добавить суффикс.
  • Как только вы нажмете одну из опций откроется диалоговое окно для ввода текста.
  • После ввода текста нажмите ОК.

Опция работает одинаково для чисел, текста и дат.

16. Извлечь значения

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

Просто посмотрите на варианты, которые у вас есть.

Эти опции закрывают все основные задачи, которые вы привыкли решать с помощью формул.

17. Только дата или время

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

Для этого в Power Query вам нужен всего лишь один клик, вот шаги.

  • Выберите столбец, где у вас есть дата и время вместе.
  • Если вам нужна Дата: щелкните правой кнопкой мыши ➜ Преобразование ➜ Только дата;
  • Если хотите Время: щелкните правой кнопкой мыши ➜ Преобразование ➜ Только время.

18. Объедините дату и время

Теперь у вас есть отдельно дата и время. Значит, пора узнать, как их объединить.

Это довольно просто.

  • Прежде всего, загрузите ваши данные в редактор.
  • После выбора обоих столбцов (Дата и время) и продолжить на вкладке преобразования.
  • Теперь из группы Столбец «Дата и время» выход к «Дата» и нажмите «Объединить дату и время».

Теперь у вас есть новый столбец с объединенными данными.

19. Округление чисел

Есть функции для округления чисел в Excel, но также есть и Power Query.

Вот варианты:

  1. Округление с уменьшением.
  2. Округление: Вы можете выбрать, до какого знака после запятой округлить.

Шаги:

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

Примечание . Когда вы выбираете опцию «Округление», вам нужно количество десятичных знаков для округления.

20. Расчеты

В Power Query есть варианты, которые вы можете использовать для выполнения расчетов. Посмотрите на приведенный ниже список.

  • Стандартный
  • Статистика
  • Научный
  • Тригонометрические
  • Округление
  • Информация

Вы можете найти все эти опции на вкладке Преобразование.

Для выполнения любого из этих расчетов вам нужно выбрать столбец и выбрать опцию.

21. Группировка

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

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

  • На вкладке «Преобразование» нажмите «Группировать по», откроется диалоговое окно.
  • Теперь в этом диалоговом окне отображается выбор столбца, который вы хотите сгруппировать.

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

  • В конце нажмите ОК.

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

22. Удалить знак отрицания

Есть много методов удаления отрицательного знака, и один из них — это Power Query.

Щелкните правой кнопкой мыши по столбцу и в направлении Преобразование, а нажмите на Абсолютное значение.

23. Добавить настраиваемый столбец

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

  • Прежде всего, добавьте на вкладку Добавление столбца ➜ Настраиваемый столбец.
  • Здесь у вас появится диалоговое окно для создания формулы для использования в столбце (сейчас я хочу умножить количество на цену). Ведите формулу в поле формулы.

Совет. Когда вы вводите формулу, в диалоговом окне появляется сообщение, если в формуле есть какая-то ошибка.

24. Автообновление запроса

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

Вот шаги:

  • На вкладке «Данные» нажмите «Существующие подключения»
  • Теперь щелкните правой кнопкой мыши по нужному запросу, далее Изменить свойства подключения.
  • Через минуты в открывшемся диалоговом окне.
  • В конце нажмите ОК.

25. Создайте сводную таблицу из нескольких рабочих книг.

Иногда мы получаем или собираем данные из разных книг. И в этом случае создание сводной таблицы потребует усилий для объединения этих рабочих книг в одну.

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

У меня есть четыре книги с данными о продажах для разных отделений.

Убедитесь, что все эти файлы в одной папке.

Шаг 1 — Объедините файлы с помощью Power Query

Прежде всего, нам нужно объединить все файлы в одну таблицу с Power Query.

  • Перейдите на вкладку «Данные» ➜ «Скачать & Преобразовать» ➜ «Создать запрос» ➜ «Из файла» ➜ «Из папки».
  • Теперь в окне выбора папки нажмите «Обзор» и выберите папку, в которой находятся все файлы.
  • ОК.
  • Вы увидите окно «Объединить файлы».
  • В этом эксклюзивном листе с вашими данными во всех книгах. Важно: убедитесь, что во всех книгах указано одинаковое имя листа!
  • После того, как вы нажмете OK, Power Query отправит все данные из рабочих книг в редакторе.

Шаг 2 — Подготовка данных для сводной таблицы

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

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

Шаг 3 — Вставьте сводную таблицу

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

  • Выберите таблицу и перейдите на вкладку «Вставка» и нажмите кнопку «Сводная таблица».
  • Откроется окно создания сводной таблицы. Нажмите OK, и вы получите новую сводную таблицу в своей книге.

Поздравляю! Вы успешно создали новую сводную таблицу из разных файлов.

Заключение

Вы можете не пользоваться POWER QUERY. Но… Многие приведенные выше советы вдохновляют вас использовать его все больше и больше.

Многие… Многие из приведенных выше советов вдохновляют вас использовать его все больше и больше. /p>

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


Спасибо за большой объем полезной информации. Хотел бы дополнить п.10 «Изменить тип данных». Описанный вами метод преобразования значений в столбцы указаны как число (пиктограмма «1,2,3»). В моем случае исходные данные в формате «дата» PowerQ почему-то воспринял как «текст»! Т.е. все даты стали пятизначным числом но в формате текста. В этом случае пришлось вначале текст преобразовать в число и только потом число преобразовать в дату. Если пытаться текст сразу преобразовать в дату получим Ошибка.


Спасибо за информацию.
Подскажите, в аналогичном примере задания 25, почему-то 2 файла объединяет, а с
Одинаковая структура, но вот почему-то никак не получается объединить больше двух.
Не сталкивались ли с такой проблемой?


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


Дмитрий! Огромное Вам спасибо! Все нужные решения в одной статье, отличная памятка для новичков. Гениально!


Хороший и полезный материал.



Знакомство с Power Query на примерах транспонирования Таблицы Excel

Power Query — это инструмент MS Excel, предназначенный для импорта из самых различных источников и обработки данных. Впервые появился в 2013 году и был доступен в виде специальной надстройки, которую можно скачать с официального сайта Microsoft и установить на Excel 2010-2013. После установки и подключения на ленте Excel появится соответствующая вкладка.

В Excel 2016 Power Query уже встроен в ядро ​​программы. Команды управления запросами находятся во вкладке Данные , в группе Скачать и преобразовать (в английском варианте Get & Transform ).

Далее будем использовать привычное название Power Query.

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

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

После появления Power Query в среде пользователей Excel произошло потрясение, сравнимое появление сводных таблиц. Это не шаг, а прыжок вперед, соответствующий любой аналитик (и обычный пользователь Excel), имеющий дело с большими и обновляемыми данными из разных источников, может ускорить свою работу в десятки раз. Да, в десятки, если не в сотни. Ведь как раньше делался, скажем, отчет? Импортируются данные (из разных источников), очищаются, связываются вместе с помощью формул ВПР, затем делаются необходимые расчеты, все агрегируются с помощью сводных таблиц в краткий отчет. Периодически эти действия нужно повторять, т.к. традиционными методами (без VBA) очень трудно автоматизировать все шаги. Сегодня этому кошмару пришел конец. В Power Query достаточно один раз все настроить и выполнить все операции импорта, обработки и выгрузки данных повторяться нажатием одной кнопкой обновления.

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

Вкратце алгоритм работы Power Query таков:

1. импорт данных из выбранных источников данных

2. обработка полученных данных

3. выгрузка

Список источников довольно разнообразный: от текстовых файлов до внешних баз данных и интернета. Можно легко присоединиться к данным внутри самого MS Excel.

На этапе обработки также производят операции по очистке, связыванию, группировке, математическому преобразованию и т.д. Специфика работы именно с такими, плохо организованными и неочищенными данными, объясняет набор инструментов Power Query. Частично они повторяют то, что есть в Excel, но есть и новые, которые значительно расширяют привычный функционал Эксель. Важнейшей особенности работы в Power Query — это то, что все шаги записываются. Это дает возможность нажатием одной кнопки повторить все операции. Объединяя возможность подключения к данным внутри Excel и новые методы их обработки, мы получаем дополнительные инструменты, которые делают работу в Excel удобнее и быстрее.

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

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

В целом это не проблема, т.к. в Excel существует минимум два способа транспонирования.

Первый и самый быстрый способ — используйте Специальной вставкой , поставив галочку напротив транспонировать .

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

Второй способ транспонирования — используйте функции ТРАНСП . Это формула массива, поэтому для ее вставки нужно вначале указать точный диапазон и использовать с помощью комбинации Ctrl + Shift + Enter .

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

Итого получается, что мы не можем сделать динамическое транспонирование в изменяющемся диапазоне. Так да не так. С появлением Power Query задача решается быстро, без шума и пыли.

Транспонирование таблицы результатов Power Query

Первым делом нужно сделать запрос на источник данных. Нас интересуют данные из этой же книги Excel. Power Query не видит обычных ячеек, а только именованные диапазоны и Таблицы Excel. Как правило, используйте Таблицы Excel. Для преобразования обычного диапазона в таблицу рекомендую горячую комбинацию клавиш Ctrl + T .

Теперь активируем любую ячейку Таблицы с данными и нажимаем кнопку Данные — Скачать и преобразовать — Из таблицы .

Открывается окно редактирования Power Query.

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

1. Инструменты редактирования — лента, на которой находятся команды Power Query.

2. Строка формул — здесь записывается код языка М для выделенного в данный момент шага обработки.

3. Запросы — скрываемая панель для навигации между запросами текущих книг.

4. Панель результата — место, где отображается результат обработки данных этапа выделенного шага.

5. Параметры запроса — панель с вызовом запроса (можно проверить) и перечнем созданных шагов, которые также можно редактировать.

Выделив любой из шагов, мы увидим состояние данных на соответствующем этап.

Название запроса лучше всего изменить на более говорящее. Довольно часто в использовать сразу несколько запросов, поэтому в них нужно ориентироваться. Назовем «Транспонирование».

Из предыдущего рисунка видно , что мы еще ничего не сделали, а два шага уже записаны. Как так? Все просто. Первый шаг — это обращение к новому столбцу, а также автоматическое определение типа данных у каждого столбца. Поэтому все в порядке.

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

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

Таблица с данными получит такой вид.

Теперь можно транспонировать. Используем команду Преобразование — Транспонировать .

Таблица мгновенно изменяется.

Сделаем первую строку назад заголовками. Можно через Преобразование — Таблица — использовать первую строку в качестве заголовка через кнопку в верхнем левом углу от таблицы.

Получим конечный результат обработки.

Задача решена. Все шаги преобразования данных записаны и видны справа.

Осталось измененные данные вернуть в Excel с помощью команды Главная — Закрыть — Закрыть и загрузить .

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

Если выбрать Только создать соединение, выгрузки не произойдет. Такой вариант применяют, если требуется дальнейшая обработка или использование этого запроса. Для выгрузки в Excel можно выбрать Новый лист либо указать конкретный диапазон. Если установить галочку Добавить эти сведения в модель данных , то результат запроса даже без выгрузки в Excel можно использовать в модели данных или Power Pivot. Этот вариант позволяет обрабатывать миллионы (миллионы!) Строк, т.к. на обработке данных в памяти требуется гораздо меньше ресурсов. Оставляем все по умолчанию и жмем Загрузить . В процессе выгрузки таблица имеет серенький цвет, а когда выгрузка завершена, становится зелененькой.

Вот и все, дело сделано, мы получили транспонированную таблицу исходных данных.

Самое интересное происходит далее. Если добавить новые данные, то для повторения всех действий достаточно обновить запрос через правую кнопку в панели запросов (см. чуть ниже), либо во вкладке Данные — Подключения — Обновить все .

Добавим в исходную таблицу данные о продажах во втором квартале.

А теперь обновим запрос.

Это просто праздник какой-то! (с).

Обратим внимание, что справа в окне Excel появляется панель для управления существующими запросами.

Их может быть много, но у нас только один. Сразу под названием видно, сколько загружено строк. Здесь же указываются ошибки, если они есть. Это важно для контроля. Если подвести курсор мыши к названию, то откроется окно с кратким описанием запроса и командой управления снизу.

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

Перечислим наиболее часто используемым среди них.

Изменить — команда открытия окна редактирования. Эквивалентно двойному двойию левой кнопки мыши по самому запросу.

Обновить — обновление выбранного запроса (если нужно обновить только один запрос, а не все).

Загрузить в… — изменение места загрузки (в таблицу, модель или создание только соединения)

Дублировать — сделать выбранную панель запроса.

Другие команды не менее важны, но их рассмотрим другой раз.

Запросы книги можно закрыть или снова отобразить с помощью команды Данные — Скачать и преобразовать — Показать запросы .

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

Серия видеоуроков о Power Query.

Поделиться в социальных сетях:
Оцените статью
logicle.ru
Добавить комментарий