power query для чего нужен
Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel
Термины «Power Query», «Power Pivot», «Power BI» и прочие «пауэры» все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.
Давайте проясним ситуацию.
Power Query
Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:
В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :
Возможности этих вариантов совершенно идентичны.
Основное окно Power Query обычно выглядит примерно так:
Power Pivot
Общие принципы работы в Power Pivot следующие:
Главное окно Power Pivot выглядит примерно так:
А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:
У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:
К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.
Power Maps
Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).
Ключевые особенности Power Map:
Power View
Выглядеть это будет примерно так:
На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.
Power BI
В Power BI Desktop можно:
Выглядит это примерно так:
На iPhone, например, созданный выше отчет выглядит так:
О Power Query в Excel
С помощью Power Query (в предыдущих версиях Excel & преобразование данных) можно импортировать или подключиться к внешним данным, а затем сформировать их, например удалить столбец, изменить тип данных или объединить таблицы в нужном виде. Затем вы можете загрузить запрос в Excel, чтобы создавать диаграммы и отчеты. Периодически обновляйте данные, чтобы обновить их.
Power Query можно использовать в четыре этапа.
Подключение Подключение к данным в облаке, в службе или локально
Преобразование Данные фигуры должны соответствовать вашим потребностям, а исходный источник остается без изменений
Объединить Интеграция данных из нескольких источников для получения уникального представления данных
Загрузить Заполните запрос и загрузите его на таблицу или в модель данных и периодически обновляйте его.
Рассмотрим каждый этап более подробно. В заключительном разделе содержится история работы Power Query в Excel. Общие сведения по всем темам справки по Power Query см. в справке Power Query для Excel.
С помощью Power Query можно импортировать данные в один источник данных, например в книгу Excel, или в несколько баз данных, веб-каналов или служб, расположенных в облаке. К источникам данных относятся данные из Интернета, файлов, баз данных, таблиц Azure и даже таблиц Excel в текущей книге. С помощью Power Query вы сможете собрать все эти источники данных, используя собственные уникальные преобразования и сочетания для анализа информации, которая в противном случае была бы не видна.
После импорта вы можете обновить данные, чтобы внести в них дополнения, изменения и удалить их из внешнего источника. Дополнительные сведения см. в теме «Обновление подключения к внешним данным в Excel».
Преобразование данных означает внесение в них изменений в зависимости от требований к их анализу. Например, можно удалить столбец, изменить тип данных или отфильтровать строки. Каждая из этих операций — это преобразование данных. Этот процесс применения преобразований (и объединения) к одному или несколько наборам данных также называется формированием данных.
Это можно обдумать так. Вазу начинается как единое обилие, которое превратилось в одну фигуру в практический и красивый. Данные одинаковы. Она должна формироваться в таблицу, подходящую для ваших потребностей и которая позволяет использовать привлекательные отчеты и панели мониторинга.
Power Query использует выделенное окно, называемое редактором Power Query, для упрощения и отображения преобразований данных. Чтобы открыть редактор Power Query, выберите редактор запросов запуска в группе «Получить данные» в группе «Пуск & Преобразование данных», но он также откроется при подключении к источнику данных, создании запроса или загрузке запроса.
Редактор Power Query отслеживает все ваши работы с данными, записывая и пометив каждое преобразование или шаг, которые вы применяйте к данным. Независимо от того, является ли преобразование подключением к данным, удалением столбцов, слиянием или изменением типа данных, каждое преобразование можно просмотреть и изменить в разделе APPLIED STEPS области «Параметры запроса».
В пользовательском интерфейсе можно внести множество преобразований. Каждое преобразование записуется в качестве шага в фоновом режиме. Вы даже можете изменить и написать собственные действия с помощью языка Power Query M в расширенный редактор.
Все преобразования, которые применяются к вашим подключениям к данным, в совокупности образуют запрос, который представляет исходный (без изменений) источник данных. При обновлении запроса каждый шаг выполняется автоматически. Запросы заменяют необходимость вручную подключать и формировать данные в Excel.
Вы можете объединить несколько запросов в книге Excel путем их слияния или объединения. Операции «Приложение» и «Слияние» выполняются с любым запросом в виде таблицы и не зависят от источников данных.
«Приложение» При операции создания запроса создается новый запрос, содержащий все строки из первого запроса, за которым следуют все строки из второго запроса. Операции с приложением можно выполнять двумя типами:
Промежуточная досье Создает новый запрос для каждой операции.
Inline Append Данные будут сданы в существующем запросе, пока вы не дойдете до конечного результата.
Слияние Операция слияния создает новый запрос на основе двух существующих запросов. Этот запрос содержит все столбцы главной таблицы, один из них служит ссылкой на связанную таблицу. Связанная таблица содержит все строки, которые соответствуют каждой строке из общего значения столбца в главной таблице. Более того, столбцы из связанной таблицы можно расширить или добавить в главную.
Существует два основных способа загрузки запросов в книгу:
В редакторе Power Query можно использовать команды «Закрыть» и «Загрузить» в группе «Закрыть» на вкладке «Главная».
В области «Запросы книги Excel» («Выбор запросов и & подключений») щелкните запрос правой кнопкой мыши и выберите «Загрузить в».
Кроме того, вы можете настроить параметры загрузки в диалоговом окне «Параметры запроса» (выберите «Параметры файла> и параметры > Параметры запроса»), чтобы выбрать способ просмотра данных и место их загрузки на компьютере или в модель данных (реляционный источник данных из нескольких таблиц, которые находятся в книге).
Интеграция данных & (power Query) с Excel за годы внося изменения.
Excel 2010 и 2013 для Windows
В Excel 2010 для Windows мы впервые представили Power Query, и он был доступен как бесплатная надстройка, которую можно скачать здесь: скачайте надстройки Power Query. После включения функции Power Query были доступны на вкладке Power Query на ленте.
Мы обновили Power Query, чтобы сделать его основным в Excel для импорта и очистки данных. Вы можете получить доступ к мастерам и инструментам импорта данных Power Query в группе «& Преобразовать данные» на вкладке «Данные» ленты Excel.
В этом режиме были улучшены функции импорта данных, переустановка команд на вкладке «Данные», новая боковая & запросы и возможность формировать данные с помощью сортировки, изменения типов данных, разделения столбцов, агрегирования данных и так далее.
Этот новый опыт также заменил старые мастеры импорта данных, которые были заменены командой «Данные» в группе «Внешние данные». Однако доступ к ним по-прежнему можно получить в диалоговом окне «Параметры Excel» (выберите «Параметры файла> Параметры > Данные> Показать устаревшие мастеры импорта данных).)
Excel 2016 и 2019 для Windows
Мы добавили тот же & » Get & Transform Data» с учетом технологии Power Query, что и в Microsoft 365.
Excel для Microsoft 365 для Mac
В 2019 г. мы начали работу по поддержке Power Query в Excel для Mac. С этого момента мы добавили возможность обновлять запросы Power Query из TXT-, CSV-, XLSX-, JSON- и XML-файлов. Мы также добавили возможность обновления данных с SQL сервера и из таблиц & диапазонов в текущей книге.
В октябре 2019 г. мы добавили возможность обновлять существующие запросы Power Query и использовать VBA для создания и изменения новых запросов.
В январе 2021 г. мы добавили поддержку обновления запросов Power Query из источников OData и SharePoint.
Примечание Power Query не поддерживается в Excel 2016 и Excel 2019 для Mac.
Депрекация каталога данных
С помощью каталога данных можно просмотреть общие запросы, а затем выбрать их для загрузки, редактирования или использования в текущей книге. Эта функция постепенно была неподготовлена к:
1 августа 2018 г. мы прекратили передачу новых клиентов в каталог данных.
3 декабря 2018 г. пользователи не могли делиться новыми или обновленными запросами в каталоге данных.
4 марта 2019 г. каталог данных перестал работать. После этой даты мы рекомендуем скачать общие запросы, чтобы вы могли продолжать использовать их за пределами каталога данных, используя параметр «Открыть» в области задач «Запросы моего каталога данных».
Отключение надстройки Power Query
В начале 2019 г. мы официально не добавили надстройки Power Query, необходимую для Excel 2010 и 2013 для Windows. Во время пользования надстройка может быть вам предоставлена, но впоследствии она может измениться.
Неавторяемая соединитела данных Facebook
Импорт и обновление данных из Facebook в Excel прекращены в апреле 2020 г. Все подключения к Facebook, созданные до этой даты, больше не работают. Мы рекомендуем как можно скорее исправить или удалить существующие запросы Power Query, которые используют соединител Facebook, чтобы избежать непредвиденных результатов.
Узнайте, как использовать Power Query и Power Pivot в Excel
Быстрое получение аналитики
С помощью Power Query & (в Excel) и Power Pivot в Excel вы будете тратить меньше времени на обработку данных и больше времени на их влияние. Для ознакомления посмотрите это 2-минутное видео.
Попробуйте сами
Независимо от того, небольшие или крупные наборы данных с миллионами строк, вы можете быстрее подключаться, очищать, анализировать данные и делиться ими с помощью Power Query (или получить & Transform в Excel) и Power Pivot.
Вопросы и ответы
С Приложения Microsoft 365 для предприятий приложение Excel на Windows для ПК предлагает все функции Power Query и Power Pivot, которые улучшают аналитику и моделирование, могут использовать расширенные возможности подключения к данным и эффективно обмениваться данными в организации. Дополнительные сведения о доступности Power Query и Power Pivot см. ниже и узнайте у ИТ-группы, поддерживает ли ваш текущий план Office эти функции.
Основные функции бизнес-аналитики
Основные функции аналитики доступны для Microsoft 365 подписки или Office 2016. К таким функциям относятся срезы, срезы, диаграммы и возможности модели данных. С помощью Power Query можно импортировать, формировать и объединять данные из файлов, баз данных и веб-сайтов. Поддержка базовой модели данных позволяет загружать данные в модель данных, автоматически обнаруживать связи, добавлять группировку по времени и создавать показатели DAX.
Дополнительные сведения о том, как получить наилучшие результаты, см. в этой Office.
Расширенные функции бизнес-аналитики
Расширенные возможности аналитики доступны в Приложения Microsoft 365 для предприятий, Office 2019 Professional, Office 2019 Professional Plus и Excel 2019. Помимо основных функций аналитики, с помощью Power Query можно импортировать, формировать и объединять данные из корпоративных, больших данных и облачных источников данных, а также делиться запросами. Поддержка расширенных моделей данных в Power Pivot включает специальный редактор моделирования данных, представления данных, вычисляемые столбцы DAX, ключевые показатели эффективности и иерархии.
Дополнительные сведения см. в источниках данных Power Query в Excel версияхи Где находится Power Pivot?
Excel 2016 для Mac и Excel 2019 для Mac имеют множество основных функций анализа, перечисленных выше: срезы, диаграммы и основные возможности импорта. У них еще нет Power Query или Power Pivot, но мы постоянно улучшаем Excel на Mac. Excel для Microsoft 365 для Mac предлагает ограниченную поддержку Power Query.
Дополнительные сведения см. в этой Excel для Mac.
Power Query обеспечивает быстрый и простой сбор и формирование данных. Power Query позволяет подключать, объединять и уточнять источники данных в нужных для анализа запросах. Power Query можно использовать в четыре этапа:
Подключение Импорт данных и подключение к данным в облаке, в службе или локально.
Преобразовать Формировать данные в нужном виде, а исходный источник остается без изменений.
Объединить Дополнительные формы данных путем их интеграции из нескольких источников для получения уникального представления данных.
Нагрузки Выполните запрос и сохраните его на компьютере или в модели данных.
Дополнительные сведения см. в справке по Power Query в Excel и Power Query для Excel.
Возможность Power Pivot предназначена для эффективного анализа и создания сложных моделей данных. С помощью Power Pivot можно работать с большими объемами данных из различных источников одновременно, быстро анализировать их и распространять результаты. Вы можете использовать модель данных и коллекцию таблиц, чтобы создавать связи, сохраняемые в книге. Модель данных тесно интегрирована с другими функциями Excel, например таблицами и сводными таблицами, чтобы обеспечивать удобный интерфейс анализа.
Дополнительные сведения см. в этой Microsoft Excel.
Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. С помощью Power BI Desktop вы можете подключаться к сотням источников данных, упрощать подготовку данных и выполнять специальный анализ. Вы также можете создавать отчеты и публиковать их в организации для использования в Интернете и на мобильных устройствах. Все пользователи могут создавать персонализированные информационные панели с использованием всестороннего представления организации. Имеются встроенные средства управления и обеспечения безопасности.
Excel 2016 и Power BI Desktop предоставляют уникальный для отрасли набор инструментов. Вместе они позволяют бизнес-аналитикам легко собирать, формировать, анализировать и наглядно изучать данные. Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. Он помогает всем сотрудникам организации объединять данные из различных источников: книг Excel, локальных корпоративных данных, служб Azure и других популярных облачных решений, таких как Salesforce.com или Marketo, чтобы просматривать и совместно использовать информационные панели и интерактивные отчеты.
Дополнительные сведения см. в Excel для Power BI.
Подписчики Microsoft 365 и Excel 2019 добавили улучшения в функции и функции, к которые вы уже привыкли.
Рекомендации по работе с Power Query
В этой статье содержатся советы и рекомендации по максимально эффективному использованию структурирование данных в Power Query.
Выбор правильного соединителя
Полный список доступных соединителей в Power Query см. в разделе соединители в Power Query.
Фильтрация на ранних этапах
Всегда рекомендуется фильтровать данные на ранних стадиях запроса или как можно раньше. Некоторые соединители будут использовать фильтры с помощью свертывания запросов, как описано в разделе Power Query свертывания запросов. Также рекомендуется отфильтровать любые данные, не относящиеся к вашему случаю. Это позволит лучше сосредоточиться на вашей задаче, отображая только данные, относящиеся к предварительной версии данных.
Можно использовать меню Автофильтр, в котором отображается отдельный список значений, найденных в столбце, чтобы выбрать значения для сохранения или фильтрации. Кроме того, панель поиска можно использовать для поиска значений в столбце.
Кроме того, можно воспользоваться специальными фильтрами для определенного типа, как в предыдущих столбцах даты, DateTime или даже даты.
Эти фильтры для конкретного типа помогают создать динамический фильтр, который всегда будет извлекать данные за последние x секунд, минут, часов, дней, недель, месяцев, кварталов или лет, как показано на следующем рисунке.
Дополнительные сведения о фильтрации данных на основе значений из столбца см. в разделе Фильтрация по значениям.
Проделать ресурсоемкие операции последними
Для выполнения определенных операций требуется чтение полного источника данных, чтобы возвращались результаты, и, таким же, будет выполняться слишком много времени для предварительного просмотра в редакторе Power Query. Например, если выполняется сортировка, то возможно, что первые несколько отсортированных строк находятся в конце исходных данных. Поэтому для возврата результатов операция сортировки должна сначала прочитать все строки.
Другие операции (например, фильтры) не требуют считывания всех данных перед возвратом результатов. Вместо этого они работают над данными в том виде, в котором они называются потоковой передачей. Данные передаются по потокам, а результаты возвращаются по своему пути. В редакторе Power Query такие операции должны считывать достаточное количество исходных данных для заполнения предварительной версии.
По возможности сначала выполните такие операции потоковой передачи, а затем выполните все более ресурсоемкие операции. Это позволит сократить время, затрачиваемое на ожидание подготовки к просмотру при каждом добавлении нового шага в запрос.
Временно работать с подмножеством данных
Если добавление новых шагов в запрос в редакторе Power Query выполняется слишком долго, сначала следует выполнить операцию «не выполнять первые строки» и ограничить количество строк, с которыми вы работаете. После добавления всех необходимых действий удалите шаг «не выполнять первые строки».
Использование правильных типов данных
Некоторые функции в Power Query являются контекстными для типа данных выбранного столбца. Например, при выборе столбца даты доступные параметры в группе столбцов даты и времени в меню Добавление столбца будут доступны. Но если для столбца не задан тип данных, эти параметры будут неактивны.
Аналогичная ситуация возникает для фильтров конкретного типа, так как они относятся к определенным типам данных. Если в столбце не определен правильный тип данных, то эти фильтры для конкретного типа будут недоступны.
Крайне важно всегда работать с правильными типами данных для столбцов. При работе с структурированными источниками данных, такими как базы данных, сведения о типах данных будут перенесены из схемы таблицы, найденной в базе данных. Но для неструктурированных источников данных, таких как TXT и CSV-файлы, важно задать правильные типы данных для столбцов, поступающих из этого источника данных. По умолчанию Power Query предлагает автоматическое обнаружение типов данных для неструктурированных источников данных. Вы можете ознакомиться с дополнительными сведениями об этой функции и о том, как она может помочь в работе с типами данных.
Дополнительные сведения о важности типов данных и способах работы с ними см. в разделе типы данных.
Изучение данных
Эти средства профилирования данных помогают лучше понять данные. Средства предоставляют небольшие визуализации, отображающие информацию по каждому столбцу, например:
Вы также можете взаимодействовать с этими функциями, которые помогут подготовить данные.
Дополнительные сведения о средствах профилирования данных см. в разделе средства профилирования данных.
Документирование работы
Рекомендуется документировать запросы путем переименования или добавления описания шагов, запросов или групп по своему усмотрению.
Хотя Power Query автоматически создает имя шага для вас на панели Примененные действия, можно также переименовать шаги или добавить описание к любому из них.
Дополнительные сведения о всех доступных компонентах и компонентах, найденных в области Примененные действия, см. в разделе Использование списка примененных действий.
Создание модульного подхода
Можно полностью создать один запрос, содержащий все преобразования и вычисления, которые могут потребоваться. Но если запрос содержит большое количество шагов, целесообразно разбить запрос на несколько запросов, где один запрос ссылается на следующий. Цель этого подхода — упростить и разделить этапы преобразования на небольшие части, чтобы их было проще понять.
Например, предположим, что у вас есть запрос с девятью шагами, показанными на следующем рисунке.
После этого появится диалоговое окно с предложением ввести имя для нового запроса. Это позволит эффективно разделить запрос на два запроса. Один запрос будет иметь все запросы перед слиянием. Другой запрос будет иметь начальный шаг, который будет ссылаться на новый запрос, и остальные шаги, которые были выполнены в исходном запросе, из шага объединить с таблицей цен вниз.
Можно также использовать ссылки на запросы в том виде, в котором они отображаются. Но рекомендуется размещать запросы на уровне, который на первый взгляд не кажется очень полезным с учетом множества шагов.
Дополнительные сведения о ссылках на запросы см. в разделе Общие сведения о панели запросов.
Создание групп
Хорошим способом сохранения работы является использование групп на панели «запросы».
Единственная цель групп — помочь вам организовать работу, выполнив в качестве папок для запросов. Вы можете создавать группы в группах, если вам когда-либо нужно. Перемещение запросов между группами так же просто, как и перетаскивание.
Попробуйте предоставить группам осмысленное имя, которое имеет смысл для вас и вашего случая.
Дополнительные сведения о всех доступных компонентах и компонентах, найденных в области запросы, см. в разделе Общие сведения о панели запросов.
Запросы будущего подтверждения
Создание запроса, который не будет иметь каких-либо проблем во время следующего обновления, является высшим приоритетом. В Power Query есть несколько функций, чтобы обеспечить устойчивость запроса к изменениям и возможность обновления даже при изменении некоторых компонентов источника данных.
Рекомендуется определить область запроса в соответствии с тем, что он должен делать, и о том, к чему он должен учитывать, с точки зрения структуры, макета, имен столбцов, типов данных и любого другого компонента, относящегося к области.
Ниже приведены некоторые примеры преобразований, которые могут помочь сделать запрос устойчивым к изменениям.
Дополнительные сведения о фильтрации данных по положению строки см. в разделе Фильтрация таблицы по положению строки.
Дополнительные сведения о выборе или удалении столбцов см. в разделе Выбор или удаление столбцов.
Дополнительные сведения о параметрах отмены свертывания столбцов см. в разделе Отмена свертываниястолбцов.
Если в запросе имеется шаг, который изменяет тип данных столбца, но некоторые ячейки выдают ошибки, так как значения не соответствуют требуемому типу данных, можно удалить строки, которые выдавали значения ошибок.
Дополнительные сведения о работе и работе с ошибками см. в разделе Работа с ошибками.
Использование параметров
Рекомендуется создавать динамические и гибкие запросы. Параметры в Power Query помогают сделать запросы более динамичными и гибкими. Параметр служит для простого хранения и управления значением, которое может многократно использоваться различными способами. Но чаще всего используется в двух сценариях:
Аргумент Step — Параметр можно использовать в качестве аргумента для нескольких преобразований, управляемых из пользовательского интерфейса.
Аргумент — пользовательской функции Можно создать новую функцию на основе запроса и указать параметры в качестве аргументов пользовательской функции.
Ниже приведены основные преимущества создания и использования параметров.
Многократное использование параметра в нескольких шагах или запросах.
Делает создание пользовательских функций простым и простым.
Можно даже использовать параметры в некоторых аргументах соединителей данных. например, можно создать параметр для имени сервера при подключении к базе данных SQL Server. затем можно использовать этот параметр в диалоговом окне SQL Server базы данных.
При изменении расположения сервера необходимо всего лишь обновить параметр для имени сервера, и ваши запросы будут обновлены.
Дополнительные сведения о создании и использовании параметров см. в разделе Использование параметров.
Создание многократно используемых функций
Если Вы найдетесь в ситуации, когда необходимо применить тот же набор преобразований к разным запросам или значениям, создайте Power Query настраиваемую функцию, которую можно использовать многократно, как можно более эффективно. Power Query пользовательской функцией является сопоставление набора входных значений с одним выходным значением и создается из собственных функций и операторов M.
Например, предположим, что имеется несколько запросов или значений, требующих одинакового набора преобразований. Можно создать пользовательскую функцию, которую позже можно будет вызвать для запросов или выбранных значений. Эта пользовательская функция экономит время и помогает управлять набором преобразований в центральном расположении, которое можно изменить в любой момент.
Power Query пользовательские функции можно создавать на основе существующих запросов и параметров. Например, представьте запрос, содержащий несколько кодов в виде текстовой строки, и необходимо создать функцию, которая будет декодировать эти значения.
Начните с параметра со значением, которое служит примером.
Из этого параметра вы создадите новый запрос, в котором будут применяться необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-слабый на несколько компонентов:
Затем можно преобразовать этот запрос в функцию, щелкнув запрос правой кнопкой мыши и выбрав создать функцию. Наконец, можно вызвать пользовательскую функцию в любой запрос или значения, как показано на следующем рисунке.
После нескольких преобразований можно увидеть, что вы достигли желаемого результата и использовали логику для такого преобразования из пользовательской функции.
Дополнительные сведения о создании и использовании пользовательских функций в Power Query из статьи пользовательские функции.