Power Query. Формулы М. Объект List 1

Все уроки Продвинутого курса Power Query на этой странице и в этом плейлисте.

Описание

В этом уроке вы узнаете, что из себя представляет объект 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	Введение ч. 1	В этом уроке вы вкратце узнаете как устроен язык M / Power Query
2	Введение ч. 2	В этом уроке вы узнаете про ключевые слова let, in и узнаете как устроены функции M / Power Query.
3	Введение ч. 3	В этом уроке я расскажу какие преимущества вам даст владение языком формул M / Power Query
4	Объекты Power Query	В этом уроке мы познакомимся с объектами Power Query: Table, List, Record.
5	Объект List ч. 1	В этом уроке мы начнем знакомиться с объектом List в Excel Power Query.
6	Объект List ч. 2	Вы узнаете как создавать листы, как ссылаться на элементы листа и изучим несколько полезных формул листа.
7	Объект Record (Запись)	Record — это набор пар ключ-значение. Каждый Record — это строка таблицы.
8	Объект Table	В этом уроке мы познакомимся с объектом Table в Power Query. Что это такое и какие основные функции применяются к этому объекту.
9	Ссылки ч. 1	В этом уроке из курса по Power Query мы повторим как ссылаться на строки таблицы.
10	Ссылки ч. 2	В этом уроке из курса обучения Power Query будем практиковать ссылки.
11	Ссылки ч. 3	В этом уроке курса по Power Query мы повторим ссылки на запись, ссылки на значение, ссылки на другую строку.
12	Ссылки ч. 4	В этом уроке мы повторим/научимся ссылаться на ячейку, которая находится в другом шаге запроса.
13	Пользовательские функции	В этом уроке мы научимся создавать пользовательские функции.
14	Работа с датой и временем, #datetime, #duration	В этом уроке по Excel Power Query вы научитесь пользоваться функциями #duration и #datetime для создания даты/времени и длительности.
15	Аналоги текстовых функций Excel	В этом уроке курса мы изучим Power Query аналоги текстовых функций Excel.
16	Группировка, Пивот, Анпивот, Сортировка	В этом уроке по формулам Power Query мы разберем функции группировки, сведения, отмены сведения, сортировки и фильтра.
17	Нюансы консолидации из разных книг/листов Excel и List PositionOf	В этом уроке мы узнаем о некоторых нюансах объединения таблиц по вертикали из разных файлов.
18	Разделить столбец на строки, Text.Trim	В этом уроке мы разделим текстовый столбец на строки. Данные скучены в одной ячейке. Нужно разбить ее на несколько.
19	Заголовки в двух строках, List.Zip	В этом уроке мы обработаем заголовки, которые находятся в двух строках. Нам понадобится функция List.Zip
20	Вычисления в Power Query	В этом уроке мы научимся выполнять вычисления в Power Query и в Power Pivot.
21	Скользящее среднее, List Range	В этом уроке мы научимся считать скользящее среднее в Power Query.
22	Агрегирование текста, группировка	В этом уроке вы научитесь выполнять группировку с агрегированием текстовых значений в Power Query.
23	Table.TransformColumns	В этом уроке мы изучим функцию Table.TransformColumns.
24	Разгруппировка/Скрытые возможности Table.ReplaceValue	В этом уроке мы научимся делать разгруппировку данных в Power Query, а также изучим скрытые возможности функции Table.ReplaceValue.
25	Функция List.Accumulate	В этом уроке мы с нуля разберем функцию List.Accumulate. Сначала я объясню цикл For, а потом мы закрепим знания на несложных, но очень интересных примерах.
26	Множественная замена текста c List Accumulate и VBA	В этом уроке мы научимся делать множественную текстовую замену с помощью Power Query.
27	Функции Text.Format, List.Generate, List.Accumulate	В этом уроке мы сгенерируем список веб-страниц, в которых будет меняться параметр id.
28	Множественная текстовая замена c List.Generate	В этом уроке мы научимся выполнять множественную текстовую замену в Power Query с помощью функции List.Generate.
29	List.Generate 2, график платежей по кредиту	Создадим при помощи этой функции целую таблицу, которая будет представлять из себя график платежей по кредиту.
30	List.Generate, Нарастающий итог 5	Выполним нарастающий итог, который в сотни раз быстрее предыдущих версий нарастающего итога.