Файлы к уроку:
Ссылки:
Не сверху вниз
Посмотрите на этот запрос:
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. |