power query и power pivot в чем разница
Узнайте, как использовать Power Query и Power Pivot в Excel
Быстрое получение аналитики
С помощью Power Query & (в Excel) и Power Pivot в Excel вы будете тратить меньше времени на обработку данных и больше времени на их влияние. Для ознакомления посмотрите это 2-минутное видео.
Попробуйте сами
Независимо от того, небольшие или крупные наборы данных с миллионами строк, вы можете быстрее подключаться, очищать, анализировать данные и делиться ими с помощью Power Query (или получить & Transform в Excel) и Power Pivot.
Вопросы и ответы
С Приложения Microsoft 365 для предприятий приложение Excel на Windows для ПК предлагает все функции Power Query и Power Pivot, которые улучшают аналитику и моделирование, могут использовать расширенные возможности подключения к данным и эффективно обмениваться данными в организации. Дополнительные сведения о доступности Power Query и Power Pivot см. ниже и узнайте у ИТ-группы, поддерживает ли ваш текущий план Office эти функции.
Основные функции бизнес-аналитики
Основные функции аналитики доступны для Microsoft 365 подписки или Office 2016. К таким функциям относятся срезы, срезы, диаграммы и возможности модели данных. С помощью Power Query можно импортировать, формировать и объединять данные из файлов, баз данных и веб-сайтов. Поддержка базовой модели данных позволяет загружать данные в модель данных, автоматически обнаруживать связи, добавлять группировку по времени и создавать показатели DAX.
Дополнительные сведения о том, как получить наилучшие результаты, см. в этой Office.
Расширенные функции бизнес-аналитики
Расширенные возможности аналитики доступны в Приложения Microsoft 365 для предприятий, Office 2019 Professional, Office 2019 Professional Plus и Excel 2019. Помимо основных функций аналитики, с помощью Power Query можно импортировать, формировать и объединять данные из корпоративных, больших данных и облачных источников данных, а также делиться запросами. Поддержка расширенных моделей данных в Power Pivot включает специальный редактор моделирования данных, представления данных, вычисляемые столбцы DAX, ключевые показатели эффективности и иерархии.
Дополнительные сведения см. в источниках данных Power Query в Excel версияхи Где находится Power Pivot?
Excel 2016 для Mac и Excel 2019 для Mac имеют множество основных функций анализа, перечисленных выше: срезы, диаграммы и основные возможности импорта. У них еще нет Power Query или Power Pivot, но мы постоянно улучшаем Excel на Mac. Excel для Microsoft 365 для Mac предлагает ограниченную поддержку Power Query.
Дополнительные сведения см. в этой Excel для Mac.
Power Query обеспечивает быстрый и простой сбор и формирование данных. Power Query позволяет подключать, объединять и уточнять источники данных в нужных для анализа запросах. Power Query можно использовать в четыре этапа:
Подключение Импорт данных и подключение к данным в облаке, в службе или локально.
Преобразовать Формировать данные в нужном виде, а исходный источник остается без изменений.
Объединить Дополнительные формы данных путем их интеграции из нескольких источников для получения уникального представления данных.
Нагрузки Выполните запрос и сохраните его на компьютере или в модели данных.
Дополнительные сведения см. в справке по Power Query в Excel и Power Query для Excel.
Возможность Power Pivot предназначена для эффективного анализа и создания сложных моделей данных. С помощью Power Pivot можно работать с большими объемами данных из различных источников одновременно, быстро анализировать их и распространять результаты. Вы можете использовать модель данных и коллекцию таблиц, чтобы создавать связи, сохраняемые в книге. Модель данных тесно интегрирована с другими функциями Excel, например таблицами и сводными таблицами, чтобы обеспечивать удобный интерфейс анализа.
Дополнительные сведения см. в этой Microsoft Excel.
Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. С помощью Power BI Desktop вы можете подключаться к сотням источников данных, упрощать подготовку данных и выполнять специальный анализ. Вы также можете создавать отчеты и публиковать их в организации для использования в Интернете и на мобильных устройствах. Все пользователи могут создавать персонализированные информационные панели с использованием всестороннего представления организации. Имеются встроенные средства управления и обеспечения безопасности.
Excel 2016 и Power BI Desktop предоставляют уникальный для отрасли набор инструментов. Вместе они позволяют бизнес-аналитикам легко собирать, формировать, анализировать и наглядно изучать данные. Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. Он помогает всем сотрудникам организации объединять данные из различных источников: книг Excel, локальных корпоративных данных, служб Azure и других популярных облачных решений, таких как Salesforce.com или Marketo, чтобы просматривать и совместно использовать информационные панели и интерактивные отчеты.
Дополнительные сведения см. в Excel для Power BI.
Подписчики Microsoft 365 и Excel 2019 добавили улучшения в функции и функции, к которые вы уже привыкли.
Совместное использование 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 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( таблПродажи[Выручка] )
Повторяем процесс еще два раза:
Добавленные меры появятся в правой панели сводной таблицы с характерным значком:
Теперь их можно смело закидывать мышкой в нашу сводную и выполнять план-факт анализ в любых разрезах за считанные секунды:
Возможные проблемы и их решения
В процессе реализации вы можете нарваться на несколько типичных «граблей»:
Что такое 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, например, созданный выше отчет выглядит так:
Разница между PowerPivot, PowerQuery и PowerBI
в чем разница между PowerPivot, PowerQuery и PowerBI? Как следует решить, какой инструмент следует использовать и когда.
3 ответов
Power Query
Power Query-это инструмент самообслуживания ETL (извлечение, преобразование, загрузка), который работает как надстройка Excel. Он позволяет пользователям извлекать данные из различных источников, манипулировать ими в форме, соответствующей их потребностям, и загружать их в Excel.
Power Pivot
Power Pivot-это компонент моделирования данных в памяти, который позволяет хранить сжатые данные и очень быстро агрегировать и вычислять. Это также доступна как часть Excel и может использоваться для создания модели данных в книге Excel. Power Pivot может загружать данные самостоятельно или загружать данные с помощью Power Query. Он очень похож на табличную модель служб SSAS (SQL Server Analysis Services), которая похожа на серверную версию Power Pivot.
Power View
Power View-это интерактивный инструмент визуализации, который предоставляет пользователям интерфейс перетаскивания, позволяющий создание быстрых и простых визуализаций данных в своих книгах Excel (с использованием модели данных Power Pivot).
Power BI
Power BI это служба SaaS, которая позволяет бизнес-пользователям обслуживать свои собственные потребности бизнес-аналитики. Он предоставляет встроенную возможность подключения к службам SaaS, таким как Salesforce и многие другие. Он обеспечивает подключение к локальным и облачным источникам с использованием комбинации прямого запроса и периодических данных обновляет. Он доступен как услуга freemium. Он является преемником «Power BI для Office 365», который был основан на продуктах Microsoft Office 365 и SharePoint Online и через Excel 2013 включает Power Query, Power Pivot и Power View.
Power BI (с O365 и SharePoint Online) предоставляет сайт, на котором пользователи могут загружать и обмениваться созданным контентом с другими пользователями, а также управлять шлюзами в корпоративный источник данных, включать обновление данных и дополнительно такие функции, как Q&A, который позволяет естественный язык запросов моделей данных.
Microsoft также выпустила автономное настольное приложение Power BI, который связывает Power Query, Power Pivot и Power View в автономном приложении, удаляя ограничение Excel 2013. Power BI Desktop доступен бесплатно.
также можно достичь многих функциональных возможностей Power BI, используя локальный SQL Server 2012+, Excel 2010+ и SharePoint 2010+, если облачный не является вариантом для вас.