Файлы к уроку:
Ссылки:
Описание
Вам нужно из одной таблицы вставить данные столбца другой таблицы, т. е. то, что на листе Excel можно решить с помощью функции ВПР. В Power Query обычно для подобных задач применяется операция объединения таблиц по горизонтали или Merge / Join, но в этом уроке вы узнаете как можно сделать иначе.
Решение
В этом примере у нас будет 2 таблицы. Первая таблица называется sales:
Нам нужно из таблицы ticket_types вставить столбец с ценой билета:
Можно просто в таблицу sales добавить столбец со ссылкой на определенную ячейку таблицы ticket_types:
tab_select_rows_cols{[Item="ticket_types"]}[Data]{[ticket_type=[ticket_type]]}[ticket_price]
Код
let
src =
Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="path"]}[Content]{0}[Column1]),
null,
true),
tab_select_rows_cols =
Table.SelectRows(src,
each [Kind] = "Table")[ [Item], [Data] ],
sales =
tab_select_rows_cols{[Item="sales"]}[Data],
col_price =
Table.AddColumn(sales,
"ticket_price",
each tab_select_rows_cols{[Item="ticket_types"]}[Data]{[ticket_type=[ticket_type]]}[ticket_price]),
col_length =
Table.AddColumn(col_price,
"movie_length",
each tab_select_rows_cols{[Item="movies"]}[Data]{[movie_title=[movie_title]]}[movie_length]),
col_revenue =
Table.AddColumn(col_length,
"revenue",
each [ticket_quantity] * [ticket_price]),
tab_group =
Table.Group(col_revenue,
{"movie_title", "movie_length"},
{{"sum_revenue",
each List.Sum([revenue]),
type number}}),
col_revenue_per_minute =
Table.AddColumn(tab_group,
"revenue_per_minute",
each [sum_revenue] / [movie_length]),
tab_sort =
Table.Sort(col_revenue_per_minute,
{{"revenue_per_minute",
Order.Descending}})
in
tab_sort
Примененные функции
- Table.SelectRows
- Table.AddColumn
- Table.Group
- List.Sum
- Table.Sort
- Order.Descending
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. |