Вечер добрый. Уперся и сделал обработку данных о продажах на предмет АВС-анализа с использованием двух пользовательских функций (частично подсмотрел на Ютубе - вторую функцию про Индексацию). Отработал его на данных где всего 121 строка (UPD), вроде немного, но любая крайняя операция занимала по минуте . Учитывая что по мимо АВС-анализа маржи еще придется делать аналогичный по затратам и продаже на 10 000 строках - то исход неизбежен и печален. Почему он тормозит? Плюс в том что начал понимать тему Пользовательских функций.
Основной запрос
Код
let
Sourse = Excel.CurrentWorkbook(){[Name="Таблица_1"]}[Content],
Types = Table.TransformColumnTypes(Sourse,{{"Предприятие", type text}, {"Группа", type text}, {"Наименование", type text}, {"Дата", type date}, {"Сумма", Int64.Type}}),
Sort = Table.Sort(Types,{{"Предприятие", Order.Ascending}, {"Дата", Order.Ascending}, {"Сумма", Order.Descending}}),
Add = Table.AddColumn(Sort, "Итого", each Summa (Sort, [Предприятие], [Дата])),
Group1 = Table.Group(Add, {"Дата"}, {{"Таблица", each _, type table}}),
Add1 = Table.AddColumn(Group1, "Пользовательская", each Table.Group([Таблица], {"Предприятие"}, {{"Таблица1", each _, type table}})),
Expand = Table.ExpandTableColumn(Add1, "Пользовательская", {"Предприятие", "Таблица1"}, {"Предприятие", "Таблица1"}),
Add2 = Table.AddColumn(Expand, "Пользовательская", each Table.AddIndexColumn ([Таблица1], "Индекс", 1, 1)),
Select = Table.SelectColumns(Add2,{"Пользовательская"}),
ТаблицА = Table.ExpandTableColumn(Select, "Пользовательская", {"Предприятие", "Группа", "Наименование", "Дата", "Сумма", "Итого", "Индекс"}, {"Предприятие", "Группа", "Наименование", "Дата", "Сумма", "Итого", "Индекс"}),
#"Нарастающий итог" = Table.AddColumn(ТаблицА, "Нарастающий итог", each SummaN(ТаблицА, [Предприятие], [Дата], [Индекс])),
ABC = Table.AddColumn(#"Нарастающий итог", "ABC", each if [Нарастающий итог] / [Итого] >= 0.9 then "C" else if [Нарастающий итог] / [Итого] <= 0.6 then "A" else "B"),
Доля = Table.AddColumn(ABC, "Доля", each [Сумма] / [Итого]),
Select2 = Table.SelectColumns(Доля,{"Предприятие", "Группа", "Наименование", "Дата", "Сумма", "Доля", "ABC"}),
#"Измененный тип" = Table.TransformColumnTypes(Select2,{{"Предприятие", type text}, {"Группа", type text}, {"Наименование", type text}, {"ABC", type text}, {"Доля", Percentage.Type}, {"Дата", type date}, {"Сумма", type number}})
in
#"Измененный тип"
Пользовательская функция (Summa) Итога по Предприятию и периоду
Код
(Tab, Pr, Data) =>
let
Select = Table.SelectRows(Table.SelectColumns(Tab,{"Предприятие", "Дата", "Сумма"}), each ([Предприятие] = Pr) and ([Дата] = Data)),
Sum = List.Sum(Select[Сумма])
in
Sum
Пользовательская функция (SummaN) для нарастающего итога по Предприятию и периоду
Код
(Таблица, Предпр, Data, КолвоСтрок) =>
let
Select = Table.SelectRows(Таблица, each ([Предприятие] = Предпр) and ([Дата] = Data)),
FirstN = Table.FirstN(Select,КолвоСтрок),
Sum = List.Sum(FirstN[Сумма])
in
Sum
UPD Пытался вложить Пользовательские функции внутрь основного запроса - не получается. Заменил файл - убрал ненужную группировку (код поправил).
mitox написал: Пользовательская функция (SummaN) для нарастающего итога по Предприятию и периоду
Вот это вот так делать не надо. Возьмите эту, и пристройте к делу.
Код
//Функция, которая к таблицу (первый аргумент) на основе данных столбца (второй аргумент)
//добавляет столбец с названием "Количество накопительно", в котором содержится накопительная сумма
FnRunSum = (sub as table, col as text)=>
let
subbuf = Table.Buffer( sub ),
itemCount = Table.RowCount( subbuf ),
sumList = List.Buffer( Table.ToColumns( Table.SelectColumns( subbuf, {col}) ){0} ),
calcRunningTotal = List.Generate(
() => [id = 0, runningSum = sumList{id}],
each [id] < itemCount,
each [id = [id] + 1, runningSum = [runningSum] + sumList{id}],
each [runningSum]
),
result = Table.FromColumns( Table.ToColumns( subbuf ) & {calcRunningTotal}, Table.ColumnNames( subbuf ) & {"Количество накопительно"} )
in
result
Михаил Л, Тоже самолет (UPD - на 121 строке, на 10 000 - не так). Моя "прикручивалка" пока только научилась читать код (потому что сам писал и вижу как вы его преобразовали). Но не более - я про FnRunSum . Его логика тоже понятна и читаема, кроме самого ListGenerator (читаю описание).
Первое что нужно понимать - результат работы List.Generate будет список. Элементами этого списка может быть что угодно: числа, текст, таблицы, записи, списки, и даже функции. Второе, внутренняя механика функции, в ней 4 аргумента, первый это мы задаем начальный элемент в цикла, в данном случае это запись из двух полей, обо поле это итератор, по которому проверяется условие выхода из цикла, а второе это аккумулятор, в котором мы накапливаем суммы. Второй аргумент это собственно проверка условия выхода из цикла. Третий аргумент это та самая однообразная, операция, которая применяется к элементам цикла, и которая передаётся на следующий круг, а 4 аргумент это то значение, которое мы хотим передать в итоговый список, который и будет результатом работы функции, в данном случае это накопительная сумма.
PooHkrd написал: а 4 аргумент это то значение, которое мы хотим передать в итоговый список, который и будет результатом работы функции, в данном случае это накопительная сумма
Вот этой части не хватало в моем пазле по аргументам функции.
let a=let a1= (a,b)=>
let b1=List.Generate(()=>[c=a{0},i=0],
each [i]<List.Count(a),
each try if b{[i]}=b{[i]+1}
then [c=[c]+a{[i]+1},i=[i]+1]
else [c=a{[i]+1},i=[i]+1]
otherwise [i=[i]+1], each [c])
in b1 in a1,
b=Excel.CurrentWorkbook(){[Name="Таблица_1"]}[Content],
c=(b[Сумма]),d=(b[Наименование])
in Table.FromColumns(Table.ToColumns(b)&{a(c, d)},
Table.ColumnNames(b)&{"Кол-во накопительно"})
Есть недочёт, если необходимо группировать по наименованию, виду товара, компании и другому Но это можно решить, предварительно правильно отсортировав источник
И вообще у меня недочетов нет. Как хотел я сделать - так и сделал. Раз хотите по другому, то надо показать желаемый результат не одним скрином на четыре строки, а три-четыре скрина по строк двадцать каждый. Если конешно не хотите показать желаемый результат в файле Excel.