Как открыть pbix в excel

Обновлено: 29.06.2024

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

Можно и очень просто. Для этого только потребуется скачать и установить одну бесплатную надстройку. И разместить набор данных в Power BI Premium или у вас должна быть лицензия Power BI Pro.

Раньше для загрузки данных из Power BI в Excel использовалась специальная надстройка Power BI Publisher, которая создавала «коннект» между Excel и отчетами на онлайн-сервисе Power BI. После выхода последних обновлений Power BI компания Microsoft предлагает загружать данные из Power BI в Excel с помощью обновленного инструмента, названного «Обновления анализа в Excel» 🙂 От английского «Analyze in Excel updates». Его можно скачать бесплатно на онлайн-сервисе Power BI и сделать много хорошего.

А что именно можно сделать и как – разберем по шагам.

Подключение Excel к отчетам на портале Power BI

После скачивания установите надстройку на компьютере.

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

При первом запуске ODC-файла может потребоваться ввести почту и пароль для доступа к учетной записи Power BI. Если после их ввода Excel «не захочет» работать сразу, его нужно перезапустить.

4. Готово! На листе Excel создана сводная таблица с тем же набором полей, что и в файле Power BI. Можно настраивать сводные таблицы так, как вам нужно.

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

5. Если данные в вашем отчете Power BI поменялись, в файле Excel их тоже нужно обновить. Для этого нажмите кнопку Данные → Обновить.

Размещение отчетов Excel в сервисе Power BI

Допустим, ваш директор участвует в важном совещании и ему срочно нужно показать инвесторам «ту самую» Excel-табличку, которую вы сделали на прошлой неделе. Не проблема, табличку можно тут же разместить на Dashboard на онлайн-сервисе Power BI, и она станет доступна пользователям.

Кстати, разместить на информационной панели Power BI можно практически все, что есть в вашем файле Excel. Но без фанатизма: там не поддерживаются макросы, элементы управления, отчеты Power View, трехмерные карты и некоторые другие функции. Справедливости ради отмечу, что Power View можно импортировать в Power BI Desktop, а уже pbix-файл загрузить на сервис Power BI.

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

1. Перейдите на сервис Power BI и загрузите Excel-файл: нажмите кнопку Получить данные в левом нижнем углу.

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

3. В открывшемся файле выделите область, которую нужно «перенести» на Dashboard, затем нажмите Закрепить.

Выберите, на какой панели мониторинга нужно закрепить элементы файла.

4. Готово! Отчет появился на панели мониторинга. И директор сможет его тут же увидеть.

С помощью компонента Анализ в 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 для дизайнеров и разработчиков Power BI Desktop Требуется лицензия Pro или Premium

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

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

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

Требования

Экспорт данных из визуального элемента на панели мониторинга

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

Наведите указатель мыши на визуальный элемент, чтобы отобразилась кнопка Дополнительные параметры (. ), и щелкните ее, чтобы открыть меню действий.

Выберите Экспорт в CSV.

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

Откройте файл в Excel.

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

Экспорт данных из визуального элемента в отчете

Данные можно экспортировать из визуального элемента в отчете в формате CSV или XLSX (Excel).

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

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

Выберите визуальный элемент в отчете. Обратите внимание на панель Фильтры справа. К этому визуальному элементу применены фильтры. Дополнительные сведения о фильтрах см. в статье Ознакомление с панелью "Фильтры" отчета.

Выберите Дополнительные параметры (. ) в правом верхнем углу визуализации. Выберите Экспорт данных.

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

Сводные данные: выберите этот вариант, если вы хотите экспортировать данные, которые отображаются в визуальном элементе в настоящий момент. Этот тип экспорта отображает только те данные, которые использовались для создания текущего состояния визуального элемента. Если к визуальному элементу применены фильтры, экспортируемые данные также будут отфильтрованы. Например, для этого визуального элемента экспорт будет включать только данные для 2014 года и центрального региона и только данные для четырех производителей: VanArsdel, Natura, Aliqui и Pirum. Если в визуальном элементе есть агрегаты (сумма, среднее и т. д.), экспортируемые данные также будут агрегированы.

Базовые данные: выберите этот вариант, если вы хотите экспортировать данные, которые отображаются в визуальном элементе, а также дополнительные данные из базового набора данных. Это могут быть данные, содержащиеся в наборе данных, но не используемые в визуальном элементе. Если к визуальному элементу применены фильтры, экспортируемые данные также будут отфильтрованы. Если в визуальном элементе есть агрегаты (сумма, среднее и т. д.), они будут удалены из экспортируемых данных, то есть данные будут преобразованы в плоскую структуру.

Дальнейшее зависит от используемого браузера. Вам может быть предложено сохранить файл, или вы увидите ссылку на экспортированный файл в нижней части браузера. Если вы используете приложение Power BI в Microsoft Teams, экспортированный файл сохраняется в локальной папке "Загрузки".

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

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

Рекомендации и устранение неполадок

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

Если вы не можете использовать эту функцию, обратитесь к владельцу отчета и администратору, чтобы узнать, почему вам не удается экспортировать данные из определенного визуального элемента или из всех элементов. Возможно, функция была отключена намеренно, но ее можно включить для вас. Иногда экспорт может быть невозможен по особым причинам. Проблема может быть связана с разрешениями, содержимым данных, их типом, типом визуального элемента, именами полей, присвоенными разработчиками, и многим другим. При обращении к владельцу отчета или администратору предложите ему ознакомиться со следующими статьями: Параметры клиента для администратора, Безопасность на уровне строк и Защита данных.

Количество строк, которые можно экспортировать в Excel, ограничено. Для файлов XLSX ограничение составляет 150 000 строк. Для файлов CSV оно составляет 30 000 строк.

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

Некоторые типы визуальных элементов не поддерживаются. К ним относятся пользовательские визуальные элементы, визуальные элементы R и многие другие.

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

Как я могу открыть файл PBIX на моем ПК?

1. Установите Microsoft Power Bi

Так как файл PBIX в основном создается Microsoft Power Bi, необходимо установить Power Bi на вашем компьютере, чтобы открыть файл PBIX. При установке Power Bi убедитесь, что вы используете последнюю версию Power Bi. В противном случае он может не открыть файл. Вы можете получить Power BI Desktop здесь .

2. Установите правильную ассоциацию файлов

1. Откройте панель управления .
2. Поиск программ по умолчанию .
3. Перейти в набор ассоциаций .
4. Установите Microsoft Power Bi в качестве программы по умолчанию для открытия. файлы расширения pbix .

3. Переименуйте расширение в .Zip

Для просмотра содержимого файла вы можете переименовать расширение в .zip. Это не позволит вам изменять или изменять содержимое файла. Но это даст вам доступ к содержимому файла.

4. Открытие файла PBIX в Excel

Хотя вы не можете открыть файл PBIX непосредственно в Excel, вы можете экспортировать данные из Power Bi и импортировать их в Excel для выполнения вашей задачи.

В данной статье я хочу рассказать о некоторых возможностях бесплатной и крайне полезной, но пока еще мало известной надстройки над 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 умеет забирать данные из указанной папки и объединять их содержимое в единые таблицы. Это может быть полезно, например, если вам периодически приходят какие-то специализированные отчеты за отдельный промежуток времени, но данные для анализа нужны в общей таблице. Гифка

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

  
• Epson wf7710 программа для сканирования
  
• Затраты списываются без учета выручки в 1с что это
  
• Сохранение детализированного отчета в файл в формате csv
  
• Сертификаты в яндекс браузере где находятся
  
• Временная шкала в powerpoint