Как переделать столбцы в строки с Power Query в Excel
Проблема не в том, что у нас нехватка данных, проблема в том, что у нас их слишком много; что просто уже сложно найти суть в этих данных. Вам нужен способ переорганизации и очистки ваших данных, чтобы сделать их полезными. Microsoft Excel это очень удобный инструмент, но вам всё равно нужно почистить данные, прежде чем их можно будет анализировать и просматривать.
Вот где Power Query вступает в игру. Power Query, также называемый «Подключение и преобразование данных», находится в Excel и автоматизирует процесс очистки данных.
Одним из самых популярных шагов очистки данных является получение данных из столбцов и преобразование их в строки. Поднимите ваши знания Excel на следующий уровень, научившись «отменять свёртывание» данные с помощью Microsoft Power Query. В этом уроке вы узнаете, как использовать Power Query для преобразования столбцов в строки в Excel.
Зачем преобразовывать столбцы в строки?
Прежде чем мы начнём, вам может быть интересно, почему полезно использовать данные, которые расположены в столбцах и преобразовать их в строки.
Ответ немного технический. В принципе, предпочтительнее, если каждая строка данных подобна записи, единственному указателю данных. Данные в столбцах смешиваются по несколькими атрибутами, распределенным в столбцах.
В приведенном ниже примере вы можете увидеть, как выглядят данные в Excel, если преобразовать их из столбцов в строки. С левой стороны, находится набор записей о клиентах и разными типами проектов в столбцах. С правой стороны я отменил свёртывание данных и конвертировал их в формат строки.
Кроме того, если вы пользуетесь сводными таблицами, очень важно, чтобы ваши данные были в строках, а не столбцах, при создании сводной таблицы. Просто данные в столбцах загружаются в сводные таблицы не правильно.
Вкратце: преобразование данных из столбцов в строки облегчает работу. Power Query делает это запросто, в пару кликов. Давайте узнаем, как:
Как быстро преобразовать столбцы в строки в Excel (смотри и учись)
Если вы никогда раньше не использовали Power Query, я настоятельно рекомендую посмотреть этот быстрый двухминутный видеоурок ниже, чтобы посмотреть, как я работаю с ним. Вы узнаете, как преобразовать ваши данные в столбцах в строки (записи) в Excel, чтобы упростить работу с ними.
Давайте также рассмотрим написанную версию этих инструкций, чтобы узнать больше о Power Query и «отмене свёртывания» данных в Excel.
Знакомство с Microsoft Power Query для Excel
Power Query, формально называемый «Подключение и преобразование данных» в Excel 2016, это инструмент с двумя основными функциями:
Power Query — это мощный инструмент, который располагается в Excel. Вместо того, чтобы вручную переупорядочивать данные и повторять этот процесс, Power Query позволяет вам создать набор шагов, которые вы можете применить к данным повторно.
Установка Power Query
Power Query встроен в Excel 2016, но вы можете установить его для более ранних версий Excel в качестве надстройки. К сожалению, Power Query доступен только для Excel в Windows.
Чтобы установить Power Query для Excel в Windows (требуется только для Excel 2013 и Excel 2010, поскольку эта функция встроена в Excel 2016) перейдите на веб-сайт Microsoft, чтобы загрузить и установить надстройку Power Query.
Отправка данных в Power Query
Power Query используется в Excel, но он открывается в новом окне, которое находится поверх Excel.
Чтобы отменить свёртывание данные в Excel, вам сначала необходимо конвертировать данные Excel в таблицу, если они ещë не в этом формате. Для работы с данными, вы можете передать в Power Query таблицу данных.
Если вы хотите следовать со мной в этом уроке, я сделал пример данных в этом файле Excel, который доступен бесплатно.
Преобразование в таблицу
Чтобы преобразовать данные в таблицу, кликните в любом месте таблицы, а затем найдите параметр Форматировать как таблицу, это находится на ленте Excel. Вы можете нажать на любую из миниатюр стилей, чтобы преобразовать ваши обычные ячейки в таблицу с даными.
Передача в Power Query
Как только ваши данные окажутся в табличном формате, перейдите на вкладку Данные на ленте Excel и нажмите кнопку Из таблицы, чтобы отправить данные таблицы в Power Query и преобразовать их.
Это отправляет данные в новое окно, которое открывается поверх Microsoft Excel. Это совершенно новое окно с множеством функций.
Отмена свертывания данных
На этом этапе вы должны увидеть что-то похожее на снимок экрана ниже. Это окно, в котором находятся инструменты, необходимые для отмены (или сброса данных).
Чтобы отменить свёртывание данных в Excel, выделите все столбцы, которые вы хотите сбросить. В принципе, Power Query преобразует каждый из этих столбцов в самостоятельные строки. Для данного здесь примера, я выделил каждый из столбцов с типом проекта:
Как только вы нажмете на Отменить свёртывание столбцов, Excel преобразует ваши данные из столбцов в строки. Каждая строка является самостоятельной записью, готовой перейти в сводную таблицу или подойдёт для таблицы данных.
Эта особенность кажется волшебной. Теперь давайте вернем данные в Microsoft Excel, чтобы начать с ними работать.
Закрыть и загрузить
Окно Power Query имеет множество функций, в которые вы можете погрузиться на других уроках, но на данный момент мы закончили с переворачиванием наших данных. Давайте вернемся и нажмите кнопку Закрыть и Загрузить, чтобы отправить данные обратно в Excel.
Закрыть и загрузить отправляют данные обратно в Excel и помещает их в электронную таблицу. Вы также увидите окно запросов с правой стороны, которое показывает запрос, который мы только что создали, чтобы преобразовать наши данные.
Обновление запроса
А что если ваши исходные данные изменились? Если в исходные данные добавлено больше строк, вам не нужно повторять весь этот процесс, чтобы перевернуть эти строки.
Вместо повторного создания запроса с начала, вам необходимо использовать параметр Обновить данные. На скриншоте ниже, я добавлю совершенно новую строку с новым клиентом, к исходному источнику данных:
Теперь вернемся на вкладку, где загружаются наши не сформированные данные. Когда мы нажимаем внутри таблицы, мы видим меню Параметры запросов, расположенные рядом с вашими данными. Нажмите кнопку Обновить (в правой части меню), чтобы обновить запрос.
Это произведёт возврат к исходной таблице и повторит шаги запроса. Поскольку мы добавили новые строки в нашу исходную таблицу данных, Power Query добавит эти новые строки в запрос и запустит по ним преобразование.
Это одна из моих любимых функций Power Query. Мы добавили данные в исходный исходный файл, но в один клик обновили и преобразовали новые данные.
Повторите и продолжайте дальнейшее обучение по использованию Microsoft Excel
[expert_bq id=»1570″]Но это недокументированная и крайне ограниченная функция, не поддерживаемая Microsoft, которую можно использовать только на свой страх и риск. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Шаг 1: СтартВо-первых, нам нужен Источник. Другими словами, книга Excel с некоторыми данными, загруженными в модель данных PowerPivot. Если вы видите таблицу в окне PowerPivot, это хорошо.
Создание отчета в Power BI и его визуализация. Базовый курс из 10-и уроков
Однако, не желая загружать пользовательский интерфейс MS Excel 2016, для Power Query не стали создавать отдельную вкладку на ленте а втиснули его в качестве отдельной группы с банальным названием Скачать & преобразовать на вкладке Данные (хотя сообщество пользователей MS Excel и продолжает использовать старое имя — Power Query).
