Как ускорить работу power query

Как ускорить работу power query

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Как увеличить скорость работы в Power Query?

Бывают такие ситуации, когда в редактор Power BI вы загружаете большие объемы данных, например, таблицы с миллионами строк. Если у вас в редакторе запросов (Power Query) таких запросов много, и вы пытаетесь производить с ними определенные действия: обрабатывать, объединять, добавлять, то Power Query может «подтормаживать», долго обрабатывать информацию, тем более если ваш компьютер или ноутбук слабоваты и это в целом ведет к увеличению времени работы.

На этот процесс можно повлиять, и я хочу поделиться таким советом: нужно временно ограничить число строк в конкретном запросе.

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

Вот как это реализовывается на практике:

Редактор запросов Power Query:

На главной странице нажимаем «Изменить запросы»

В данной таблице 996 строк. Для примера ограничим таблицу 20 строками.

В основном поле, в левом верхнем углу правой кнопкой мыши нажимаем на значок на пересечении столбцов и строк.

В появившемся меню нажимаем «Сохранить верхние строки…».

Затем в открывшемся окне указываем цифру 20.

Наша таблица уменьшилась до 20 строк.

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

Для примера, создадим шаг, состоящий из нового столбца.

На вкладке «Добавление столбца» нажимаем «Настраиваемый столбец»

Оптимизация Power Query при расширении столбцов таблицы

Простота и простота использования, которая позволяет пользователям Power BI быстро собирать данные и создавать интересные и мощные отчеты, чтобы принимать интеллектуальные бизнес-решения, также позволяет пользователям легко создавать плохо выполняемые запросы. Это часто происходит при наличии двух таблиц, связанных с внешним ключом, связанных с таблицами SQL или списками SharePoint. (Для записи эта проблема не относится к SQL или SharePoint и возникает во многих сценариях извлечения внутренних данных, особенно в тех случаях, когда схема является гибкой и настраиваемой.) Кроме того, нет ничего неправильного в хранении данных в отдельных таблицах, которые совместно используют общий ключ. На самом деле это фундаментальный принцип проектирования и нормализации базы данных. Но это означает лучший способ расширения отношений.

Рассмотрим следующий пример списка клиентов SharePoint.

И в следующем списке расположений он ссылается.

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

Эти данные верхнего уровня собираются через один http-вызов API SharePoint (игнорируя вызов метаданных), который можно увидеть в любом веб-отладчике.

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

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

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

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

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

Во-вторых, загрузите вторичную таблицу, включив идентификатор, который является внешним ключом. Щелкните правой кнопкой мыши панель «Запросы», чтобы создать новый запрос.

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

В этом примере можно увидеть, что LocationId в первичном списке соответствует идентификатору в дополнительном списке. Пользовательский интерфейс переименовывает это в Location.Id , чтобы сделать имя столбца уникальным. Теперь давайте будем использовать эти сведения для слияния таблиц.

Щелкнув правой кнопкой мыши панель запросов и выбрав «Создать запросы объединения>запросов» в качестве новых>, вы увидите удобный пользовательский интерфейс, который поможет объединить эти два запроса.

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

Выбрав обе таблицы, выберите столбец, который объединяет таблицы логически (в этом примере — LocationId из первичной таблицы и идентификатора из вторичной таблицы). В диалоговом окне вы узнаете, сколько строк совпадает с внешним ключом. Скорее всего, вы захотите использовать тип соединения по умолчанию (левый внешний) для таких данных.

Нажмите кнопку «ОК «, и вы увидите новый запрос, который является результатом соединения. Расширение записи теперь не подразумевает дополнительные вызовы серверной части.

Обновление этих данных приведет только к двум вызовам SharePoint — одному для основного списка и одному для дополнительного списка. Соединение будет выполнено в памяти, значительно уменьшая количество вызовов в SharePoint.

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

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

Power Query Ускорение №4. Ускорить запрос уменьшив количество шагов с помощью Table.TransformColumns

Ваш запрос Power Query работает слишком медленно. Как быть?

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

Попробуем очистить эту таблицу всего за 2 шага:

Решение

Чтобы уменьшить количество шагов мы воспользуемся функцией Table.TransformColumns. Она позволит нам одной формулой преобразовать данные сразу во всех столбцах.

Примененные функции

• Table.TransformColumns
• Date.FromText
• Text.End
• Text.Remove
• Text.Trim
• Number.FromText

Код

let src = Excel.CurrentWorkbook()<[Name = "Таблица1"]>[Content], cols_transform = Table.TransformColumns( src, < , ), type text>, ), type text>, > ) in cols_transform

Курс Ускорения запросов Power Query

Номер урока	Урок	Описание
1	Power Query Ускорение запроса №1. Удаляем лишние шаги	На реальном жизненном примере мы разберем как ускорить запрос удаляя лишние шаги.
2	Power Query Ускорение запроса №2. Выбираем правильный формат файла	Какой формат файла выбрать, чтобы запрос обрабатывался быстрее: XLS, XLSX, CSV?
3	Power Query Ускорение запроса №3. Уменьшить количество подключений	Уменьшение количества подключений позволит навести порядок в ваших запросах.
4	Power Query Ускорение запроса №4. Уменьшить количество шагов с Table.TransformColumns	Уменьшаем количество шагов с помощью функции Table.TransformColumns.

Power Query Ускорение №4. Ускорить запрос уменьшив количество шагов с помощью Table.TransformColumns was last modified: 2 июня, 2022 by Admin