Power Query Power Query. Работа с ошибками Ошибки

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

Все уроки курса Работа с ошибками в 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

Этот урок входит в курс Работа с ошибками в Power Query

Номер урока	Урок	Описание
1	Измененный тип, Фильтрация в UI	Почему возникает ошибка Expression.Error Столбец таблицы не найден? Одна из причин — шаг Измененный тип. Почему в вашей итоговой таблицы не все данные, которые должны там оказаться? Вероятно вы совершили ошибку неверной фильтрации в пользовательском интерфейсе.
2	Фиксированная ширина столбца Excel	В этом уроке вы узнаете как отключить автоматическое определение ширины столбцов таблицы Excel при обновлении запроса Power Query.
3	Formula Firewall, запрос ссылается…	Вы работаете в Power Query, но при попытке обновить все возникает ошибка Formula Firewall, запрос ссылается на… Что делать? Как исправить?
4	Доступ к веб-содержимому	В этом уроке вы узнаете как избавиться от надоедливого окна доступ к веб-содержимому.
5	Количество столбцов CSV	При импорте CSV получается неверное количество столбцов.
6	Не получается изменить тип данных	При попытке изменить тип данных с текстового на дату возникает ошибка DataFormat.Error.
7	Разные имена листов	В этом уроке мы решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами.
8	Разные имена листов 2	В этом уроке мы другими способами решим проблему, когда в ваших источниках нужные вам данные находятся всегда на листах с разными именами.
9	Разные имена столбцов, Table.TransformColumnNames	В этом уроке мы разберем проблему, когда нам присылают файлы, в которых всегда столбцы названы по-разному.
10	Как развернуть все столбцы	В этой задаче мы научимся разворачивать все столбцы табличного столбца не перечисляя каждый из них хардкодом.
11	Подключиться к последнему файлу	В этом уроке мы научимся подключаться к самому свежему файлу из нужной нам папки с нужным названием.
12	Консолидация и MissingField.Type	Бывает вы ходите выполнить добавление таблиц друг под другом с предварительной обработкой, но получаете ошибку Expression.Error: Столбец таблицы не найден.
13	Удалить пустые столбцы	Как без хардкода автоматически удалить пустые столбцы в Power Query, которые есть в импортируемой таблицы. Разберем 2 способа. Один из них прост до безобразия, а для второго понадобится функция Table.Profile.
14	Удалить лишние пробелы 2	В этом уроке мы разберем еще 1 способ удалить лишние пробелы в текстовом столбце Power Query. Для этого мы повторим и изучим несколько новых функций. Text.SplitAny List.Select Character.FromNumber Text.Combine
15	Плохо структурированный TXT	Как быть, если текстовый файл разбивается на столбцы неправильно?
16	При округлении не совпадает общая сумма	Разберем ошибку, которая возникает при необходимости разбить число на определенные доли, а результаты округлить до двух знаков после запятой. В такой ситуации может получиться, что сумма слагаемых не будет равняться исходному числу.

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

Power Query. Практика. Скученные данные 3

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