Как включить power bi в excel

Обновлено: 04.07.2024

Почему Power BI и Excel вызывают такие споры? Одна из причин заключается в том, что владение Microsoft Excel было стандартным требованием для профессионалов на протяжении десятилетий. Фактически, это обычное условие для тех, кто претендует на вакансии с шестизначными цифрами.

Microsoft Power BI недавно оказался претендентом на свержение Excel. Power BI быстро становится самой полезной программой визуализации данных.

Но не все знают о его преимуществах.

Не знаете с чего начать? Не волнуйтесь, мы о вас позаботились.

Взгляните на то, что вам нужно знать о Microsoft Power BI и Excel.

Облачный сервис

Одним из наиболее заметных атрибутов, которые предлагает Microsoft Power BI, является возможность использования облачной службы. Это позволяет безопасно создавать резервные копии данных в удаленном месте, а также упрощает обмен данными.

Кроме того, вы можете настроить облачную службу для сбора отчетов из других веб-служб, таких как Salesforce. А также можете загружать отчеты прямо с устройств вашей компании.

Независимо от того, откуда поступил отчет, вы всегда сможете использовать веб-браузер для доступа к ним. Это обеспечивает значительную гибкость при анализе.

Мобильная доступность

Доступность и функциональность для мобильных устройств - это то, что интегрировано в Microsoft Excel. Однако она не известна как самая удобная функция.

Важно отметить, что это не имеет ничего общего с недостатком самого Excel. Его мобильное представление делает именно то, что вы ожидаете - оно позволяет пользователю просматривать электронные таблицы на мобильном устройстве.

Microsoft Power BI предлагает дополнительные функции, позволяя пользователям настраивать способ просмотра отчетов на платформе этого типа. В результате навигация по информации в электронной таблице становится более плавной и интуитивно понятной.

Варианты визуализации

Power BI предоставляет пользователям возможность выбирать инструменты из широкого спектра решений для визуализации данных. К ним относятся подробные диаграммы, графики и т. д., которые вы можете легко реализовать в зависимости от вашей ситуации и потребностей.

Для тех, кому требуются дополнительные параметры визуализации, могут загрузить альтернативы в разделе Marketplace программного обеспечения Microsoft BI.

Оповещения о данных

Power BI стремится быть максимально интуитивно понятным, чтобы обеспечить доступность и удобство. Его функция Data Alerts - один из наиболее заметных способов достижения этой цели.

Пользователи могут создавать наборы правил для своих отчетов, которые можно использовать для автоматического оповещения при соблюдении определенных критериев. Это особенно полезно для тех, кто часто проверяет электронные таблицы на предмет пороговых значений.

Например, вы можете использовать оповещения о данных, чтобы получать уведомление, как только достигнута квартальная цель дохода. Это приводит к лучшей организации и повышению общей производительности.

Создание рабочего пространства

Даже небольшие компании часто вынуждены создавать и получать отчеты из разных источников. По мере роста вашей компании растет и количество отчетов.

К счастью, Power BI позволяет создать рабочую область, которую можно использовать для консолидации всех данных. Собрав их вместе, вы можете просмотреть и проанализировать набор данных. Вы можете назначить пользователям или группам пользователей определенную роль, чтобы упростить совместное использование наборов данных и управление экспортом данных.

Для больших проектов можно создать несколько рабочих пространств для организации отчетов, аналитики и другого.

Быстрая статистика

Несомненно, по мере того, как проекты становятся крупнее, а таблицы данных становятся более объемными, может быть трудно найти информацию, которую вы ищете.

Функция Quick Insights позволяет автоматически проверять соответствующие тенденции, выбросы и корреляции в ваших наборах данных. Это поможет вам лучше понять имеющуюся информацию.

Их лучше всего использовать для таких показателей, как количество продаж, демографические данные аудитории и процентный рост, по многим из которых может быть сложно ориентироваться.

Скорость управления

Одним из наиболее заметных недостатков большого проекта в Microsoft Excel является печально известное замедление, которое происходит при редактировании ячеек. Во многих случаях также невозможно разбить эту информацию на более мелкие проекты.

К счастью, в Microsoft Power BI есть алгоритм, который упрощает сжатие модели данных. Эта функция напрямую увеличивает скорость обработки данных.

Если ваш бизнес постоянно занимается крупными проектами, вы обнаружите, что сэкономленное время не только повысит Вашу продуктивность, но и позволит использовать эти дополнительные часы для других обязательств.

Постоянное развитие

Несомненно, Microsoft Power BI - это решение Microsoft для обработки данных будущего. Это означает, что ему уделяется большое внимание, когда дело доходит до настройки, исправления и добавления функций.

Каждую неделю Microsoft обновляет инструмент еженедельных функций Power BI. Это означает, что вы можете ожидать своевременного ответа на любые неурегулированные ошибки или проблемы. Это не характерно для многих программных решений.

Многие из этих дополнительных функций также направлены на непосредственное повышение производительности, позволяя получить еще больше от программного обеспечения.

Выбор между Microsoft Power BI и Excel

Поначалу выбор между этими двумя инструментами может показаться трудным.

Но, учитывая приведенную выше информацию о Microsoft Power BI и Excel, вы будете на правильном пути к принятию решения, которое лучше всего подходит для вас и вашей компании.

С помощью компонента Анализ в Excel можно перенести наборы данных Power BI в Excel, а затем просматривать их и взаимодействовать с ними с помощью сводных таблиц, диаграмм, срезов и других функций Excel. Чтобы использовать Анализ в Excel, необходимо сначала скачать соответствующий компонент из Power BI, установить его, а затем выбрать один или несколько наборов данных для использования в Excel.

В этой статье показано, как установить и использовать анализ в Excel, описаны ограничения этого компонента, а также приведены связанные с ним дальнейшие действия. В этом учебнике вы узнаете:

Давайте приступим к установке.

Установка компонента "Анализ в Excel"

Компонент Анализ в Excel следует установить по ссылкам, указанным в службе Power BI. Power BI обнаружит версию Excel, установленную на компьютере, и автоматически скачает соответствующую версию (32- или 64-разрядную). Вы можете войти в службу Power BI по следующей ссылке:

Кроме того, в службе Power BI можно перейти к набору данных, который необходимо проанализировать, и выбрать элемент Дополнительные параметры для набора данных, отчета или другого элемента Power BI. В появившемся меню выберите параметр Анализ в Excel, как показано на следующем рисунке.

В любом случае Power BI определит, установлен ли компонент "Анализ в Excel", и если нет, вам будет предложено его скачать.

Когда вы выберите скачать, Power BI обнаружит установленную версию Excel и скачает соответствующую версию установщика компонента "Анализ в Excel". Вы увидите состояние скачивания в нижней части браузера или в том месте, где в браузере отображается ход загрузки.

После завершения скачивания запустите установщик (MSI-файл), чтобы установить анализ в Excel. Вместо "Анализ в Excel" для процесса установки используется имя наподобие Поставщик Microsoft Analysis Services OLE DB, как показано на следующем рисунке.

По завершении можно выбрать в службе Power BI отчет (или другой элемент данных Power BI, например, набор данных), а затем проанализировать его в Excel.

Подключение к данным Power BI

В службе Power BI перейдите к набору данных или отчету, который необходимо проанализировать в Excel, а затем выполните следующие действия.

Откройте меню Дополнительные параметры.

Среди пунктов меню выберите Анализ в Excel.

На приведенном ниже изображении показано, как выбрать отчет.

Помните, что если выбрать в меню "Отчет" пункт "Анализировать в Excel", то это будет базовый набор данных отчета, который переносится в Excel.

Затем служба Power BI создаст файл Excel набора данных с расширением файла ODC, предназначенный (и структурированный) для использования с компонентом Анализ в Excel, после чего начинается процесс загрузки в браузере.

Имя файла совпадает с именем набора данных (отчета или другого источника данных), из которого он получен. Таким образом, если отчет назывался Квартальный отчет, то скачанный файл будет называться Квартальный отчет.xlsx.

Теперь при использовании функции "Анализ в Excel" скачивается файл Excel, а не файл ODC. Это обеспечивает защиту данных, экспортированных из Power BI. Скачанный файл Excel наследует метку конфиденциальности набора данных, выбранного для функции "Анализ в Excel".

Запустите файл Excel.

При первом открытии файла может потребоваться включить редактирование, а затем включить содержимое в зависимости от настроек защищенного просмотра и доверенного документа.

При использовании Excel для анализа Power BI с помощью PivotTable решение Power BI применяет наследование метки конфиденциальности к Excel. Метка конфиденциальности, применяемая к набору данных Power BI, автоматически применяется к файлу Excel, если вы создаете таблицу PivotTable в Excel.

Если затем метка для набора данных меняется на более строгую, применяемая к файлу Excel метка будет автоматически заменена при обновлении данных в Excel. Если набор данных становится менее строгим, наследование или обновление метки не выполняется.

Заданные вручную метки конфиденциальности в Excel не будут автоматически перезаписаны меткой набора данных. Если файл Excel имеет заданную вручную метку конфиденциальности, отобразится совет политики с рекомендацией обновить метку.

Использование Excel для анализа данных

После включения редактирования и содержимого Excel отобразит пустую сводную таблицу и список полей из набора данных Power BI, готовых к анализу.

Файл Excel использует строку подключения MSOLAP для соединения с набором данных в Power BI. При анализе данных или работе с ними Excel отправляет запросы к набору данных Power BI, и результаты возвращаются обратно в Excel. Если набор данных подключен к динамическому источнику с помощью DirectQuery, Power BI отправляет запросы к этому источнику и возвращает результаты в Excel.

При наличии такого подключения к данным Power BI вы можете создавать сводные таблицы и диаграммы, а также анализировать набор данных точно так же, как и при использовании любого локального набора данных в Excel.

Анализ в Excel особенно удобен для наборов данных и отчетов Power BI, подключенных к следующим источникам данных:

Табличные модели данных служб Azure Analysis Services и табличные или многомерные модели данных служб SQL Server Analysis Services (SSAS)

Подключение к наборам данных из активного подключения к службам Analysis Services (Azure и SQL Server) в настоящее время не поддерживается

файлы Power BI Desktop или книги Excel с моделями данных, меры которых, созданы с помощью выражений анализа данных (DAX).

При использовании компонента Анализ в Excel всем пользователям с разрешением на доступ к набору данных данные предоставляются на всех уровнях детализации.

При использовании анализа в Excel необходимо учитывать несколько моментов, в связи с которыми может потребоваться выполнить одно или несколько дополнительных действий для согласования. Эти возможности описаны в следующих разделах.

Даже если вы уже вошли в Power BI в браузере, в первый раз при открытии нового файла в Excel вам, возможно, потребуется снова войти в эту службу со своей учетной записью Power BI. При это выполняется проверка подлинности соединения из Excel в Power BI.

Пользователи с несколькими учетными записями Power BI

Некоторые пользователи имеют несколько учетных записей Power BI. Если это действительно так, вы можете войти в Power BI, используя одну учетную записью, при том что доступ к набору данных, используемому для анализа в Excel, предоставлен другой. В этом случае при попытке доступа к набору данных, используемому в книге анализа в Excel, вы можете столкнуться с ошибкой Запрещено или ошибкой входа в систему.

Если это случится, вы сможете повторно войти в систему с помощью учетной записи Power BI, имеющей доступ к набору данных, используемому для анализа в Excel. Вы также можете выбрать свое имя на верхней ленте в Excel. По нему можно определить, с какой учетной записью вы выполнили вход в систему. Выйдите и снова войдите, используя другую учетную запись.

Сохранение и совместное использование новой книги

Книгу Excel, созданную с использованием набора данных Power BI, можно сохранить как и любую другую книгу. При этом ее нельзя опубликовать или импортировать обратно в Power BI,так как для публикации или импорта данные в книге должны содержаться в таблицах или использовать модель данных. Так как в новой книге есть только подключение к набору данных в Power BI, ее публикация или импорт в Power BI привели бы к возникновению замкнутого цикла.

Сохранив книгу, вы можете предоставить доступ к ней другим пользователям Power BI в своей организации.

Когда такой пользователь откроет вашу книгу в первый раз, он увидит в ней сводные таблицы и данные в том состоянии, в каком они были на момент последнего сохранения (то есть они могут быть неактуальны). Чтобы обновить данные до текущей версии, потребуется нажать кнопку Обновить на ленте Данные. А так как книга подключается к набору данных в Power BI, пользователи, пытающиеся обновить книгу, должны будут войти в эту службу и установить обновления для Excel (только в первый раз).

Так как пользователям требуется обновить набор данных, а обновление внешних подключений в Excel Online не поддерживается, рекомендуется открывать такие книги в классическом приложении Excel на компьютере.

Администраторы клиентов Power BI могут использовать портал администрирования Power BI, чтобы отключить функцию Анализировать в Excel для работы с локальными наборами данных, размещенными в базах данных Analysis Services (AS). Если этот параметр отключен, функция Анализировать в Excel будет отключена для баз данных AS, но по-прежнему будет доступна для работы с другими наборами данных.

Другие способы доступа к наборам данных Power BI из Excel

Пользователи с определенными номерами SKU Office также могут подключаться к наборам данных Power BI из Excel с помощью функции Получить данные в Excel. Если номер SKU не поддерживает эту функцию, пункт меню Получить данные не отображается.

В меню ленты Данные выберите Получить данные > Из набора данных Power BI, как показано на следующем рисунке.

Появится область, где можно просмотреть наборы данных, к которым у вас есть доступ, проверить, являются ли наборы данных сертифицированными или рекомендуемыми, и определить, применены ли к ним метки защит.

Дополнительные сведения о получении данных в Excel таким способом см. в статье Создание сводной таблицы на основе набора данных Power BI в документации по Excel.

Вы также можете получить в Excel доступ к рекомендуемым таблицам, которые отображаются в коллекции Типы данных. Дополнительные сведения о рекомендуемых таблицах и способах доступа к ним см. в статье Доступ к рекомендуемым таблицам Power BI в Excel (предварительная версия).

Требования

Некоторые требования к использованию Анализа в Excel:

Компонент Анализ в Excel поддерживается в Microsoft Excel 2010 с пакетом обновления 1 и более поздних версиях.
Сводные таблицы Excel не поддерживают агрегирование числовых полей перетаскиванием. В наборе данных Power BI должны быть заранее определенные меры. Прочитайте о создании мер.
В некоторых организациях могут действовать правила групповой политики, которые запрещают устанавливать необходимые обновления Анализ в Excel. Если вам не удается их установить, обратитесь к своему администратору.
Анализ в Excel требует, чтобы набор данных размещался в Power BI Premium или у пользователя была лицензия Power BI Pro. Дополнительные сведения о различиях в функциональных возможностях между разными типами лицензий Power BI см. в разделе Сравнение функций Power BI на странице Цены на Power BI.
Пользователи могут подключаться к наборам данных через Анализ в Excel, если у них есть разрешение для базового набора данных. Это разрешение можно получить разными способами, например через роль участника в рабочей области, содержащей набор данных, или разрешение на сборку для набора данных в рабочей области или содержащем набор приложении. Дополнительные сведения о разрешении на сборку для наборов данных.
Гостевые пользователи не могут использовать Анализ в Excel для наборов данных, отправленных (полученных) из другого клиента.
Компонент Анализ в Excel относится к службе Power BI. Он недоступен для Сервера отчетов Power BI или Power BI Embedded.
Анализ в Excel поддерживается только на компьютерах под управлением Microsoft Windows.
При совместном использовании книги Excel с помощью приложения Power BI необходимо предоставить разрешения на сборку для набора данных в приложении, а также разрешения для расположения OneDrive или SharePoint в отношении книги Excel. Если при предоставлении разрешений пользователям в приложении Power BI указывается параметр "Вся организация", необходимо задать эквивалентные настройки разрешений в OneDrive или SharePoint для книги Excel.

Если вам нужно удалить компонент Анализ в Excel, используйте средство Установка и удаление программ на компьютере.

Что такое Power BI?
В первую очередь хочу уточнить, что Power BI это не часть Excel, а самостоятельная программа для бизнесс-анализа, которую надо будет установить на ПК отдельно. Но оно того стоит. Сам Power BI включает в себя большой набор инструментов, позволяющих обработать и отобразить всю необходимую информацию о любых показателях компании (и не только) при помощи интерактивной визуализации. Проще говоря – при помощи Power BI можно легко и быстро, без длительного обучения и спец.навыков создать красивые интерактивные графики на основании практически любых данных. Сейчас очень модно называть такие наборы визуализаций «дашбордами»:

Пара вещей, которые надо знать о Power BI в первую очередь:

Он бесплатен. Не совсем, конечно. Есть еще версия Pro. Но уверяю – того, что умеет базовая бесплатная версия большинству хватить за глаза
Есть версии для смартфонов и планшетов под управлением ОС Windows, Android и iOS, а так же возможность выкладывать созданные отчеты в интернет, что дает возможность просматривать визуальные отчеты практически где угодно
Он способен получать данные из почти любых источников: из файлов Excel, CSV, XML, текстовые файлы, базы данных Access и SQL, Facebook, различные веб-сервисы (вроде Яндекса, Google и OneDrive) и еще более 30 других источников

Т.е. по сути, Power BI это Power Query и Power View в одном флаконе(плюс PowerPivot, от которого Power BI унаследовал некоторые алгоритмы и главное - мощные формулы DAX). Только к слову напомню, что Power View доступна далеко не каждому - она включена только в выпуск Office Professional Plus, начиная с Excel 2013. Владельцы других версий пролетают и побаловать себя крутыми визуализациями уже не имеют возможности.

Сам Power BI делится на три приложения (хоть устанавливать надо только одно):

Power BI Desktop – основное приложение, в котором создаются отчеты
Power BI Service(служба Power BI) - интернет-площадка, в которую помещаются все созданные отчеты и отправленные на публикацию в службу
Power BI for Mobile – приложение для смартфонов и планшетов, позволяющее просматривать опубликованные в службе Power BI отчеты

Что необходимо сделать и знать, прежде чем начать работать в Power BI

После запуска Power BI появится основное окно. Для начала достаточно просто уметь ориентироваться в рабочих областях. На скрине ниже я постарался выделить основные рабочие области этого окна

Кнопка вызова меню – раскрывает меню с доступом к основным командам и настройкам, таким как Публикация, Импорт и Экспорт, Сохранение, Справка, выход из учетной записи Power BI и пр.

Ribbon-панель с набором команд – на вкладках этой панели распределены основные инструменты для работы с запросами и визуальными элементами

Навигация по областям - основной областью является область визуализаций(первая сверху иконка), в которой строятся все визуальные отчеты. Но так же можно переходить в представление таблиц для просмотра данных и изменения определенных параметров полей(Типы данных, Сортировка, Добавление столбцов и мер и пр.) и в визуальное представление связей между таблицами. Здесь же можно настроить связи.

Визуализации - эта наверное одна из главных областей данного окна. Именно здесь расположены кнопки создания непосредственно графиков(они называются визуальными элементами). Нажатие на иконку, отражающую примерный вид визуального элемента автоматически добавляет такой элемент в рабочую область визуализаций.

Фильтры - данная область позволяет создавать фильтрацию данных, попадающих в визуальный элемент. Например, имея в таблице данных информацию по Доходам и Расходам, можно при помощи фильтров строить визуальный элемент только по расходам или только по доходам. Область фильтров в свою очередь делится на три раздела:

Фильтры уровня визуального элемента - помещенные сюда фильтры распространяют свое действие исключительно на тот визуальный элемент, к которому относится этот фильтр
Фильтры уровня страницы - помещенные сюда фильтры распространяют свое действие на все визуальные элементы той страницы, к которой относится этот фильтр
Фильтры уровня отчета - распространяет свое действие на все визуальные элементы всех страниц отчета

Поля - здесь перечислены все поля всех таблиц запросов. Именно на основании данных в этой области строятся визуальные элементы. Добавление данных из полей в визуальный элемент или область фильтров производится обычным перетаскиванием мыши.

Чуть ниже рабочей области есть еще область навигации по листам, где можно добавить в отчет новый лист, переименовать или удалить существующие.

При создании любого запроса(вкладка Главная -Получить данные) появится еще одно из основных окон – окно запросов. Для тех, кто работал в Power Query из Excel там вряд ли найдется что-то новое, т.к. Power BI полностью перенял работу с запросами именно от Power Query:

Кнопка вызова меню – раскрывает меню с доступом к основным командам, таким как Закрыть, Сохранить, а так же настройкам источников данных

Ribbon-панель с набором команд – как можно догадаться, это основная рабочая зона, т.к. именно на этой панели на вкладках(Главная, Преобразование, Добавить столбец, Просмотр) распределены основные инструменты для работы с запросами и данными

Область запросов – здесь перечислены все запросы, загруженные в текущую модель данных. Имена запросов можно изменять: либо двойным щелчком на имени запроса в Области запросов, либо в Панели свойств текущего запроса.

Область предварительного просмотра – здесь отображается предварительный просмотр вида данных, которые будут загружены в модель после всех наших действий. С её помощью можно наглядно видеть результирующий набор данных и выявлять ошибки.

Строка формул(Панель формул) – здесь отображается текст последнего произведенного действия с запросом. Отображается текст на встроенном языке M и его можно прямо в этой строке изменить. Отобразить или скрыть её можно с вкладки Просмотр -Панель формул.
Панель свойств текущего запроса – здесь собраны свойства выбранного запроса. Можно изменить имя запроса, а если раскрыть пункт Все свойства, то так же можно дать описание запроса и не включать запрос в обновление данных(по умолчанию все загруженные в модель запросы обновляются одной кнопкой Обновить из главного окна Power BI).

Панель выполненных в запросе операций(Примененные шаги) – очень удобная штука. Здесь отображаются по шагам все примененные в запросе действия: начиная от загрузки данных и завершая последним преобразованием. Если какую-то операцию в запросе сделали по ошибке или просто ошиблись параметрами – можно просто удалить последний шаг(или несколько). Можно сказать, что эта возможность заменяет собой откат действий в офисных программах(Ctrl+Z). Так же очень удобно, чтобы подучить синтаксис языка M – можно пошагово посмотреть какая операция как обзывается на этом языке. Так же весь текст запроса можно посмотреть, перейдя на вкладку Просмотр -Расширенный редактор.

Кратко о процессе работы в Power BI:

сначала входные данные загружаются в модель и подготавливаются (приводятся в удобный для построения визуализаций вид) при помощи Power Query
далее на основании подготовленных данных обычным перетаскиванием без сложных манипуляций выстраиваются диаграммы и прочие визуальные отчеты
созданные отчеты загружаются в облако, где уже другие пользователи могут посмотреть эти отчеты с браузера или из мобильного приложения

Кому подойдет Power BI?
Microsoft считает, что Power BI будет полезно небольшой, средней или крупной компании. Я считаю, что такое приложение необходимо всем, кто так или иначе занимается аналитикой данных и построением всевозможных отчетов.

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над MS Excel под названием Power Query.

Power Query позволяет забирать данные из самых разных источников (таких как csv, xls, json, текстовых файлов, папок с этими файлами, самых разных баз данных, различных api вроде Facebook opengraph, Google Analytics, Яндекс.Метрика, CallTouch и много чего еще), создавать повторяемые последовательности обработки этих данных и загружать их внутрь таблиц Excel или самого data model.

И вот под катом вы можете найти подробности всего этого великолепия возможностей.

Совместимость и технические подробности

Power Query доступен бесплатно для всех версий Windows Excel 2010, 2013 и встроен по умолчанию в Windows Excel 2016. Для пользователей MacOS X Power Query недоступен (впрочем, даже без этого маковский Excel отвратителен на ощупь и продвинутые пользователи, включая меня, чаще всего работают с нормальным Excel через Parallels или запуская его на удаленной виндовой машинке).

Также, Power Query встроен в новый продукт для бизнес аналитики — Power BI, а еще, ходят слухи, что Power Query будет появляться и в составе других продуктов от Microsoft. Т.е. Power Query ждет светлое будущее и самое время для адептов технологий Microsoft (и не только) заняться его освоением.

Как оно работает

После установки Power Query в интерфейсе Excel 2010–2013 появляется отдельная одноименная вкладка.

В новом Excel 2016 функционал Power Query доступен на вкладке Data (данные), в блоке “Get & Transform”.

Сначала, в интерфейсе Excel мы выбираем конкретный источник данных, откуда нам их нужно получить, и перед нами открывается окошко самого Power Query с предпросмотром первых строчек загруженных данных (область 1). В верхней части окошка располагается Ribbon с командами по обработке данных (область 2). И в правой части экрана (область 3) у нас расположена панель с последовательностью всех действий, которые применяются к данным.

Возможности Power Query

У Power Query очень много возможностей и я хочу остановиться на некоторых из числа моих любимых.

Как я уже писал выше, Power Query замечателен тем, что позволяет подключаться к самым разным источникам данных. Так он позволяет загружать данные из CSV, TXT, XML, json файлов. Притом процесс выбора опций загрузки тех-же CSV файлов гибче и удобнее, чем он реализован штатными средствами Excel: кодировка автоматически выбирается часто правильно и можно указать символ разделителя столбцов.

Объединение файлов лежащих в папке

Power Query умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

Текстовые функции

Разделить столбец по символу или по количеству символов. И в отличие от Excel можно задать максимальное количество столбцов, а также направление откуда нужно считать символы — слева, справа.
Изменить регистр ячеек в столбце
Подсчитать количество символов в ячейках столбца.

Числовые функции

К столбцам с числовыми значениями по нажатию на кнопки на Ribbon можно применять:

Арифметические операции
Возводить в степени, вычислять логарифмы, факториалы, корни
Тригонометрические операции
Округлять до заданных значений
Определять четность и т.д.

Функции для работы с датами, временем и продолжительностью

К столбцам со значениями даты и времени по нажатию на кнопки на Ribbon можно применять:

Автоматическое определение формата вписанной даты (в excel c этим большая боль)
Извлекать в один клик номер месяца, дня недели, количество дней или часов в периоде и т.п.

Unpivot — Pivot

В интерфейсе Power Query есть функция “Unpivot”, которая в один клик позволяет привести данные с одной метрикой разложенные по столбцам по периодам к форме, которая будет удобна для использования в сводных таблицах (понимаю что трудно написал — смотрите пример). Также, есть функция с обратным действие Pivot. Гифка

Операция Merge — смерть ВПР

Функция ВПР (VLOOKUP) одна из наиболее используемых функций в MS Excel. Она позволяет подтягивать данные в одну таблицу из другой таблицы по единому ключу. И вот как раз для этой функции в Power Query есть гораздо более удобная альтернатива — операция Merge. При помощи этой операции соединение таблиц нескольких таблиц в одну по ключу (по простому или по составному ключу, когда соответствие нужно находить по нескольким столбцам) выполняется буквально в 7 кликов мыши без ввода с клавиатуры.

Операция Merge — это аналог join в sql, и ее можно настроить чтобы join был разных типов — Inner (default), Left Outer, Right Outer, Full Outer.

Upd.Мне тут подсказали, что Power Query не умеет делать Aproximate join, а впр умеет. Чистая правда, из коробки альтернатив нет. Гифка

Подключение к различным базам данных. Query Folding.

Power Query также замечателен тем, что умеет цепляться к самым разным базам данных — от MS SQL и MySQL до Postgres и HP Vertica. При этом, вам даже не нужно знать SQL или другой язык базы данных, т.к. предпросмотр данных отображается в интерфейсе Power Query и все те операции, которые выполняются в интерфейсе прозрачно транслируются в язык запросов к базе данных.

А еще в Power Query есть понятие Query Folding: если вы подключены к совместимой базе данных (на текущий момент это MS SQl), то тяжелые операции по обработке данных Power Query будет стараться выполнить на серверной стороне и забирать к себе лишь обработанные данные. Эта возможность радикально улучшает быстродействие многих обработок.

Язык программирования “М”

Надстройка Power Query — это интерпретатор нового, скриптового, специализированного для работы с данными, языка программирования М.

На каждое действие, которое мы выполняем с данными в графическом интерфейсе Power Query, в скрипт у нас пишется новая строчка кода. Отражая это, в панели с последовательностью действий (область 3), создается новый шаг с говорящим названием. Благодаря этому, используя панель с последовательностью действий, мы всегда можем посмотреть как выглядят у нас данные на каждом шаге обработки, можем добавить новые шаги, изменить настройки применяемой операции на конкретном шаге, поменять их порядок или удалить ненужные шаги. Гифка

Также, мы всегда можем посмотреть и отредактировать сам код написанного скрипта. И выглядеть будет он примерно так:

Язык M, к сожалению, не похож ни на язык формул в Excel, ни на MDX и, к счастью, не похож на Visual Basic. Однако, он очень прост в изучении и открывает огромные возможности по манипуляции данными, которые недоступны с использованием графического интерфейса.

Загрузка данных из Яндекс.Метрики, Google Analytics и прочих Api

Немного овладев языком “M” я смог написать программки в Power Query, которые умеют подключаться к API Яндекс.Метрики и Google Analytics и забирать оттуда данные с задаваемыми настройками. Программки PQYandexMetrika и PQGoogleAnalytics я выложил в опенсорс на гитхаб под лицензией GPL. Призываю пользоваться. И я буду очень рад, если эти программы будут дорабатываться энтузиастами.

Для Google Analytics подобного рода экспортеров в разных реализациях достаточно много, но вот для Яндекс.Метрики, насколько я знаю, мой экспортер был первым публично доступным, да еще и бесплатным :)

Power Query умеет формировать headers для post и get запросов и забирать данные из интернета. Благодаря этому, при должном уровне сноровки, Power Query можно подключить практически к любым API. В частности, я для своих исследований дергаю данные по телефонным звонкам клиентов из CallTouch API, из API сервиса по мониторингу активности за компьютером Rescuetime, занимаюсь парсингом нужных мне веб-страничек на предмет извлечения актуальной информации.

Еще раз про повторяемость и про варианты применения

Как я уже писал выше, скрипт Power Query представляет собой повторяемую последовательность манипуляций, применяемых к данным. Это значит, что однажды настроив нужную вам обработку вы сможете применить ее к новым файлам изменив всего один шаг в скрипте — указав путь к новому файлу. Благодаря этому можно избавиться от огромного количества рутины и освободить время для продуктивной работы — анализа данных.

Я занимаюсь веб-аналитикой и контекстной рекламой. И так уж получилось, что с момента, как я познакомился с Power Query в ее интерфейсах я провожу больше времени, чем в самом Excel. Мне так удобнее. Вместе с тем возросло и мое потребление другой замечательной надстройки в MS Excel — PowerPivot.

разбираю семантику для Толстых проектов,
Делаю частотные словари,
Создаю веб-аналитические дашборды и отчеты для анализа конкретных срезов,
Восстанавливаю достижение целей в системах веб-аналитики, если они не настроены на проекте,
Сглаживаю прогноз вероятности методами Андрея Белоусова (+Байеса:),
Делаю аудит контекстной рекламы на данных из K50 статистика,
И много других разных ad-hoc analysis задач, которые нужно сделать лишь однажды

Вот bi систему, про которую я рассказывал на Yac/M 2015 (видео) я делал полнстью при помощи Power Query и загружал данные внутрь PowerPivot.

Пару слов про локализацию

На сайте Microsoft для пользователей из России по умолчанию скачивается Power Query с переведенным на русским язык интерфейсом. К счастью, локализаторы до перевода на русский языка программирования (как это сделано с языком формул в excel) не добрались, однако жизнь пользователям неоднозначными переводами сильно усложнили. И я призываю вас скачивать, устанавливать и пользоваться английской версией Power Query. Поверьте, она будет гораздо понятнее.

Читайте также: