Страницы: 1
RSS
Power Query как ускорить работу запроса в котором использованы пользовательские функции?
 
Вечер добрый.
Уперся и сделал обработку данных о продажах на предмет АВС-анализа с использованием двух пользовательских функций (частично подсмотрел на Ютубе - вторую функцию про Индексацию). Отработал его на данных где всего 121 строка (UPD), вроде немного, но любая крайняя операция занимала по минуте :cry: .
Учитывая что по мимо АВС-анализа маржи еще придется делать аналогичный по затратам и продаже на 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 - 09.04.2020 19:31:26
 
Цитата
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
Изменено: PooHkrd - 09.04.2020 20:32:49
Вот горшок пустой, он предмет простой...
 
PooHkrd, Спасибо - очень шустрая (!). Пробую ее по индексам теперь "прикрутить".
 
mitox, на ListGenerator-е, конечно, шустрее
Код
// Таблица 2 (2)
let
   
    SummaN = (Таблица, Предпр, Data, КолвоСтрок) =>
    let
    Select = Table.SelectRows(Таблица, each ([Предприятие] = Предпр) and ([Дата] = Data)),
    FirstN = Table.FirstN(Select,КолвоСтрок),
    Sum = List.Sum(FirstN[Сумма])
    in
    Sum,
    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}}),
    Group1 = Table.Group(Sort, {"Дата","Предприятие"}, {{"Таблица", each Table.AddIndexColumn (_, "Индекс", 1, 1), type table}, {"Итого", each List.Sum([Сумма]), type number}})[[Таблица],[Итого]],
    ТаблицА = Table.ExpandTableColumn(Group1, "Таблица", {"Предприятие", "Группа", "Наименование", "Дата", "Сумма", "Индекс"}, {"Предприятие", "Группа", "Наименование", "Дата", "Сумма", "Индекс"}),
    #"Нарастающий итог" = 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
    #"Измененный тип"
 
Михаил Л, Тоже самолет (UPD - на 121 строке, на 10 000 - не так). Моя "прикручивалка" пока только научилась читать код (потому что сам писал и вижу как вы его преобразовали). Но не более - я про FnRunSum . Его логика тоже понятна и читаема, кроме самого ListGenerator (читаю описание).

UPD Вот эта часть для меня дремучий лес
Код
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 ) & {"Количество накопительно"} )
UPD Хотя нет - и это читаемо. Это я не научился еще думать этим языком.
Изменено: mitox - 09.04.2020 21:49:35
 
Первое что нужно понимать - результат работы List.Generate будет список. Элементами этого списка может быть что угодно: числа, текст, таблицы, записи, списки, и даже функции.
Второе, внутренняя механика функции, в ней 4 аргумента, первый это мы задаем начальный элемент в цикла, в данном случае это запись из двух полей, обо поле это итератор, по которому проверяется условие выхода из цикла, а второе это аккумулятор, в котором мы накапливаем суммы. Второй аргумент это собственно проверка условия выхода из цикла. Третий аргумент это та самая однообразная, операция, которая применяется к элементам цикла, и которая передаётся на следующий круг, а 4 аргумент это то значение, которое мы хотим передать в итоговый список, который и будет результатом работы функции, в данном случае это накопительная сумма.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
а 4 аргумент это то значение, которое мы хотим передать в итоговый список, который и будет результатом работы функции, в данном случае это накопительная сумма
Вот этой части не хватало в моем пазле по аргументам функции.
Изменено: mitox - 09.04.2020 22:02:29
 
еще один вариант на List.Generate
Код
let
    ABC =(Tbl,Col,BC,Name)=> let
        source = Table.Buffer(Table.Sort(Tbl,{Col, 1})),
        list   = List.Buffer(Table.Column(source,Col)),
        bounds = List.Buffer({0}&List.Transform(BC,each _*List.Sum(list))),
        ABC    = {List.Generate(
                     ()=> [ i=0, v=list{i}, p=1 ],
                     each [i]<List.Count(list),
                     each [ i=[i]+1,
                            v=[v]+list{i},
                            p=List.Count(List.Select(bounds,each _=<v))
                          ],
                     each {"A","B","C"}{[p]-1}
                 )}
    in Table.FromColumns(Table.ToColumns(source)&ABC,Table.ColumnNames(source)&{Name}),
    Source = Excel.CurrentWorkbook(){[Name="Таблица_1"]}[Content],
    
    Grouped = Table.Group(Source , {"Дата"}, {{"_", each Table.Group(_, {"Предприятие"}, {{"_", each ABC(_,"Сумма",{.6,.9},"ABC")}})}}),
    Expand = Table.ExpandTableColumn(Table.ExpandTableColumn(Grouped,"_",{"_"}), "_", {"Предприятие", "Группа", "Наименование", "Сумма", "Столбец1", "ABC"})
in
    Expand
 
Андрей Лящук, Волшебник и Волшебники - спасибо. Я научусь (!)
 
Доброго вечер!
Прошу помочь, какой нужно использовать способ, какой метод.
Поработал с материалами этого поста, доработал файл под свои нужды
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица_1"]}[Content],
    #"Сгруппированные строки" = Table.Group(Источник, {"Предприятие", "Группа", "Наименование"}, {{"Количество", each Ф_Накопительно(_,"Сумма"), type table}}),
    #"Развернутый элемент 1" = Table.ExpandTableColumn(#"Сгруппированные строки", "Количество", {"Дата", "Сумма", "Количество накопительно"}, {"Дата", "Сумма", "Кол-во накопительно"}),
    #"Развернутый элемент 2" = Table.ExpandListColumn(#"Развернутый элемент 1", "Кол-во накопительно"),
    #"Строки с применным фильтром1" = Table.SelectRows(#"Развернутый элемент 2", each ([#"Кол-во накопительно"] <> null)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Строки с применным фильтром1",{"Сумма"})
in
    #"Удаленные столбцы"
Когда раскрываю таблицу, раскрываю лист, нарастающие суммы раскрываются в одну дату
В идеале должно получиться, как на рисунке

Заранее благодарен!
Изменено: Tikr - 16.10.2020 20:49:41
 
Группировал по наименованию. Буфера надо добавить
Код
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)&{"Кол-во накопительно"})
 
Да, спасибо, всё работает!

Есть недочёт, если необходимо группировать по наименованию, виду товара, компании и другому
Но это можно решить, предварительно правильно отсортировав источник
 
Tikr, вот так сделайте для скорости
Код
c=List.Buffer(b[Сумма]),d=List.Buffer(b[Наименование])
И вообще у меня недочетов нет. Как хотел я сделать - так и сделал.
Раз хотите по другому, то надо показать желаемый результат не одним скрином на четыре строки, а три-четыре скрина по строк двадцать каждый. Если конешно не хотите показать желаемый результат в файле Excel.
Изменено: Михаил Л - 17.10.2020 11:43:38
Страницы: 1
Наверх