powerpivot и power query в чем разница
Надстройки Power Query и Power Pivot
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В Excel 2016 надстройка уже встроена по умолчанию, на вкладке Данные – раздел «Скачать и преобразовать».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Возможности Power Query
Операции по преобразованию данных выполняются в окне редактора запросов.
В редакторе Power Query пользователь работает с данными – выполняет преобразования, а его действия автоматически записываются в виде шагов запроса.
Как скачать Power Query
Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года:
Надстройка Power Pivot
Power Pivot — это надстройка Excel, в которой можно выполнять улучшенные вычисления для сводных таблиц с помощью DAX-формул. Power Pivot значительно расширяет функционал сводных таблиц и позволяет работать с большими объемами данных – в десятки миллионов строк.
Как работает
Возможности Power Pivot
Кстати, в Power Pivot есть свои инструменты для импорта данных, но из небольшого числа источников – некоторые базы данных, файлы Excel и текстовые и др. Эти инструменты можно найти на вкладке Главная. Так как доступных источников данных не очень много, то, как правило, в качестве источника для него выступает Power Query, функционал которого по подключению намного больше.
Увидеть все загруженные данные можно в нижней части окна Power Pivot – ярлычки с названиями таблиц находятся там же, где названия листов в «обычном» Excel.
Совместное использование Power Query и Power Pivot
Power Query (получить & transform) и Power Pivot дополняют друг друга. Для импорта данных рекомендуется использовать Power Query.Power Pivot отлично подходит для моделирования импортированных данных. Используйте оба этих функции для наглядного Excel, чтобы изучать и визуализировать их в сводных и сводных Power BI
Коротко говоря, с помощью Power Query можно получать данные в Excel, как на Excel, так и на Excel данных. Power Pivot добавляет к модели данных дополнительные сведения.
С помощью Power Query можно находить источники данных, выполнять соединения, а затем формировать данные (например, удалить столбец, изменить тип данных или объединить таблицы) нужным образом. Затем вы можете загрузить запрос в Excel для создания диаграмм и отчетов. Power Query можно использовать в четыре этапа:
Подключение Импорт и подключение к данным в облаке, в службе или локально.
Преобразования Формировать данные в нужном виде, а исходный источник остается без изменений.
Объединить Дополнительные фигуры данных путем их интеграции из нескольких источников для получения уникального представления данных.
Нагрузки Выполните запрос и сохраните его на компьютере или в модели данных.
Это можно обдумать так. Вазы начинаются с висяки, которая превратит одну фигуру в что-то удобное и красивое. Данные одинаковы. Ее необходимо сформать в таблицу, которая подходит для ваших потребностей и которая позволяет использовать привлекательные отчеты и панели мониторинга.
Если данные помещаются в Excel данных, их можно усовершенствовать, выполнив аналитику в Power Pivot. Вы можете сделать следующее:
Обновление модели данных не поддерживается в SharePoint Online или SharePoint локально.
Модель данных, созданную с Excel Power Query, нельзя импортировать в таблильную модель в SQL Server Data Tools.
Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel
Термины «Power Query», «Power Pivot», «Power BI» и прочие «пауэры» все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.
Давайте проясним ситуацию.
Power Query
Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:
В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :
Возможности этих вариантов совершенно идентичны.
Основное окно Power Query обычно выглядит примерно так:
Power Pivot
Общие принципы работы в Power Pivot следующие:
Главное окно Power Pivot выглядит примерно так:
А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:
У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:
К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.
Power Maps
Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).
Ключевые особенности Power Map:
Power View
Выглядеть это будет примерно так:
На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.
Power BI
В Power BI Desktop можно:
Выглядит это примерно так:
На iPhone, например, созданный выше отчет выглядит так:
Power Query. Формулы М. Группировка, Пивот, Анпивот, Сортировка
Все уроки Продвинутого курса Power Query на этой странице и в этом плейлисте.
Описание
В этом уроке мы изучим функции, которые позволят нам выполнять операции Группировки, Пивот, Анпивот и Сортировки.
В уроках модуля Основы мы выполняли эти операции при помощи пользовательского интерфейса, но понимание функций позволит нам решать более сложные задачи за меньшее количество шагов.
Группировка — это получение уникальных значений из одного столбца или получение уникальных наборов значений из нескольких столбцов с каким-то агрегированием, например, с суммой или со счетом.
Пивот — это преобразование таблицы из вертикальной в горизонтальную. В таблице будет меньше строк. Какой-то из столбцов таблицы превратиться в заголовки новых столбцов, а какой-то из столбцов таблицы превратится в значения этих столбцов. Значения тоже могут быть агрегированы.
Анпивот — это обратная операция для пивота. Это означается, что названия определенных столбцов превратятся в один столбец, а значения из этих столбцов попадут в другой столбец. При этом у таблицы станет больше строк.
Сортировка — это настройка порядка столбцов. Столбцы можно упорядочить по возрастанию или убыванию.
В этом уроке мы изучим/повторим:
Посчитать количество строк
Чтобы посчитать количество строк можно воспользоваться функцией Table.RowCount.
Посчитать количество строк с помощью Table.Group:
Выполнить группировку
Выполним группировку с агрегированием. Получим из левой таблицы правую.
Группировка с параметром GroupKind.Local
Мы хотим из левой таблицы получить правую. Группировка будет
Сортировка таблицы
Сортировка таблицы — это настройка порядка строк. В данном примере мы сортируем таблицу в следующем порядке:
Фильтрация
Фильтрация — это отбор строк. В данном примере мы выбираем строки, где поле «Сотрудник» = «Иванов И. И.»
Анпивот и Анпивот других столбцов
Анпивот — это превращение горизонтальной широкой таблицы в узкую вертикальную.
Была такая таблица:
Решение с помощью Table.Unpivot:
Решение с помощью Table.UnpivotOtherColumns:
Пивот
Пивот — это операция обратная анпивоту, т. е. мы наоборот из узкой таблицы сделаем широкую.
План-факт анализ в сводной таблице с Power Pivot и Power Query
Постановка задачи
В исходных данных имеем две таблицы. Скромную в дизайне, большую по размеру, но удобную в работе таблицу с фактическими значениями продаж, выгруженную из какой-нибудь учетной системы:
И «красивую» таблицу с плановыми помесячными показателями от руководства:
Задача: каким-то образом объединить обе таблицы в одну, чтобы наглядно отобразить выполнение плана по каждому товару, региону, месяцу, кварталу и т.д.
Необходимая оговорка
Можно, конечно, не напрягаться, и решать это дело привычным образом «в лоб». Т.е. с помощью 144 функций СУММЕСЛИМН (SUMIFS) вычислять суммарные продажи по каждому месяцу, товару и городу, а потом с помощью еще 144 формул вручную считать процент выполнения плана.
Потом мысленно взвыть, когда шеф скажет, что хотел видеть динамику по кварталам, а не по месяцам. И лучше в рублях, а не в процентах. И города лучше расположить по столбцам, а месяцы по строчкам. И не ной, у тебя вся ночь впереди, к утру чтоб было готово.
И в нашем примере всего 3 города и 4 товара. А если будет больше?
Что мы будем делать
Думаю, никто не будет спорить, что самым удобным, гибким и мощным инструментом для анализа данных в Microsoft Excel являются сводные таблицы. Так что, в идеале, надо бы свести решение нашей задачи именно к ним.
Но как объединить в одной сводной две наших исходных таблицы? Плоскую таблицу продаж по дням и трехмерную таблицу плановых значений с детализацией по месяцам? Тут нам помогут 2 мастхэв надстройки для Excel:
Шаг 1. Добавляем соединительные таблицы-справочники
Шаг 2. Превращаем все таблицы в «умные» и даём им имена
Для загрузки таблиц в Power Pivot они должны быть «умными» (динамическими). Для этого с каждой таблицей проделываем следующее:
Я назвал наши таблицы, соответственно:
Шаг 3. Грузим первые 4 таблицы в Power Pivot
Теперь по очереди для каждой из первых четырёх таблиц, установив в неё активную ячейку, жмём на кнопку Добавить в модель данных (Add to Data Model) :
В итоге все наши таблички должны загрузиться в открывшееся окно Power Pivot на отдельные вкладки:
Шаг 4. Доводим до ума таблицу План
Прежде, чем загрузить в Модель данных Power Pivot таблицу с плановыми значениями, её нужно сначала подрихтовать: убрать в ней пустые строки и итоги, развернуть в плоскую, заполнить пустые ячейки в первом столбце городами и т.д. Проще и легче всего это проделать с помощью надстройки Power Query.
Сначала загрузим таблицу с планами в редактор запросов Power Query, используя кнопку Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или на вкладке Power Query (если у вас старая версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):
Затем в открывшемся окне Power Query делаем следующее:
После этого наша последняя таблица таблПлан должна загрузиться в окошко Power Pivot.
Шаг 5. Связываем таблицы
Связь делается очень просто: хватаем мышью столбец в одной из соединительных таблиц (таблТовары, таблГеография, таблКалендарь), тянем и бросаем на соответствующий столбец в таблицах таблПродажи и таблПлан2:
Шаг 6. Строим сводную
Теперь можно знакомым уже образом перетащить мышью нужные нам поля из таблиц в области сводной таблицы.
Главные принципы здесь такие:
Например, можно накидать так:
В итоге должно получиться что-то уже очень похожее на то, что нам требуется:
Шаг 7. Добавляем меры для вычислений
Сумма по полю Выручка := SUM( таблПродажи[Выручка] )
Повторяем процесс еще два раза:
Добавленные меры появятся в правой панели сводной таблицы с характерным значком:
Теперь их можно смело закидывать мышкой в нашу сводную и выполнять план-факт анализ в любых разрезах за считанные секунды:
Возможные проблемы и их решения
В процессе реализации вы можете нарваться на несколько типичных «граблей»: