Power Query Power Query. Продвинутый курс Формулы М

Power Query. Формулы М. List.Generate, график платежей по кредиту в Power Query

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

Описание

List.Generate позволяет генерировать целые таблицы. Здесь мы сгенерируем целую таблицу — график платежей по кредиту.

Исходные данные — это параметры кредита — процентная ставка, количество платежей и сумма кредита.

Решение

Сам алгоритм будет достаточно прост.

В параметре initial мы создадим запись из нескольких полей: счетчик периода, начальный баланс, ежемесячный платеж
В параметре condition укажем, что цикл будет продолжаться до тех пор, пока период меньше количества платежей
В параметре next создадим новое значение периода и новое значение баланса, а значение платежа останется постоянным
В параметре selector введем оставшиеся поля: процент, тело кредита, конечный баланс

Примененные функции

List.Generate
Number.Power
Table.FromList
Splitter.SplitByNothing
ExtraValues.Error
Table.ExpandRecordColumn
Table.TransformColumnTypes
Int64.Type

Код

let
   source = List.Generate(
      () => [
         Counter = 0,
         balance = loan_amt,
         payment = loan_amt
            * (
               (interest / 12)
                  * Number.Power(1 + (interest / 12), num_payments)
            )
            / (Number.Power(1 + (interest / 12), num_payments) - 1)
      ],
      each [Counter] < num_payments,
      each [
         balance = loan_amt
            * (
               Number.Power(1 + (interest / 12), num_payments)
                  - Number.Power(1 + (interest / 12), [Counter] + 1)
            )
            / (Number.Power(1 + (interest / 12), num_payments) - 1),
         payment = [payment],
         Counter = [Counter] + 1
      ],
      each [
         Месяц = [Counter] + 1,
         Начальный_баланс = [balance],
         Платеж = [payment],
         Процент = [balance] * interest / 12,
         Тело_кредита = [payment] - [balance] * interest / 12,
         Конечный_баланс = [balance]
            - ([payment] - [balance] * interest / 12)
      ]
   ),
   table_from_list = Table.FromList(
      source,
      Splitter.SplitByNothing(),
      null,
      null,
      ExtraValues.Error
   ),
   col_expand_table = Table.ExpandRecordColumn(
      table_from_list,
      "Column1",
      {
         "Месяц",
         "Начальный_баланс",
         "Платеж",
         "Процент",
         "Тело_кредита",
         "Конечный_баланс"
      },
      {
         "Месяц",
         "Начальный_баланс",
         "Платеж",
         "Процент",
         "Тело_кредита",
         "Конечный_баланс"
      }
   ),
   types = Table.TransformColumnTypes(
      col_expand_table,
      {
         {"Месяц", Int64.Type},
         {"Начальный_баланс", type number},
         {"Платеж", type number},
         {"Процент", type number},
         {"Тело_кредита", type number},
         {"Конечный_баланс", type number}
      }
   )
in
   types

Этот урок входит в Продвинутый курс 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	Выполним нарастающий итог, который в сотни раз быстрее предыдущих версий нарастающего итога.

предыдущая запись

Power Query. Формулы М. List.Generate, Нарастающий итог 5

следующая запись