Файлы к уроку:
Ссылки:
Описание
Есть операция Fill Down. В этом уроке мы сделаем обратное действие. Мы отменим заполненный вниз столбец и вернем null.
Решение
Ключевые операции для решения этой задачи: группировка и индекс. Сначала мы выполним операцию группировки, свернув таблицу. Потом пронумеруем строки свернутой таблицы. Дальше останется только развернуть табличный столбец и заполнить значениями null все, кроме первой строки каждой группы.
Код
let
src =
Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
tab_group =
Table.Group(src,
{"Тип населения",
"Возрастная группа"},
{{"tab",
each _,
type table [Тип населения=text,
Возрастная группа=text,
Год=number,
Значение=number]}}),
col_replace_val_1 =
Table.ReplaceValue(tab_group,
each [tab],
each Table.AddIndexColumn([tab],
"row_num",
1,
1),
Replacer.ReplaceValue,
{"tab"}),
col_expand =
Table.ExpandTableColumn(col_replace_val_1,
"tab",
{"Год",
"Значение",
"row_num"},
{"Год",
"Значение",
"row_num"}),
col_replace_val_2 =
Table.ReplaceValue(col_expand,
each [Тип населения],
each if [row_num] = 1
then [Тип населения]
else null,
Replacer.ReplaceValue,
{"Тип населения"}),
col_replace_val_3 =
Table.ReplaceValue(col_replace_val_2,
each [Возрастная группа],
each if [row_num] = 1
then [Возрастная группа]
else null,
Replacer.ReplaceValue,
{"Возрастная группа"})
in
col_replace_val_3
Примененные функции
- Table.Group
- Table.ReplaceValue
- Table.AddIndexColumn
- Replacer.ReplaceValue
- Table.ExpandTableColumn
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 вакансий. |