Power bi экспорт в excel

Экспорт запросов Power Query/M в файлы формата ODC в Excel 2016 в группе Get & Transform

Текст представляет собой адаптированный перевод статьи Chris Webb (Крис Уэбб), оригинал – Exporting Power Query/M Queries To ODC Files In Excel 2016 Get & Transform

Об авторе

Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И это одна из них.

Экспорт запросов Power Query/M в файлы формата ODC в Excel 2016 в группе Get & Transform

В последних обновлениях Office 365 click-to-run версии Excel 2016 добавлена действительно полезная функция в группе Get & Transform (ранее известная как Power Query): возможность экспорта/импорта ODC файлов, содержащих запросы на языке М. Хотя сейчас стандартный способ загрузки данных — использование группы Get & Transform, это очень удобное нововведение.

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

В разделе «Как получить данные из недавно используемых источников: ODC файлов, других соединений книги или таблиц?», находящемся в середине страницы.

Нужно просто щелкнуть правой кнопкой мыши на нужном запросе в области Queries & Connections и выбрать соответствующий пункт:

…если позже понадобится создать новое соединение, импортируйте ODC файл обычным способом.

Как всегда, хочется большего. Вот несколько идей/пожеланий:

Приложение Power BI Desktop тоже должно импортировать и экспортировать ODC файлы в такой формат. Это значительно облегчит повторное использование запросов.
Приложение Power BI должно иметь центральный репозиторий пользователей Power BI и Excel для хранения ODC файлов, чтобы облегчить их обмен и повторное использование разработчиками отчётов. Очень печально, что не наблюдается никаких шагов в области интеграции Azure Data Catalog и Excel/Power BI, так как это было бы очевидным местом для создания такого репозитория.
Также имеется острая необходимость в системе контроля версий запросов М непосредственно внутри Excel и Power BI (это несколько отличается от запросов предыдущего пункта). Множество людей проделывают это вручную, используя сервисы подобные Git. Но хочется иметь возможность проверять код непосредственно из редактора запросов.

Ещё одно улучшение – замена старого диалога «Load To» на стандартный диалог Excel с заголовком Import Data. Если нажать Close & Load в редакторе запросов, то увидим следующее:

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

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

К столбцам из текста в Power Query по нажатию на кнопки на Ribbon можно применять такие функции как:

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

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

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

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

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

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

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

Unpivot — Pivot

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

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

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

Читать еще: Как заменить столбец в excel

Операция 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.

Вот часть задач, которые я делаю с использованием Power Query:

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

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

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

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

Получение и преобразование и Power Pivot в Excel

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

Быстрый поиск подробных сведений

С помощью возможностей «скачать и преобразовать» и «Сводная таблица» в Excel вы будете тратить меньше времени на обработку данных, и в этом случае это скажется на влиянии. Просмотрите это 2-минутное видео, чтобы узнать, как это сделать.

Самостоятельное отображение

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

Другие ресурсы

Дополнительные сведения об этих мощных средствах анализа данных в Excel см. в других ресурсах:

Виртуальные лабораторные работы: в этом практическом занятии вы найдете статью «Создание и преобразование и включение Power Pivot» в виртуальной среде.

edX.org Analytics (анализ аналитики ) — для аналитики мы предлагаем бесплатный самостоятельный курс с этими возможностями, готовый к использованию видеороликов, лабораторий и ссылок.

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

Читать еще: Autofilter vba excel

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

Вопросы и ответы

В Office 365 профессиональный плюсв приложении Excel для компьютеров с Windows доступны все функции «скачать и преобразовать» и «Power Pivot», расширяющие возможности аналитики и моделирования, использующие преимущества расширенных возможностей подключения к данным и эффективнее Предоставьте совместный доступ к данным в Организации. Дополнительные сведения о доступности функций «скачать и преобразовать» и «Power Pivot» и попросите ИТ-специалиста, если ваш план Office поддерживает эти функции, см. в приведенных ниже сведениях.

Основные возможности бизнес-аналитики

Основные возможности аналитики доступны в любой подписке на Office 365 или любой покупке Office 2016 1. К таким функциям относятся Сводные таблицы, срезы, диаграммы и некоторые возможности получения и преобразования и модели данных. С помощью возможностей «скачать и преобразовать» вы можете импортировать данные из файлов, баз данных и веб-сайтов. Поддержка основных моделей данных позволяет загружать данные в модель данных, автоматически находить связи, добавлять группировки по времени и создавать меры DAX. *

Расширенные возможности бизнес-аналитики

Расширенные возможности аналитики доступны в Office 365 профессиональный плюс, Office 2016 Professional, Office 2016 профессиональный плюс и Excel 2016 для единовременно закупок. Помимо основных функций аналитики, вы можете использовать функции «скачать и преобразовать» для импорта, изменения и объединения данных из корпоративных, больших и облачных источников данных, а также предоставления общего поиска по каталогу корпоративных данных. Расширенная поддержка модели данных в Power Pivot включает специализированный редактор моделирования данных, представление данных, вычисляемые столбцы, ключевые показатели эффективности и иерархии.

Да, в Excel 2016 для Mac имеется множество основных функций анализа, перечисленных выше: сводные таблицы, срезы, диаграммы и основные возможности импорта. У него еще нет возможности «получить и преобразовать» или «Power Pivot», но мы постоянно улучшаем Excel на компьютере Mac.

Функция «скачать и преобразовать» обеспечивает быстрые и удобные возможности сбора данных и формирования форм. С помощью функции «скачать и преобразовать» можно подключать, объединять и уточнять источники данных в соответствии со своими потребностями анализа. Дополнительные сведения о программе «скачать и преобразовать».

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

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

Excel 2016 и Power BI Desktop предоставляют портфель инструментов, которые являются уникальными в отрасли. Вместе они позволяют бизнес-аналитикам легко собирать, группировать, анализировать и наглядно изучать данные. Power BI — это набор средств бизнес-аналитики, которые предоставляют аналитику в рамках всей Организации. Она помогает всем сотрудникам организации обмениваться данными из различных источников — книг Excel, локальных корпоративных данных, служб Azure, а также других популярных предложений на основе облака, таких как Salesforce.com или Marketo, для просмотра и совместного использования рабочих панелей. Интерактивные отчеты. Узнайте больше о том, как Power BI и Excel взаимодействуют друг с другом.

Для подписчиков Office 365 в Excel 2016 добавлены усовершенствованные функции и функции, которые вы уже используете.