Power Query. Ошибки. Не совпадают суммы при округлении

---

Описание

В этом уроке мы разберем ошибку, которая возникает, если нужно разбить сумму на определенные доли, а результаты округлить до двух знаков после запятой.

Например, у нас есть сумма зарплаты сотрудника. Этот сотрудник работал на нескольких точках. Нужно точно указать сколько он заработал на каждой точке исходя из отработанного времени. Результат нужно округлить до двух знаков после запятой.

После округления может получиться так, что сумма слагаемых не будет равняться сумме зарплаты. Различие составит одну копейку. Теперь эту копейку нужно вычесть из любой точки. О том как это сделать и будет этот урок.

В первой таблице находятся зарплаты сотрудников:

Во второй таблице указано сколько часов сотрудник работал в каждом отделе:

Решение

Чтобы решить эту задачу нам нужно будет найти сумму денег всех департаментов. Потом нужно сравнить сумму этих чисел с общей суммой и в случае несовпадения с общей суммой прибавить разницу к последнему числу.

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

• Table.TransformColumnTypes
• Table.NestedJoin
• JoinKind.LeftOuter
• Table.ExpandTableColumn
• Table.Group
• List.Sum
• Table.AddColumn
• Number.Round
• List.RemoveLastN
• Table.ReplaceValue
• Replacer.ReplaceValue
• Table.RemoveColumns

Код

Таблица с зарплатами:

let
   source = Excel.CurrentWorkbook(){[Name = "tblEmployeesAndSalaries"]}[
      Content
   ],
   types = Table.TransformColumnTypes(
      source,
      {{"ID", Int64.Type}, {"Name", type text}, {"Salary", type number}}
   )
in
   types

Итоговая таблица:

let
   source = Excel.CurrentWorkbook(){[Name = "tblEmploeesAndWorkTime"]}[
      Content
   ],
   types = Table.TransformColumnTypes(
      source,
      {
         {"ID", Int64.Type},
         {"Department", type text},
         {"Working Hours", type number}
      }
   ),
   table_merge = Table.NestedJoin(
      types,
      {"ID"},
      tblEmployeesAndSalaries,
      {"ID"},
      "tblEmployeesAndSalaries",
      JoinKind.LeftOuter
   ),
   col_expand_table = Table.ExpandTableColumn(
      table_merge,
      "tblEmployeesAndSalaries",
      {"Name", "Salary"},
      {"Name", "Salary"}
   ),
   table_group = Table.Group(
      col_expand_table,
      {"ID", "Name", "Salary"},
      {
         {
            "Таблица",
            each _,
            type table [
               ID = number,
               Department = text,
               Working Hours = number,
               Name = text,
               Salary = number
            ]
         },
         {"Total Hours ID", each List.Sum([Working Hours]), type number}
      }
   ),
   col_expand_table_2 = Table.ExpandTableColumn(
      table_group,
      "Таблица",
      {"Department", "Working Hours"},
      {"Department", "Working Hours"}
   ),
   table_add_col_percentage = Table.AddColumn(
      col_expand_table_2,
      "Percentage",
      each [Working Hours] / [Total Hours ID]
   ),
   table_add_col_salary_dep = Table.AddColumn(
      table_add_col_percentage,
      "Salary Department",
      each Number.Round([Salary] * [Percentage], 2)
   ),
   table_group_2 = Table.Group(
      table_add_col_salary_dep,
      {"ID", "Name", "Salary"},
      {
         {
            "Таблица",
            each _,
            type table [
               ID = number,
               Name = text,
               Salary = number,
               Department = text,
               Working Hours = number,
               Total Hours ID = number,
               Percentage = number,
               Salary Department = number
            ]
         }
      }
   ),
   table_add_col_salary_dep_2 = Table.AddColumn(
      table_group_2,
      "Salary Dep",
      each List.RemoveLastN([Таблица][Salary Department], 1)
         & {
            [Таблица]{0}[Salary]
               - List.Sum(
                  List.RemoveLastN([Таблица][Salary Department], 1)
               )
         }
   ),
   col_replace_value = Table.ReplaceValue(
      table_add_col_salary_dep_2,
      each [Таблица],
      each Table.FromColumns(
         {[Таблица][Department], [Таблица][Working Hours], [Salary Dep]}
      ),
      Replacer.ReplaceValue,
      {"Таблица"}
   ),
   cols_select = Table.RemoveColumns(
      col_replace_value,
      {"Salary Dep", "Salary"}
   ),
   col_expand_table_3 = Table.ExpandTableColumn(
      cols_select,
      "Таблица",
      {"Column1", "Column2", "Column3"},
      {"Department", "Working Hours", "Salary Department"}
   )
in
   col_expand_table_3