Страницы: 1
RSS
Фильтрация вложенной таблицы Power Query
 
Добрый день коллеги.

Не бейте сильно, если где-то уже данный вопрос обсуждался, но я курил и Chris Webb и все такое, прямого решения н нашел, хотя много полезных идей около моей проблемы прочитал.
Итак есть таблица по сути три столбца Index - просто порядковый номер продажи в рамках системы, ID товара, Сумма продажи, необходимо на каждый товар, на каждую продажу подсчитать накопленную по нему сумму, по сути простой нарастающий итог, но посегментно, для каждого товара свой итог, а не общую сумму продаж. При этом желательно не использовать операции прерывающие Query Folding, по сути, ка кодин из вариантов решения, я сджойнил таблицу саму на себя и новом столбце получил вложенную таблицу с продажами по данному товару, но теперь вот незадача, надо отфильтровать вложенную таблицу, т.е. убрать все продажи с индексом (порядковым номером, Index из начала постановки задачи) больше чем у текущей строки, т.е. убрать все более поздние продажи, чем в текущей строке

https://prnt.sc/t1jI9XH2_JN5

Я пробовал Table.TransformColumns но она не принимает значение Index из внешнего окружения, моего понимания принципов работы PQ пока не хватает что-бы либо изобрести другое решение не прерывающее Query Folding либо довести это решение до ума, помогите плиз!  
Изменено: Константин Иванов - 23.06.2022 16:36:18
 
Константин Иванов, добавление индекса уже нарушает свертывание запроса. Почему не сделать нужное Вам на стороне БД?
 
Цитата
написал:
Константин Иванов, добавление индекса уже нарушает свертывание запроса. Почему не сделать нужное Вам на стороне БД?
Индекс как раз есть в исходной базе, поэтому его добавлять не требуется)) извините, видимо, сумбурно объяснил)
 
Вот,  кто-то решал похожую задачу просто в PQ, не помню где было (не моё):
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"ип", Int64.Type}, {"Описание", type text}, {"Параметр", Int64.Type}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"ип"}, {{"Параметр", each List.Sum([Параметр]), type number}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки", "Описание", each "Итого"),
    Пользовательская1 = (#"Измененный тип"&#"Добавлен пользовательский объект"),
    #"Сортированные строки" = Table.Sort(Пользовательская1,{{"ип", Order.Ascending}, {"Параметр", Order.Ascending}})
in
    #"Сортированные строки"
 
Константин Иванов, для такой задачи фолдинг поломается в любом случае. Если надо чтобы быстро, то лучше скулем на стороне БД решать, вьюху сделать. А если все же надо через PQ, то какой объем строк?
Вот горшок пустой, он предмет простой...
 
Цитата
написал:
Константин Иванов, для такой задачи фолдинг поломается в любом случае. Если надо чтобы быстро, то лучше скулем на стороне БД решать, вьюху сделать. А если все же надо через PQ, то какой объем строк?
Ну пока документов\строк немного, где-то 150.000 тысяч , те которые требуется проанализировать таким образом, но количество постоянно растет.
Жаль, что сворачивание придется потерять, но если его терять, то какие варианты Вы бы считали наиболее оптимальными? в целом я могу это сделать отдельной табличкой и при момощи связей  потом подцепить ее уже в Power BI к основной для анализа и построения отчетности. Наверное это не столь ужасно будет для производительности, поэтому за варианты с потерей Query Folding  тоже буду благодарен!
На стороне БД можно сделать, есть у нас девелопер, но я просто не хотел его дергать и мне самому эта задача представляется интересной, т.к. она в том или ином виде встречается в моей практике довольно часто и каждый раз приходится изобретать велосипед)) Мне вообще интересно именно решение с попыткой передать тем или иным способом внешний параметр внутрь функции - в даннном случае передать исходный Index в функцию, которая обрабатывает вложенную табличку. Видел решения через построение функции, но как добавить в нее изначальный Индекс не допёр (((
Так же есть решение через без передачи внешних данных внутрь функции, например List.Accumulate но мне кажется это медленное решение - тупо в изначалной табличке, опираясь на исходный индекс, перебирать по условию и ссумировать, если его же применять ко вложенным таблицам это вообще мне кажется очень долгим и неуклюжим решением, хотя я может чего-то не догоняю.

Заранее спасибо за отклик)
 
Цитата
написал:
Вот,  кто-то решал похожую задачу просто в PQ, не помню где было (не моё):
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9      let          Источник = Excel.CurrentWorkbook(){[Name=  "Таблица1"  ]}[Content],          #  "Измененный тип"   = Table.TransformColumnTypes(Источник,{{  "ип"  , Int64.Type}, {  "Описание"  , type text}, {  "Параметр"  , Int64.Type}}),          #  "Сгруппированные строки"   = Table.Group(#  "Измененный тип"  , {  "ип"  }, {{  "Параметр"  , each List.Sum([Параметр]), type number}}),          #  "Добавлен пользовательский объект"   = Table.AddColumn(#  "Сгруппированные строки"  ,   "Описание"  , each   "Итого"  ),          Пользовательская1 = (#  "Измененный тип"  &#  "Добавлен пользовательский объект"  ),          #  "Сортированные строки"   = Table.Sort(Пользовательская1,{{  "ип"  , Order.Ascending}, {  "Параметр"  , Order.Ascending}})    in          #  "Сортированные строки"   
 
Спасибо за отклик !
я посмотрел КОД ночестно признаться решения своего вопроса тут не увидел, скачал , сейчас еще раз проанализирую но что-то пока не увидел тут решения своего вопроса ))  
 
Цитата
Константин Иванов написал:
пока не увидел тут решения своего вопроса
Вы файл с примером в формате Excel (а не скрином) приложите, возможно и помощь будет более адресной
Изменено: Msi2102 - 21.06.2022 12:40:20
 
Цитата
написал:
Цитата
Константин Иванов написал:
пока не увидел тут решения своего вопроса
Вы файл с примером в формате Excel (а не скрином) приложите, возможно и помощь будет более адресной
Так я бы с удовольствием приложил, но у меня файла нет, я качаю данные напрямую из SQL
 
Вобщем пока нарастающий итог удалось сделать несколькими способами, но все они помимо того, что убивают Query Folding весьма медленные, самое быстрое на 150.000 тысяч записей получилось в районе 12-15 минут, думал List.Generate или List.Accumulate будут быстрее, но они по всей видимости работают быстро в рамках подсчета нарастающего итога вообще, а когда 150.000 строк еще дополнительно имеют разбивку по ID продукта, то что мне удалось построить на их основе уходит по времени в бесконечность ((

Итак способы, которые я использовал

1. Прямая фильтрация исходной таблицы по индексу и ID продукта, это примрено на 4-5 часов обновления
2. Создание вложенных табличек по ID продукта и отдельная функция, которая каждую вложенную табличку в каждой строке фильтрует, что-бы индекс было до или равно текущей продажи и подсчитывает накопленную сумму это тоже на часы обновление....
3. Заранее таблицу сортируем - что бы продажи сначала были отсортированы по ID продукта, как бы блоками получились внутри одной большой таблицы, затем внутри каждого продукта сортируем продажи по индексу , что бы они были в хронологическом порядке и затем добавив новый индекс уже в таком положении таблицы считаем накопительный итог для каждой продажи, 15 минут,пока это лучший результат.
Вариантов с List.Accumulate или List.Generate  пока построить не удалось, возможно там надо цикл в цикле делать - то есть общий цикл идет по табличке, а для каждой строки запускается собственный цикл который за счет индексов знает сколько строк надо проссумировать назад, что-бы получить нарастающий итог по текущему продукту
 
Изменено: Константин Иванов - 23.06.2022 15:07:11
 
Константин Иванов, вот вместо Вашей рукописи лучше бы сделали то, что Msi2102 попросил. И отговорка, что Вы данные из БД тащите, не принимается :) Мы от Вас как раз данные и не хотим, а нужен пример этих данных. Конечно, если хотите решение получить, а не просто выговориться :)
Изменено: surkenny - 23.06.2022 22:48:54
Страницы: 1
Читают тему (гостей: 1)
Наверх