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

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

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

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

Использование оптимального соединителя для задачи обеспечивает лучший опыт и производительность. Например, использование соединителя SQL Server вместо соединителя ODBC при подключении к базе данных SQL Server не только обеспечивает более эффективный опыт получения данных, но и соединитель SQL Server также предлагает функции, которые могут улучшить ваш опыт и производительность, такие как свертка запросов. Дополнительные сведения о свертке запросов см. в статье "Обзор оценки запросов и свертывания запросов в 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 помогают сделать запросы более динамическими и гибкими. Параметр служит способом легко хранить и управлять значением, которое можно повторно использовать различными способами. Но чаще используется в двух сценариях:

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

  

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

  

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

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

  

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

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

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

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

Замечание

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

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

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

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

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

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

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

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

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

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

Замечание

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