Рекомендации по работе с Power Query

Эти рекомендации по работе с Power Query помогут вам повысить производительность запросов, эффективно использовать свертывание запросов, правильно выбирать типы данных, упорядочивать преобразования и повторно использовать логику с помощью параметров и пользовательских функций. Они применяются как к Power Query Desktop, так и к Power Query Online.

Выбор правильного соединителя

Power Query предлагает множество соединителей данных. Эти соединители варьируются от источников данных, таких как TXT, CSV и Excel файлы, к базам данных, таким как Microsoft SQL Server, и популярным программным обеспечением как услуга (SaaS), таким как Microsoft Dynamics 365 и Salesforce. Если встроенный соединитель недоступен в окне получения данных , используйте универсальный соединитель, например ODBC или OLE DB.

Выберите специально созданный соединитель для источника данных, если он доступен. Например, соединитель SQL Server обеспечивает лучший интерфейс получения данных, чем универсальный соединитель ODBC при подключении к базе данных SQL Server. Соединитель SQL Server также поддерживает такие функции производительности, как свертывание запросов. Дополнительные сведения см. в статье "Обзор оценки запросов и свертывания запросов в Power Query".

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

Замечание

Чтобы просмотреть полный список доступных соединителей в Power Query, перейдите к соединителям в Power Query.

Фильтруйте данные на раннем этапе, чтобы повысить производительность

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

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

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

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

Замечание

Дополнительные сведения о фильтрации данных на основе значений из столбца см. в разделе "Фильтр по значениям".

Выполняйте ресурсоёмкие операции в последнюю очередь для повышения производительности

Чтобы повысить производительность предварительной версии в редакторе Power Query, выполните дорогостоящие операции в последний раз. Для некоторых операций требуется чтение всего источника данных, чтобы вернуть какие-либо результаты, поэтому их предварительный просмотр выполняется медленно. Например, если выполнить сортировку, возможно, первые несколько отсортированных строк находятся в конце исходных данных. Чтобы вернуть результаты, операция сортировки должна сначала считывать все строки.

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

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

Использование подмножества данных при разработке запроса

Если добавление новых шагов в редакторе Power Query выполняется медленно, используйте Сохранить первые строки, чтобы ограничить объём обрабатываемых данных при разработке запроса. После добавления всех необходимых шагов удалите шаг "Сохранить первые строки" , чтобы завершенный запрос обрабатывал полный набор данных.

Использование правильных типов данных

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

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

Важно всегда работать с правильными типами данных для столбцов. При работе с структурированными источниками данных, такими как базы данных, сведения о типе данных передаются из схемы таблицы, найденной в базе данных. Но для неструктурированных источников данных, таких как TXT и CSV-файлы, важно задать правильные типы данных для столбцов, поступающих из этого источника данных. По умолчанию Power Query предлагает автоматическое обнаружение типов данных для неструктурированных источников данных. Дополнительные сведения об этой функции и о том, как она может помочь вам с типами данных.

Замечание

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

Профилирование и изучение данных

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

Power Query предоставляет три средства профилирования данных:

инструмент	Что это показывает
Качество столбцов	Доля значений в столбце, которые допустимы, содержат ошибки или пусты.
Распределение столбцов	Частота и распределение значений в каждом столбце.
Профиль столбца	Подробная статистика по выбранному столбцу.

Вы также можете взаимодействовать с этими функциями, что помогает подготовить данные.

Замечание

Дополнительные сведения о средствах профилирования данных см. в разделе "Средства профилирования данных".

Документируйте работу

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

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

Замечание

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

Разделение больших запросов на модули

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

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

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

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

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

Замечание

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

Сгруппировать запросы

Используйте группы на панели запросов, чтобы упорядочить свою работу.

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

Попробуйте дать группам понятное имя, которое имеет смысл для вас и вашего дела.

Замечание

Дополнительные сведения обо всех доступных функциях и компонентах, найденных в области запросов, см. в разделе "Общие сведения о области запросов".

Запросы, подтверждающие будущее

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

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

Следующие преобразования могут помочь запросу оставаться устойчивыми к изменениям:

Сценарий с исходными данными	Преобразование Power Query	Узнать больше
Количество строк данных меняется, но необходимо удалить фиксированное количество строк в нижнем колонтитуле.	Удаление нижних строк	Фильтрация таблицы по позиции строки
Количество столбцов изменяется, но запрос требует только определенных столбцов.	Выбор столбцов	Выбор или удаление столбцов
Количество столбцов меняется, но запрос должен выполнять операцию UNPIVOT только для определённого подмножества.	Разворачивание только выбранных столбцов	Отмена сводных столбцов
Преобразование типа данных создает ошибки для значений, которые не соответствуют целевому типу.	Удалите строки, содержащие ошибки.	Работа с ошибками

Использование параметров

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

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

  

• Аргумент пользовательской функции: создайте новую функцию на основе запроса и используйте параметры в качестве аргументов пользовательской функции.

  

Основными преимуществами создания и использования параметров являются:

• Централизованное представление всех параметров с помощью окна "Управление параметрами ".

  

• Повторное использование параметра в нескольких шагах или запросах.

• Упрощает и облегчит создание пользовательских функций.

Можно даже использовать параметры в некоторых аргументах соединителей данных. Например, можно создать параметр для имени сервера при подключении к базе данных SQL Server. Затем этот параметр можно использовать в диалоговом окне базы данных SQL Server.

Если изменить расположение сервера, необходимо обновить параметр для имени сервера, а запросы обновляются.

Замечание

Дополнительные сведения о создании и использовании параметров см. в разделе "Использование параметров".

Создание повторно используемых функций

Создайте Power Query настраиваемую функцию, если необходимо применить один набор преобразований к разным запросам или значениям. Пользовательская функция Power Query сопоставляет набор входных значений с одним выходным значением и создается с помощью встроенных функций и операторов языка формул M Power Query.

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

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

Сначала у вас есть параметр со значением, которое служит примером.

В этом параметре создается новый запрос, в котором применяются необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-LAX на несколько компонентов:

• Источник = PTY
• пункт назначения = LAX
• Авиалиния = CM
• Идентификатор рейса = 1090

Затем этот запрос можно преобразовать в функцию, щелкнув правой кнопкой мыши запрос и выбрав "Создать функцию". Наконец, вы можете вызвать свою пользовательскую функцию в любом из своих запросов или значений.

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

Замечание

Дополнительные сведения о создании и использовании пользовательских функций в Power Query см. в разделе "Пользовательские функции".