Часть 1. Обзор Power Pivot и Power Query
Это вводная часть книги. В ней вы научитесь получать доступ к Power Query из разных версий Microsoft Excel, а также установите движок Power BI. Мы рассмотрим инструмент Power Pivot, упомянем все его недостатки и особенно остановимся на трудностях, с которыми сталкиваются пользователи Excel при очистке данных посредством Power Pivot. После этого мы представим вам Power Query и объясним на примере, как именно он способен нивелировать недостатки, характерные для Power Pivot. После знакомства со всеми тремя инструментами семейства Power – Power Query, Power Pivot и Power BI – мы поговорим о базовых возможностях первых двух: Power Query и Power Pivot.
Первая часть книги будет разбита на следующие главы:
глава 1. Установка и настройка;
глава 2. Основы Power Pivot, его недостатки и управление данными;
глава 3. Введение в интерфейс Power Query;
глава 4. Подключение к различным источникам данных.
Глава 1. Установка и настройка
Инструмент Power Query может быть полезен в самых разных сферах работы, а на рис. 1.1 мы привели основные причины его использования при подготовке данных для анализа, управления и визуализации.
@Рис. 1.1. Возможности Power Query
Сегодня Power Query интегрирована во все инструменты аналитики от Microsoft, такие как Excel, Analysis Services и Power BI. Это позволяет пользователям исследовать, преобразовывать и очищать данные, полученные из разных источников.
В данной главе мы рассмотрим пошаговую процедуру доступа к данным при помощи Power Query в разных версиях Microsoft Excel, а также установим инструмент Power BI. Кроме того, мы поговорим о недостатках Power Pivot, связанных с очисткой данных в Excel и предложим альтернативное решение в виде Power Query.
В этой главе будут затронуты четыре основные темы:
введение в Power Pivot, Power BI и Power Query;
различия между версиями Office и инструментами семейства Power;
инструкция по работе в разных версиях Excel;
запуск Power Query, Power Pivot и Power BI.
Требования
Желательно, чтобы читатель книги обладал как минимум средним уровнем знаний по Microsoft Excel, умел работать со строками, столбцами, рабочими листами и книгами, а также писать формулы и функции. Будет плюсом, если он также знаком с построением диаграмм и изменением их элементов.
Сопроводительные материалы можно загрузить с GitHub по следующему адресу: https://github.com/PacktPublishing/Learn-Power-Query.
Видеофрагмент, соответствующий этой главе книги, находится по адресу: https://www.youtube.com/watch?v=cxKvtorqP1Q&list=PLeLcvrwLe186O_GJEZs47WaZXwZjwTN83&index=2&t=4s.
Введение в Power Pivot
Очень важно, чтобы вы хорошо понимали термин модель данных (data model) перед погружением в работу с Power Pivot. Модель данных образуется путем объединения двух и более таблиц по соответствующим полям или столбцам. Если вам доводилось работать с базами данных наподобие Microsoft Access, значит, вы понимаете, что имеется в виду под связями между таблицами. Объединение таблиц из одного или нескольких источников в едином источнике данных называется моделью данных, как показано на рис. 1.2.
@Рис. 1.2. Представление связанных таблиц
Инструмент Power Pivot является составной частью семейства Power BI – его мозгом, поскольку в его функции входит моделирование данных, обработка, вычисление и анализ. Если использовать аналогию с автомобилем, то можно назвать это двигателем с моделью данных, хранящей все доступные данные. Он способен обрабатывать огромные наборы информации в созданной многотабличной модели данных, после чего может использоваться в качестве источника данных, например при создании сводных таблиц. Работая с данными на рабочем листе Excel, вы можете использовать Power Pivot для построения модели данных и последующего создания связей между таблицами для выполнения расчетов любой степени сложности непосредственно в среде Excel. Power Pivot помогает при моделировании данных, установке связей между таблицами, создании вычисляемых столбцов, мер и показателей эффективности, а также может использоваться для создания куба.
Power Pivot и версии Office
Сначала мы приведем список совместимых версий Office, а затем рассмотрим отличия между ними.
Power Pivot Office 2019 (Office 365), 2016 и 2013
Раньше Power Pivot был доступен только в версиях Office Pro Plus 2013/2016, но не так давно компания Microsoft добавила этот инструмент к следующим версиям ПО: Office 365 Home, Office 365 Personal, Office 365 Business Essentials, Office 365 Business, Office 365 Business Premium и Office 365 Enterprise E1.
В этих версиях Power Pivot установлен по умолчанию, но иногда может потребоваться его активация. Об этом мы расскажем позже в разделе Повторная активация Power Query/Pivot.
Power Pivot Office 2010
Надстройка Power Pivot не была интегрирована в версию Office 2010, но может быть бесплатно загружена и установлена по адресу: https://www.microsoft.com/en-us/download/details.aspx?id=43348. У надстройки Power Pivot для Office 2010 было две версии, ожидаемо названные version и version 2 соответственно. Они были написаны в виде плагинов для разработки SQL Server 2012. При загрузке вы увидите следующую надпись: Microsoft® SQL Server® 2012 SP2 PowerPivot for Microsoft Excel® 2010. Если у вас нет SQL Server, не беспокойтесь – именно эта версия понадобится вам для установки в Excel 2010. Примечательно, что размер скачиваемого файла будет довольно большим.
Введение в Power BI
Инструмент Power BI предлагается в качестве облачной платформы или программного обеспечения как услуги (Software as a Service – SaaS), что позволяет удовлетворить все бизнес-требования заказчиков. Огромным преимуществом такого подхода является возможность без проблем обрабатывать миллионы строк данных – вы можете моделировать и анализировать данные путем объединения таблиц. Это позволяет один раз определить формулу, а затем многократно использовать ее при управлении данными!
Power BI состоит из Power Query, Power Pivot и Power View и позволяет представлять информацию в удобном для пользователя виде – при помощи создания отчетов и дашбордов. Этот инструмент доступен в облаке, где пользователи могут загружать данные и обмениваться информацией между собой, выполняя запросы к модели данных на естественном языке. Автономная версия инструмента является бесплатной и включает в себя все три приложения: Power Query, Power View и Power Pivot. Power View позволяет создавать интерактивные визуализации посредством переноса элементов мышью в отчет.
Различия между версиями Power BI
Для начала мы приведем список совместимых версий продукта для Windows и macOS, а затем рассмотрим отличия между ними.
Windows
Существует три разных версии Power BI, доступных для загрузки. Давайте по ним пройдемся.
Power BI Desktop/Free
Версия Power BI Desktop, которую часто называют Power BI Free, предназначена для компаний малого и среднего размера. При помощи нее вы можете подключаться к порядка 70 источникам данных, публиковать отчеты в интернете и экспортировать данные в Excel.
Вы можете загрузить эту версию по адресу https://www.microsoft.com/en-us/download/details.aspx?id=58494 или с Microsoft Store: https://aka.ms/pbidesktopstore.
Примечание. Системные требования Power BI Desktop довольно лояльны, и большинство компьютеров им будет удовлетворять: Windows 7 или выше, Internet Explorer 10 или выше, 1 Гб RAM, .Net 4.5 и частота CPU как минимум 1 ГГц. Что вас может удивить, так это то, что разрешение вашего монитора по умолчанию 1024 x 768 или 1280 x 800 может оказаться недостаточным для Power BI. Рекомендуемыми разрешениями являются 1440 x 900 или 1600 x 900, что обусловлено требованиями некоторых элементов управления.
При загрузке вы также должны указать разрядность вашей операционной системы: 32 или 64 бит.
Заметим, что разрядность касается исключительно данного приложения и может отличаться от разрядности Office.
Загрузите подходящий вам установочный файл Power BI Desktop, после чего запустите его. В окне установки обратите внимание на сообщение о том, какие данные программа Power BI будет собирать при работе. Вы имеете право отказаться от этого, для чего нужно щелкнуть в соответствующем месте установочного окна.
Далее выберите место на диске, куда будет установлен Power BI. Лично я предпочитаю оставлять путь по умолчанию. В конце концов, если у вас возникнут проблемы с поиском программы на диске, вам будет легче найти его, используя путь по умолчанию. При установке в другую папку на диске это может быть проблематично.
@Рис. 1.3. Окно загрузки Power BI Desktop
На этапе первой загрузки Power BI вам будет предложено подписаться на новостную рассылку, в которой вам будут приходить советы и тонкости использования инструмента.
@Рис. 1.4. Окно регистрации Power BI Desktop
У вас также есть возможность авторизоваться (используйте эту ссылку для бесплатной пробной подписки на Power BI: https://powerbi.microsoft.com/en-us/) или зарегистрироваться.
@Рис. 1.5. Окно авторизации Power BI Desktop
После этого вы увидите окно загрузки программы, запуск которого вы можете отключить, сняв соответствующий флажок Show this screen on startup (Показывать окно загрузке при запуске).
@Рис. 1.6. Главный экран Power BI Desktop
Нам кажется, что легче всего установить Power BI Desktop путем загрузки из Windows Store (доступно для Windows 8 и выше). Единственная проблема в этом случае состоит в том, что вам придется довольствоваться настройками по умолчанию. Так что если вам необходимо что-то изменить, лучше будет выбрать первый способ.
Mac / Apple
К сожалению, инструмент Power BI может быть запущен только в Windows. Если у вас Mac, вы не сможете напрямую установить Power BI – придется воспользоваться утилитой Boot Camp, создать локальную виртуальную машину (VM) или использовать стороннее ПО. Также вы можете попробовать для этих целей программы вроде Turbo.net (http://turbo.net/) или Parallels.
Что касается мобильных платформ, то вы можете установить приложение Power BI на свой iPhone или iPad из App Store по ссылке: https://apps.apple.com/us/app/microsoft-power-bi/id929738808.
Введение в Power Query
Power Query также входит в семейство Power BI и позволяет вам извлекать и загружать данные из огромного количества источников, включая файлы, базы данных и страницы в интернете. Можно рассматривать Power Query как сборщик или преобразователь данных, ведь зачастую в Power Pivot информация поступает именно из него. Работая с Excel, вы можете создать модель данных путем добавления таблиц, загруженных из разных источников посредством Power Query. Для этого нужно установить переключатель Add to Data Model (Добавить в модель данных) во время импорта. Затем вы используете Power Pivot для создания связей между таблицами. Вы можете импортировать источники данных как в Power Query, так и в Power Pivot, но Power Query является более предпочтительным вариантом, поскольку предоставляет больше выбора, и файл будет занимать меньше места на диске.
Можно импортировать данные в существующие таблицы Power Pivot или Excel, после чего выполнить преобразование данных, прежде чем использовать их в Excel. Инструмент Power Query доступен в Excel и Power BI, и преобразованные данные могут быть открыты непосредственно в этих программах. Power Query дает возможность подключаться и извлекать данные, настраивать условия, объединять и комбинировать, добавлять и изменять данные, трансформировать их и публиковать. В Power Query используется собственный язык M, который мы детально изучим в соответствующей главе.
Особенности Power Query
Перед созданием дашбордов в Excel вам необходимо воспользоваться помощью Power Query. Лист Excel ограничен 1 048 576 строками, и если попытаться загрузить более объемные данные из источника, Excel просто не сможет завершить операцию или загрузит столько записей, сколько сможет, что также может сказаться на производительности. Чтобы решить эту проблему, необходимо воспользоваться Power Query, позволяющим вам подключаться непосредственно к источникам данных. Это означает, что данные не будут физически сохраняться в файле Excel, а останутся в источнике.
Power Query дает возможность избежать монотонных действий, повторяющихся снова и снова, например запуска макроса или задачи. Этот инструмент позволяет создавать именованные запросы, содержащие последовательность шагов, которые будут применяться к набору данных, и при этом вам совсем не обязательно уметь программировать. Это способствует снижению затрат и времени компании, а также исключает необходимость обращаться к сторонним разработчикам.
Еще одной полезной особенностью Power Query является его способность разворачивать данные таким образом, чтобы вы могли создавать несколько отчетов со сводными таблицами, преобразуя данные в табличный формат. Также это позволяет объединять данные из разных таблиц в единую сводную таблицу. Это исключительно полезно, если требуемые вам данные содержатся в разных файлах Excel, создаваемых различными подразделениями компании.
Еще одной причиной использования Power Query является его способность импортировать текстовые файлы, расположенные в одной папке. Этот инструмент быстро и без особого труда соберет информацию из всех файлов в единый файл Excel. Кроме того, вы можете просто подключаться к ним, что позволит сэкономить немало времени при создании сводных таблиц.
Power Query представлял собой бесплатную надстройку в ранних версиях Excel, а в поздние версии он интегрирован по умолчанию. В следующих разделах данной главы вы узнаете, какие версии Office поддерживают Power Query и где можно скачать этот инструмент для более старых версий Microsoft Excel.
Версии Office и совместимость с Power Query
Сначала мы перечислим совместимые версии Windows и macOS, а затем пройдемся по отличиям в них касательно применения Power Query.
Windows/Android
В следующих версиях программного обеспечения есть полноценная поддержка Power Query:
Microsoft Office 2019, 2016 и 2013: все версии;
Microsoft Office 2010 Professional Plus с Software Assurance;
Power Query Premium: все возможности Power Query доступны для Professional Plus, Office 365 ProPlus и Excel 2013 Standalone;
Power Query Public: доступен для все версий Office 2013 Desktop. Включены все возможности Power Query, за исключением Corporate Power BI Data Catalog, источников данных на базе Azure, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects и Salesforce;
Get & Transform (Power Query) в настоящее время не поддерживается Android, iOS и Online.
Mac
Поддерживаемые версии для macOS:
Excel 2011 и Excel 2016 для Mac: Get & Transform (Power Query) не поддерживается;
Excel для Office 365 для Mac: если вы являетесь подписчиком Office 365 и также подписаны на программу Windows Insider, то можете обновлять существующие запросы Power Query на своем Mac.
Отличия между версиями Office 2019 (Office 365) и Office 2016
Если у вас установлен Office 2019 или Office 2016, значит, у вас уже есть Power Query и Power Pivot. В настоящее время эти инструменты располагаются в группе Get & Transform (Получить и преобразовать данные) на вкладке Data (Данные).
@Рис. 1.7. Окно Office 2019
А вот как выглядит окно Office 2016:
@Рис. 1.8. Окно Office 2016
Как видите, между Office 2016 и Office 2019 есть незначительная разница в отношении расположения инструмента Power Query, но это никак не сказывается на его работе. Однако если у вас установлен Office 2016, ваше окно может выглядеть и так, как показано на рис. 1.9.
@Рис. 1.9. Альтернативное окно Office 2016
Причина этого в существовании разных версий Excel 2016: с MSI (Windows Installer), в виде подписки Office 365 и версии ProPlus. Этим объясняются незначительные различия во внешнем виде окна программы.
Установка Power Query в Office 2013 и 2010
Для указанных версий Office вам необходимо загрузить надстройку Power Query по следующей ссылке: https://www.microsoft.com/en-us/download/details.aspx?id=39379.
После нажатия на кнопку Download на этой странице вам будет задан вопрос о том, какой разрядностью обладает установленный у вас Office: 32 или 64 бит. Если вы знаете, какая у вас версия, скачивайте соответствующий файл.
Примечание. Единственное отличие между этими версиями состоит в том, что 32-битная версия может комфортно работать с количеством строк данных, близким к двум миллионам. 64-битная версия без труда справляется с объемами данных, превышающими эту цифру в 20 раз. Также стоит помнить, что если вы создадите рабочую книгу в Power Pivot размером в 5 миллионов строк и дадите доступ к ней человеку с 32-битной версией системы, он откроет рабочую книгу, но не сможет взаимодействовать с таблицей.
После определения своей версии ПО загрузите соответствующую версию надстройки с сайта Microsoft. Чтобы узнать версию операционной системы, можете воспользоваться следующей ссылкой: https://support.microsoft.com/en-gb/help/13443/windows-which-version-am-i-running.
Для установки надстройки Power Query выполните следующие действия.
Перед тем как запускать загруженный файл, убедитесь, что Excel закрыт. Соглашайтесь со всеми предложенными условиями и жмите кнопку Next до завершения установки.
Совет. Для корректной установки надстройки в вашей системе должен быть установлен браузер Internet Explorer версии 9 или выше. Если у вас установлен Internet Explorer более ранней версии, необходимо сначала обновить его, после чего продолжить установку Power Query.
По окончании установки откройте Excel 2010 или 2013. Вы увидите новую вкладку на ленте с названием Power Query, как показано на рис. 1.10.
@Рис. 1.10. Вкладка Power Query на ленте в Excel
Повторная активация Power Query/Pivot
Иногда случается, что в Excel пропадают вкладки Power Query или Power Pivot. Это происходит из-за проблем с загрузкой надстроек COM. Если это случилось, необходимо заново выбрать нужную вам надстройку в разделе COM Add-ins (Надстройки COM). Добраться до этого раздела можно разными способами, но легче всего это будет сделать так:
Примечание. Рекомендованный способ подходит как для Power Query, так и для Power Pivot. Для простоты на иллюстрации мы покажем только метод повторной активации надстройки Power Query.
Запустите Excel, войдите в меню File (Файл) и выберите пункт Options (Параметры). Откроется окно, показанное на рис. 1.11.
@Рис. 1.11. Окно параметров Excel
Зайдите в пункт Add-ins (Надстройки) в боковом меню, а затем в выпадающем списке Manage (Управление) выберите COM Add-ins (Надстройки COM), как показано на рис. 1.12, и нажмите кнопку Go (Перейти).
@Рис. 1.12. Окно надстроек в Excel
Откроется диалоговое окно COM Add-ins (Надстройки для модели компонентных объектов (COM)), показанное на рис. 1.13, в котором вы можете установить флажок напротив пункта Power Query, если он снят.
@Рис. 1.13. Диалоговое окно надстроек COM в Excel
Запуск Power Query, Power Pivot и Power BI
В данном разделе вы научитесь запускать редактор Power Query (Power Query Editor) из Microsoft Excel и Power BI. Мы покажем этот процесс на примере Microsoft Office 2019.
Запуск Power Query из Excel
Чтобы запустить редактор Power Query в Excel, проделайте следующие действия.
Откройте Microsoft Excel 2019.
Перейдите на вкладку Data в ленте.
Нажмите на кнопку Get Data (Получить данные), как показано на рис. 1.14. Именно так осуществляется доступ к Power Query.
@Рис. 1.14. Доступ к Power Query в Excel
Выберите в предложенном списке источник данных для подключения посредством Power Query.
Пункт Launch Power Query Editor (Запустить редактор запросов) используется для выполнения запуска редактора Power Query.
Доступ к Power Query из Power BI
Выполните следующие шаги для осуществления доступа к инструменту Power Query из Power BI.
Запустите программу Power BI Desktop на своем компьютере, как показано на рис. 1.15.
@Рис. 1.15. Запуск Power BI Desktop
Появится окно загрузки программы, представленное на рис. 1.16.
@Рис. 1.16. Окно запуска Power BI Desktop
Прямо в окне запуска есть пункт Get Data (Получить данные), дающий доступ к Power Query. Если вы хотите войти в Power Query из самой программы Power BI, то закройте окно запуска.
Находясь в Power BI, нажмите на кнопку Get Data (Получить данные) в группе Data (Данные) на вкладке Home (Главная страница), как показано на рис. 1.17, чтобы получить доступ к Power Query.
@Рис. 1.17. Power BI Desktop
Выберите источник данных, чтобы начать работу с Power Query.
Заключение
В этой главе вы познакомились с тремя мощными инструментами из семейства Power, позволяющими создавать модели данных, трансформировать и преобразовывать информацию и создавать потрясающие дашборды. Вы узнали, что из себя представляют программные продукты Power BI, Power Query и Power Pivot, и научились встраивать эти инструменты в среду Microsoft Office Excel разных версий. Кроме того, вы получили представление о различиях этих инструментов в разных версиях Office. В конце главы вы узнали о том, как запускать Power Query при помощи пункта меню Get Data (Получить данные).
Во второй главе книги мы подробно остановимся на недостатках инструмента Power Pivot и объясним, почему Power Query является наиболее приоритетным выбором при необходимости извлекать и преобразовывать данные. Прочитав эту главу, вы поймете всю важность управления и анализа данных на практике. Мы рассмотрим примеры использования инструментов Power Pivot и Power Query. Вы также узнаете, как преобразовывать рабочие листы в таблицы, пригодные для импорта посредством Power Query.
… Материал не полный и предоставлен для ознакомления …