Как переделать столбцы в строки с Power Query в Excel

Проблема не в том, что у нас нехватка данных, проблема в том, что у нас их слишком много; что просто уже сложно найти суть в этих данных. Вам нужен способ переорганизации и очистки ваших данных, чтобы сделать их полезными. Microsoft Excel это очень удобный инструмент, но вам всё равно нужно почистить данные, прежде чем их можно будет анализировать и просматривать.

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

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

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

Строим дашборд в Excel на базе Power Query и Power Pivot

Стало ненужным проставлять единички на пересечении столбцов «Навык» и строк «Статья». Функциональность возросла благодаря полю отбора и сортировки по наименованию и количеству (D). Появились дополнительные фильтры в «Поля сводной диаграммы» (Рис. 3, E):

Исходные данные для план-фактного анализа

Изучим возможности применения надстройки Power Query для анализа «план-факт» деятельности торгово-развлекательного центра (ТРЦ).

Для анализа «план-факт» будем использовать следующие данные:

1) планируемые величины статей доходов и расходов (бюджет доходов и расходов БДР);

2) фактические величины статей доходов и расходов (Отчет по доходам и расходам).

Аналитика статей доходов и расходов приведена в табл. 1 в разрезе бизнес-единиц и направлений их деятельности.

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

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

Еще одно значительное преимущество Power Query в том, что таблицы (модели данных), сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия надстройки похож на связанные таблицы Excel.

1. Можно использовать различные форматы исходных данных: xml, csv, xls, doc.

2. Можно загружать данные в таблицу Excel из различных источников:

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

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

Эти возможности используются также в надстройке Excel Power BI[3]., а также в надстройке Power Query Add-In, доступной для предыдущих версий Excel.

Основные этапы подключения и преобразования данных в Excel посредством Power Query представлены на схеме.

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

Почему Power BI и Excel вызывают такие споры? Одна из причин заключается в том, что владение Microsoft Excel было стандартным требованием для профессионалов на протяжении десятилетий. Фактически, это обычное условие для тех, кто претендует на вакансии с шестизначными цифрами.

Пошаговое руководство по вашему первому скрипту Microsoft Power Query — gadgetshelp,com

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

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

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

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

Прежде всего избавимся от четырёх верхних строк, которые не представляют для нас никакой ценности. Для этого, на вкладке Главная выбираем Сократить строки -> Удалить строки -> Удаление верхних строк . Далее, в появившемся окне указываем количество строк, которые необходимо удалить и жмём ОК.

Теперь нам нужно разделить данные по столбцам. Опять же, на вкладке Главная выбираем Разделить столбец -> По разделителю и в появившемся окне в качестве разделителя указываем табуляцию, после чего жмём ОК.

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

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

Чтобы превратить эти данные в числовые выделяем столбец Сумма и на вкладке Главная изменяем тип данных на Целое число.

Еще одно значительное преимущество Power Query в том, что таблицы (модели данных), сформированные в этой надстройке, менее требовательны к ресурсам ПК за счет формирования таблиц на ссылках — принцип действия надстройки похож на связанные таблицы Excel.

Анализ «план-факт» с помощью надстройки Power Query Excel на примере ТРЦ

• загрузка данных в таблицу с указанием листа (новый либо существующий);
• только создание соединения — в случае если мы планируем использовать этот запрос для объединения с другими запросами;
• добавление в модель данных — если мы хотим загрузить полученные данные прямо в модель данных Power Pivot.

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