Power Query Язык М №12. Как Power Query работает с таблицами

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Не сверху вниз

Посмотрите на этот запрос:

let
    filepath = Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1],
    src =
      Csv.Document(File.Contents(filepath & "bike_sales.csv"),
                   [Delimiter=";"]),
    tab_headers =
      Table.PromoteHeaders(src,
                           [PromoteAllScalars=true]),
    tab_types =
      Table.TransformColumnTypes(tab_headers,
                                 {{"order_date", type date},
                                  {"order_day", Int64.Type},
                                  {"customer_age", Int64.Type},
                                  {"order_year", Int64.Type},
                                  {"order_quantity", Int64.Type},
                                  {"unit_cost", Int64.Type},
                                  {"unit_price", Int64.Type},
                                  {"profit", Int64.Type},
                                  {"cost", Int64.Type},
                                  {"revenue", Int64.Type}}),
    tab_select_rows_1 =
      Table.SelectRows(tab_types,
                       each ([customer_country] = "Canada")
                         and ([order_year] = 2013)
                         and ([product_category] = "Bikes")),
    tab_select_rows_2 =
      Table.FirstN(tab_select_rows_1,
                   30)
in
    tab_select_rows_2

Мы, люди, читаем этот код сверху вниз. Нам может показаться, что сначала в шаге src импортируется вся таблица целиком, потом в tab_headers поднимаем заголовок, потом в tab_types меняем типы данных, дальше фильтруем таблицу и оставляем только первые 30 строк.

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

Power Query использует технику Streaming. Эта техника позволяет намного эффективнее использовать память. В данном примере Power Query не будет импортировать весь датасет в шаге src, а сразу перейдет к шагу tab_select_rows_1. В этом шаге PQ будет построчно проверять соответствует ли строка условиям фильтрации. Таким образом пропадает необходимость в хранении всей исходной таблицы в памяти.

Хранение в памяти

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

let
    filepath = Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1],
    src =
      Csv.Document(File.Contents(filepath & "bike_sales.csv"),
                   [Delimiter=";"]),
    tab_headers =
      Table.PromoteHeaders(src,
                           [PromoteAllScalars=true]),
    tab_types =
      Table.TransformColumnTypes(tab_headers,
                                 {{"order_date", type date},
                                  {"order_day", Int64.Type},
                                  {"customer_age", Int64.Type},
                                  {"order_year", Int64.Type},
                                  {"order_quantity", Int64.Type},
                                  {"unit_cost", Int64.Type},
                                  {"unit_price", Int64.Type},
                                  {"profit", Int64.Type},
                                  {"cost", Int64.Type},
                                  {"revenue", Int64.Type}}),
    tab_select_rows_1 =
      Table.SelectRows(tab_types,
                       each ([customer_country] = "Canada")
                         and ([order_year] = 2013)
                         and ([product_category] = "Bikes")),
    tab_sort =
      Table.Sort(tab_select_rows_1,
                 {{"profit", Order.Descending}}),
    tab_select_rows_2 =
      Table.FirstN(tab_sort,
                   30)
in
    tab_select_rows_2

В данном запросе переменная tab_sort сохраняет все строки из tab_select_rows_1, потом их сортирует и сохраняет результат сортировки.

Какие операции сохраняют строки таблицы? Это операции, которые меняют таблицу: сортировка, объединения по горизонтали, пивот/анпивот и, возможно, буферизация.

Рекомендация

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

Query Folding

Если бы вы выполняли запрос к базе данных, то он выглядел бы примерно так:

select *
from bike_sales
where customer_country = 'Canada'
  and order_year = 2013
  and product_category = 'Bikes'
order by profit desc
limit 3

Power Query умеет эффективно выполнять запросы к базам данных без необходимости вручную вводить код на языке SQL.

Например, вы запрашиваете данные из базы данных:

let
    src =
 some_database,

    tab_select_rows_1 =
      Table.SelectRows(src,
                       each ([customer_country] = "Canada")
                         and ([order_year] = 2013)
                         and ([product_category] = "Bikes")),
    tab_sort =
      Table.Sort(tab_select_rows_1,
                 {{"profit", Order.Descending}}),
    tab_select_rows_2 =
      Table.FirstN(tab_sort,
                   30)
in
    tab_select_rows_2

Power Query интерпретирует этот код как SQL запрос. Сам для себя он перевод этот код в SQL запрос и выполняет его намного быстрее на стороне сервера.

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

Буферизация

Если вам нужно сохранить стабильную таблицу или лист, то воспользуйтесь буферизацией с помощью функций:

• Table.Buffer
• List.Buffer

При первом вызове Table.Buffer сохраняет результат в памяти. При всех последующих обращениях к объекту будет использовать версия, хранящаяся в памяти.

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

let
    filepath = Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1],
    src =
      Csv.Document(File.Contents(filepath & "bike_sales.csv"),
                   [Delimiter=";"]),
    tab_headers =
      Table.PromoteHeaders(src,
                           [PromoteAllScalars=true]),
    tab_types =
      Table.TransformColumnTypes(tab_headers,
                                 {{"order_date", type date},
                                  {"order_day", Int64.Type},
                                  {"customer_age", Int64.Type},
                                  {"order_year", Int64.Type},
                                  {"order_quantity", Int64.Type},
                                  {"unit_cost", Int64.Type},
                                  {"unit_price", Int64.Type},
                                  {"profit", Int64.Type},
                                  {"cost", Int64.Type},
                                  {"revenue", Int64.Type}}),
    tab_select_rows_1 =
      Table.SelectRows(tab_types,
                       each ([customer_country] = "Canada")
                         and ([order_year] = 2013)
                         and ([product_category] = "Bikes")),
    tab_sort =
      Table.Sort(tab_select_rows_1,
                 {{"profit", Order.Descending}}),
    tab_select_rows_2 =
      Table.FirstN(tab_sort,
                   30)
in
    tab_select_rows_2

Power Query каким-то образом ограничивает память при сортировке. Это может привести к неверному результату. Чтобы получить ожидаемый результат применим функцию Table.Buffer.

let
    filepath = Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1],
    src =
      Csv.Document(File.Contents(filepath & "bike_sales.csv"),
                   [Delimiter=";"]),
    tab_headers =
      Table.PromoteHeaders(src,
                           [PromoteAllScalars=true]),
    tab_types =
      Table.TransformColumnTypes(tab_headers,
                                 {{"order_date", type date},
                                  {"order_day", Int64.Type},
                                  {"customer_age", Int64.Type},
                                  {"order_year", Int64.Type},
                                  {"order_quantity", Int64.Type},
                                  {"unit_cost", Int64.Type},
                                  {"unit_price", Int64.Type},
                                  {"profit", Int64.Type},
                                  {"cost", Int64.Type},
                                  {"revenue", Int64.Type}}),
    tab_select_rows_1 =
      Table.SelectRows(tab_types,
                       each ([customer_country] = "Canada")
                         and ([order_year] = 2013)
                         and ([product_category] = "Bikes")),
    tab_sort =
      Table.Buffer(Table.Sort(tab_select_rows_1,
                              {{"profit", Order.Descending}})),
    tab_select_rows_2 =
      Table.FirstN(tab_sort,
                   30)
in
    tab_select_rows_2

Курс по языку М

Номер урока	Урок	Описание
1	Power Query Язык М №1. Простые выражения, let	Что такое let, одна или несколько переменных в in, вложенные блоки let.
2	Power Query Язык М №2. Создание функций М	Функции без выражения let, функции внутри выражения let.
3	Power Query Язык М №3. Создание функций М 2	Функция как параметр функции, ключевое слово each.
4	Power Query Язык М №4. Переменные и идентификаторы	Идентификаторы с кавычками и без, допустимые и недопустимые идентификаторы.
5	Power Query Язык М №5. Текстовый тип данных	Особенности работы с текстовым типом данных.
6	Power Query Язык М №6. Числовой тип данных (Ввод, Сравнение, Точность, Бесконечность)	В этом уроке мы узнаем, что из себя представляет числовой тип данных.
7	Power Query Язык М №7. Дата, время, длительность и прочее(date, datetime, time, datetimezone)	Как создать значение с типом данных #date, #time, #datetime, #datetimezone, #duration. Преобразование значений одного типа данных в другой, арифметические операции, тонкости.
8	Power Query Язык М №8. Типы данных Logical, Null	Изучим логический тип данных и значение null.
9	Power Query Язык М №9. Объект Record / Запись	Что такое объект Record / Запись. Как создать запись, как сослаться на значения записи, как сравнивать записи, как объединять записи, как изменять записи.
10	Power Query Язык М №10. Объект List / Список	Зачем нужны списки, создание списков, сравнение списков, функции для работы со списками.
11	Power Query Язык М №11. Объект Table / Таблица	Получить строку, столбец. Объединение таблиц по вертикали.
12	Power Query Язык М №12. Как PQ работает с таблицами	В этом уроке вы узнаете как думает Power Query.
13	Power Query Язык М №13. Ключевое слово each	Зачем нужно ключевое слово each.