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( таблПродажи[Выручка] )

Повторяем процесс еще два раза:

Добавленные меры появятся в правой панели сводной таблицы с характерным значком:

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

Возможные проблемы и их решения

В процессе реализации вы можете нарваться на несколько типичных «граблей»:

Источник