Файлы к уроку:
Ссылки:
Описание
Существует табличный столбец. Нам нужно отфильтровать табличный столбец с использованием другого столбца.
В нашем случае нужно отфильтровать столбец «Скидка» с использованием столбца «Дата».
Если мы просто попытаемся воспользоваться функцией Table.SelectRows следующим образом, то получим ошибку Expression.Error:
Table.SelectRows([Скидка], each [Дата] >= [Скидка][#»Акция — Дата С»])
Решение
Ошибка Expression.Error возникает в данном случае из-за того, что формула просто не видит столбец [Дата]. Мы работаем с таблицей [Скидка] и видим только столбцы этой таблицы.
Чтобы отфильтровать таблицу [Скидка] с использованием столбца [Дата] нужно воспользоваться конструкцией let — in. Значения столбца [Дата] нужно сохранить в отдельную переменную:
let
d = [Дата]
in
Table.SelectRows([Скидка], each d >= [#"Акция - Дата С"])
Код
Подключение к файлу Excel:
let
src = Table.SelectRows(
Excel.Workbook(
File.Contents(
Excel.CurrentWorkbook(){[Name = "path"]}[Content]{0}[Column1] & "\some_sales.xlsx"
)
),
each [Kind] = "Table"
)[[Name], [Data]],
tab_rename = Table.RenameColumns(src, {{"Data", "Value"}}),
rec = Record.FromTable(tab_rename)
in
rec
Таблица dim_products:
let
src = connection[dim_products],
tab_sort = Table.Sort(
src,
{
{"Продукт", Order.Ascending},
{"Акция - Дата С", Order.Ascending},
{"Скидка, %", Order.Ascending}
}
),
tab_idx = Table.AddIndexColumn(tab_sort, "Индекс", 0, 1, Int64.Type)
in
tab_idx
Таблица dim_clients:
connection[dim_clients]
Таблица fct_sales:
let
src = connection[fct_sales],
tab_join = Table.NestedJoin(
src,
{"Клиент"},
dim_clients,
{"Наименование"},
"dim_clients",
JoinKind.LeftOuter
),
col_expand = Table.ExpandTableColumn(tab_join, "dim_clients", {"Тип"}, {"Тип клиента"}),
tab_join_2 = Table.NestedJoin(
col_expand,
{"Продукт", "Тип клиента"},
dim_products,
{"Продукт", "Тип Клиента"},
"Скидка",
JoinKind.LeftOuter
),
col_transform = Table.ReplaceValue(
tab_join_2,
each [Скидка],
each
let
d = [Дата]
in
Table.SelectRows([Скидка], each d >= [#"Акция - Дата С"]),
Replacer.ReplaceValue,
{"Скидка"}
),
col_transform_2 = Table.ReplaceValue(
col_transform,
each [Скидка],
each try Table.Last([Скидка])[#"Скидка, %"] otherwise 0,
Replacer.ReplaceValue,
{"Скидка"}
),
col_transform_3 = Table.TransformColumns(
col_transform_2,
{{"Скидка", each _ * 0.01, type number}}
),
tab_add_col = Table.AddColumn(
col_transform_3,
"Сумма, итог",
each [#"Сумма, без скидки"] * [Скидка] + [#"Сумма, без скидки"]
)
in
tab_add_col
Примененные функции
- Table.SelectRows
- Table.RenameColumns
- Record.FromTable
- Table.NestedJoin
- JoinKind.LeftOuter
- Table.ExpandTableColumn
- Table.ReplaceValue
- Replacer.ReplaceValue
- Table.Last
- Table.TransformColumns
- Table.AddColumn
- Table.Sort
- Order.Ascending
- Table.AddIndexColumn
- Int64.Type
Power Query Разное
Номер урока | Урок | Описание |
---|---|---|
1 | Power Query. Знакомство с Power Query | В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос |
2 | Power Query. Подключение XML | В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel. |
3 | Power Query. Уникальные значения двух столбцов | В этом уроке мы получим уникальные значения из двух столбцов таблицы. |
4 | Power Query. Импорт таблиц PDF | Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет. |
5 | Power Query. Собрать разбитую строку | В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала. |
6 | Power Query. Пивот со счетом | В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы. |
7 | Power Query. Минимальное значение в диапазоне | В этом уроке мы найдем минимальное значение в диапазоне строк. |
8 | Power Query. Нарастающий итог 2 | В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query. |
9 | Power Query. Нарастающий итог 3 | В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query. |
10 | Power Query. Прирост населения Китая | В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет. |
11 | Power Query. Повторяющиеся значения в строке | В этом уроке разберем как определить есть ли в строке повторения. |
12 | Power Query. Таблица навигации по функциям М | В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query. |
13 | Power Query. Удалить запросы и модель данных из книги | Разберем как быстро удалить все запросы и модель данных из текущей книги. |
14 | Power Query. Открыть еще 1 Excel и еще 3 трюка | В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query. |
15 | Power Query. Подключиться к ZIP архиву | Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве. |
16 | Power Query. Импорт Word | Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками. |
17 | Power Query. Фильтрация списком | В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров. |
18 | Power Query. Пользовательская функция Switch | В этом уроке мы создадим пользовательскую функцию Switch. |
19 | Power Query. Информация о формате, Чтение zip | В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query. |
20 | Power Query. Импорт данных из gz | В этом уроке мы разберем как импортировать файл в формате gz. |
21 | Power Query. Удалить лишние пробелы, Text.Split | В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы. |
22 | Power Query. Параметры в SQL-запросе | Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel. |
23 | Power Query. Параметры в SQL-запросе 2 | Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать. |
24 | Power Query. Добавить столбец в каждую таблицу табличного столбца | В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца. |
25 | Power Query. Интервальный просмотр 1 (ВПР 1) | Объединить 2 таблицы с интервальным просмотром = 1. |
26 | Power Query. Относительный путь к файлу и папке | Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере. |
27 | Power Query. Нарастающий итог в каждой категории | Применим функцию нарастающего итога не ко всей таблице, а к определенному окну. |
28 | Power Query. ВПР без Merge или Join | Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц. |
Номер урока | Урок | Описание |
---|---|---|
29 | Power Query. Создать N копий выбранного столбца | Создадим функцию, чтобы пользователь мог указать нужное количество копий. |
30 | Power Query. Текст содержит подстроку из списка (Text.ContainsAny) | Нам нужно, чтобы отфильтровались строки, в которых содержится любая подстрока из списка. |
31 | Power Query. Генерация таблицы для веб-запросов | С помощью List.Generate сгенерируем таблицу для запросов к API |
32 | Power Query. Фильтрация табличного столбца с помощью столбца таблицы | Нужно отфильтровать табличный столбец с помощью другого столбца. |
33 | Power Query. Количество вхождений подстроки | Нужно посчитать сколько раз подстрока входит в строку. |
34 | Power Query. Скученные данные 4 | Еще один интересный пример работы со скученными данными. |
35 | Power Query. Антизаполнение / Unfill | То же самое, что Fill Down, но наоборот: вместо значений вернуть null. |
36 | Power Query. Получить код из текста | Есть текстовый файл с М-кодом. Как подключиться к файлу и выполнить этот код. |
37 | Power Query. Повторить строку N раз | Если строку нужно повторить определенное количество раз. |
38 | Power Query. Обработка PDF выписки | PDF выписку с результатами медицинских анализов преобразуем в таблицу. |
39 | Power Query. Объединить по вертикали файлы из разных папок | Папки с нужными файлами находятся в разных места. |
40 | Power Query. Объединение со знаками сравнения | Объединить таблицы по горизонтали со знаками сравнения. |
41 | Power Query. ABC анализ по категориям | ABC анализ отдельно для каждой категории. |
42 | Power Query. Более 2000 вакансий на лист Excel (API HH) | Дополним урок о получении вакансий HH. Разберем как получить более 2000 вакансий. |
43 | Power Query. Получить данные из Ice and Fire API | Учимся работать с API на примере Ice and Fire API. |