Power Query Продвинутый №29. Генераций таблиц с List.Generate, График платежей по кредиту

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

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

Описание

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

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

Решение

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

1. В параметре initial мы создадим запись из нескольких полей: счетчик периода, начальный баланс, ежемесячный платеж
2. В параметре condition укажем, что цикл будет продолжаться до тех пор, пока период меньше количества платежей
3. В параметре next создадим новое значение периода и новое значение баланса, а значение платежа останется постоянным
4. В параметре 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