Файлы к уроку:
Ссылки:
Описание
В этом уроке вы узнаете, что из себя представляет объект List в Power Query. Вы узнаете как создавать разные виды листов с разным наполнением.
В этом уроке мы изучим/повторим:
- Создание списка дат с List.Dates
- Создать список чисел идущих подряд
- Создать список букв подряд
- Создать список чисел определенной длины с определенным шагом
- Создать список месяцев
- Найти в списке из текстовых значений определенный текс
- Преобразование листа с помощь List.Transform
- Среднее значение в листе с List.Average
Каждый столбец таблицы является списком. Если мы сошлемся на столбец, то результатом получится объект List.
Создать список из букв от A до Z
Аналогичным образом мы можем создать и список из маленьких букв.
{"A".."Z"}
Создать список чисел, идущих подряд
Такой способ позволяет создавать список из чисел, идущих подряд.
{2..72}
Создать список из чисел с заданным шагом
Первый параметр — это начало списка, второй параметр — это количество чисел, а третий параметр — это размер шага.
List.Numbers(1,10,2)
Создать список дат
В данном примере мы создаем список из дат. Первый параметр — начало списка. Второй параметр — это количество элементов в списке. В данном случае мы хотим создать список с 2021.01.01 по сегодняшний день. Во втором параметре мы из сегодняшнего дня вычитаем первое января 2021, чтобы получить нужное количество дней. Третий параметр — это размер шага. Он задается с помощью функции #duration.
List.Dates(
#date(2021, 1, 1),
Number.From(DateTime.LocalNow() - #datetime(2021, 1, 1, 0, 0, 0)),
#duration(1, 0, 0, 0)
)
Создадим список из названий месяцев
Сначала создадим список 1 — 12, превратим его в таблицу, добавим столбец, заполненный единицами и еще столбец, заполненный годом. Далее мы объединим полученные столбцы в один и назначим тип данных дата. После этого значения из полученного столбца преобразуем в название месяца.
let
source = {1 .. 12},
tab_from_list = Table.FromList(
source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
tab_add_col_1 = Table.AddColumn(
tab_from_list,
"Пользовательская",
each 1,
type text
),
tab_add_col_2 = Table.AddColumn(
tab_add_col_1,
"Пользовательская.1",
each 2019,
type text
),
cols_types = Table.TransformColumnTypes(
tab_add_col_2,
{
{"Column1", type text},
{"Пользовательская", type text},
{"Пользовательская.1", type text}
}
),
cols_combine = Table.CombineColumns(
cols_types,
{"Пользовательская", "Column1", "Пользовательская.1"},
Combiner.CombineTextByDelimiter(".", QuoteStyle.None),
"Месяц"
),
col_transform_1 = Table.TransformColumns(
cols_combine,
{{"Месяц", each Date.From(_), type date}}
),
col_transform_2 = Table.TransformColumns(
col_transform_1,
{{"Месяц", each Date.MonthName(_), type text}}
)
in
col_transform_2
Найдем в столбце значения по условию и получим результат в виде списка
В данном примере в столбце исходной таблицы мы хотим найти все значения с подстрокой «Apple».
let
get_table = Excel.CurrentWorkbook(){[Name = "Телефоны"]}[Content],
get_list = List.FindText(get_table[Таблица], "Apple")
in
get_list
Среднее значение в списке
List.Average({1,2,3,5,4,1,2,3,6,5,1,3})
Примененные функции
- List.Numbers
- List.Dates
- Number.From
- DateTime.LocalNow
- #datetime
- #duration
- Table.FromList
- Splitter.SplitByNothing
- ExtraValues.Error
- Table.AddColumn
- Table.TransformColumnTypes
- Table.CombineColumns
- Combiner.CombineTextByDelimiter
- QuoteStyle.None
- Table.TransformColumns
- Date.From
- Date.MonthName
- List.FindText
- List.Average
Этот урок входит в Продвинутый курс Power Query
Номер урока | Урок | Описание |
---|---|---|
1 | Power Query Продвинутый №1. Введение в Язык М 1 | В этом уроке мы узнаем, почему стоит изучать формулы М в Power Query. Какие преимущества нам дает знание языка формул М. |
2 | Power Query Продвинутый №2. Введение в Язык М 2 | В этом втором вводном уроке по языку формул Power Query мы познакомимся с самыми основами языка форму М. |
3 | Power Query Продвинутый №3. Уровни владения Языком М | В этом уроке мы узнаем зачем изучать Power Query. Какой процент задач вы сможете решить только с пользовательским интерфейсом, а какой процент вы сможете решить с использованием функций М? Об этом узнаем в это видео. |
4 | Power Query Продвинутый №4. Объекты Power Query | В этом уроке мы познакомимся с объектами Power Query: Table, List, Record. |
5 | Power Query Продвинутый №5. Объект List 1 | В этом уроке мы начнем знакомиться с объектом List в Excel Power Query. Создадим простые List (списки) из чисел и букв. Познакомимся с функциями для создания листов/списков из чисел и дат. Научимся создавать список названий месяцев. Изучим функцию List.FindText. Создадим лист с условием при помощи List.Transform. Найдем среднее значение в листе/списке при помощи простой функции List.Average. |
6 | Power Query Продвинутый №6. Объект List 2 | В этом уроке мы продолжим знакомиться с объектом List. Мы узнаем как создавать листы, как ссылаться на элементы листа и изучим несколько полезных формул листа. |
7 | Power Query Продвинутый №7. Объект Record (Запись) | В этом уроке немного подробнее изучим объект Record в Power Query. Record — это набор пар ключ-значение. Каждый Record — это строка таблицы. В этом уроке мы изучим ссылки на Record и функции Record. |
8 | Power Query Продвинутый №8. Объект Table (Таблица) | В этом уроке мы познакомимся с объектом Table в Power Query. Что это такое и какие основные функции применяются к этому объекту. |
9 | Power Query Продвинутый №9. Ссылки 1 | В этом уроке из курса по Power Query мы повторим как ссылаться на строки таблицы. |
10 | Power Query Продвинутый №10. Ссылки 2 | В этом уроке из курса обучения Power Query будем практиковать ссылки. Помимо ссылок еще повторим и изучим несколько техник и команд: объединение листов по вертикали, Table.Skip, Table.PromoteHeaders. |
11 | Power Query Продвинутый №11. Ссылки 3 | В этом уроке курса по Power Query мы повторим ссылки на запись, ссылки на значение, ссылки на другую строку. Также вспомним как создавать таблицу параметров. |
12 | Power Query Продвинутый №12. Ссылки 4: Ссылка на другой шаг запроса и много практики | В этом уроке мы повторим/научимся ссылаться на ячейку, которая находится в другом шаге запроса. |
13 | Power Query Продвинутый №13. Пользовательские функции | В этом уроке мы научимся создавать пользовательские функции. Разберем пользовательские функции на примере столбца с нарастающим итогом. |
14 | Power Query Продвинутый №14. #datetime, #duration: работа с датой и временем | В этом уроке по Excel Power Query вы научитесь пользоваться функциями #duration и #datetime для создания даты/времени и длительности. Изучите функцию DateTimeZone.UtcNow() Узнаете как складывать данные в формате datetime и duration. Познакомитесь с функцией DateTime.LocalNow(). Научитесь преобразовывать дату/время в текст при помощи DateTime.ToText() |
15 | Power Query Продвинутый №15. Текстовые функции / Функции Text.* | В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра. |
Номер урока | Урок | Описание |
---|---|---|
16 | Power Query Продвинутый №16. Важные операции: Группировка, Пивот, Анпивот, Сортировка | В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра. |
17 | Power Query Продвинутый №17. Нюансы консолидации из разных книг/листов Excel и List PositionOf | В этом уроке мы узнаем о некоторых нюансах объединения таблиц по вертикали из разных файлов. |
18 | Power Query Продвинутый №18. Разделить столбец на строки, Text.Trim | В этом уроке мы разделим текстовый столбец на строки. Данные скучены в одной ячейке. Нужно разбить ее на несколько. |
19 | Power Query Продвинутый №19. Заголовки в двух строках, List.Zip | В этом уроке мы обработаем заголовки, которые находятся в двух строках. Нам понадобится функция List.Zip |
20 | Power Query Продвинутый №20. Вычисления в Power Query | В этом уроке мы научимся выполнять вычисления в Power Query и в Power Pivot. |
21 | Power Query Продвинутый №21. Скользящее среднее, List Range | В этом уроке мы научимся считать скользящее среднее в Power Query. Для примера рассчитаем скользящее среднее предыдущих 13 значений. Нам пригодятся функции List.Range и List.Average. |
22 | Power Query Продвинутый №22. Агрегирование текста, группировка | В этом уроке вы научитесь выполнять группировку с агрегированием текстовых значений в Power Query. |
23 | Power Query Продвинутый №23. Трансформация столбцов, Table.TransformColumns | В этом уроке мы изучим функцию Table.TransformColumns. |
24 | Power Query Продвинутый №24. Разгруппировка, Скрытые возможности Table.ReplaceValue | В этом уроке мы научимся делать разгруппировку данных в Power Query, а также изучим скрытые возможности функции Table.ReplaceValue. |
25 | Power Query Продвинутый №25. Функция List.Accumulate | В этом уроке мы с нуля разберем функцию List.Accumulate. Сначала я объясню цикл For, а потом мы закрепим знания на несложных, но очень интересных примерах. |
26 | Power Query Продвинутый №26. Множественная замена текста c List.Accumulate и VBA | В этом уроке мы научимся делать множественную текстовую замену с помощью Power Query. Для этого мы воспользуемся функцией List.Accumulate. Также разберем как решить эту задачу при помощи VBA. |
27 | Power Query Продвинутый №27. Генерируем список URL (Text.Format, List.Generate, List.Accumulate) | В этом уроке мы сгенерируем список веб-страниц, в которых будет меняться параметр id. Сделаем это всего лишь в одном шаге при помощи функций Text.Format, List.Generate и List.Accumulate. |
28 | Power Query Продвинутый №28. Множественная текстовая замена c List.Generate | В этом уроке мы научимся выполнять множественную текстовую замену в Power Query с помощью функции List.Generate. Для спонсоров канала я так же расскажу как это делать в VBA. |
29 | Power Query Продвинутый №29. Генераций таблиц с List.Generate, График платежей по кредиту | Продолжим изучать функцию List.Generate. Создадим при помощи этой функции целую таблицу, которая будет представлять из себя график платежей по кредиту. |
30 | Power Query Продвинутый №30. Нарастающий итог с List.Generate | В этом уроке мы разберем функцию List.Generate, а также при помощи этой функции выполним нарастающий итог, который в сотни раз быстрее предыдущих версий нарастающего итога. |