Страницы: 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
 
Цитата
написал:
Константин Иванов, вот вместо Вашей рукописи лучше бы сделали то, что Msi2102 попросил. И отговорка, что Вы данные из БД тащите, не принимается  Мы от Вас как раз данные и не хотим, а нужен  пример  этих данных. Конечно, если хотите решение получить, а не просто выговориться
Прошу прощения был в отъезде.
PooHkrd, surkenny , Msi2102
Вот файл , еще раз постараюсь кратко, с этим у меня проблемы.
Есть файл с продажами, каждая продажа содержит ID продукта, задача посчитать в Power Query нарастающий итог, на каждую продажу по этому конкретному продукту.
P.S. Попробовал считать его сгруппировав продукты блоками т.е. отсортировав входную таблицу так что продажи кадого продукта идут строго подряд по возрастанию , затем по следующему продукту, и так далее. Применил List.Generate, на 150.000 продаж думаю будет обновление около часа - но генерально работает.
P.P.S в DAX  за счет связей между таблицами вобщем-то тоже легче посчитать, интересно решить эту задачу именно в Power Query в том числе что бы не нагружать отчет, одно дело он на минуту дольше будет обновляться зато потом работать быстро, другое дело он будет это считать и хранить в самом Power BI мне показалось это не оптимально, но может я ошибаюсь, читал тут мнение, что DAX лучше приспособлен к подсчетам и делает это быстрее, а Power Query  скорее предназначен для подготовки сырых данных, а не для подсчетов.
Изменено: Константин Иванов - 11.07.2022 09:39:17
 
surkenny, Msi2102, PooHkrd, очень прошу промощи, и заранее прошу прощения за настойчивость)
 
Константин Иванов, попробуйте так (должно быть быстро):
Код
let
  data = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
  typed = Table.TransformColumnTypes ( data, { { "Date", type datetime }, { "Price", type number }, { "RN", Int64.Type } } ),
  sort = Table.Sort ( typed, { { "ProductID", Order.Ascending }, { "Date", Order.Ascending } } ),
  group = Table.Group (
    sort,
    { "ProductID" },
    {
      {
        "recs",
        ( t ) =>
          [
            recs = List.Buffer ( Table.ToRecords ( t ) ),
            generate = List.Generate (
              () => [ i = 0, sum = recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
              each [i] < List.Count ( recs ),
              each [ i = [i] + 1, sum = [sum] + recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
              each [result]
            )
          ][generate],
        type {record}
      }
    },
    GroupKind.Local
  ),
  toTable = Table.FromRecords ( List.Combine ( group[recs] ) )
in
  toTable
 
surkenny, спасибо! пытаюсь разобраться как это работает - если яправильно понимаю внутрь функции группировки, вставляем List.Generate в функцию агрегации, я не столь искушенный юзер или кодер в данном случае, мне потребуется время для осознания )) сегодня или макс завтра вернусь с результатом ) еще раз благодарность за ответ.
P.S. думаю тема ушла в сторону от фильтрации вложенных таблиц, это уже нарастающий итог по продукту с учетом даты или порядкового номера продажи) видимо я неверным путем изначально пошел и отсюда неверная тема.
 
Можно так (query folding сохраняется):
Код
let
  Source = Sql.Database("Server", "Database"), 
  sql = Value.NativeQuery(
    Source, 
    "SELECT *,
            SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date) as RT 
    FROM
        dbo.Table", 
    null, 
    [EnableFolding = true]
  )
in
  sql

Server, Database, Table (и схему, если не dbo) подставляете свои. Вместо * можете выбрать только интересующие столбцы. Потестил с SQL Server 2019, работает. Если в Вашей базе данных нет оконок, можете посмотреть альтернативы.
Изменено: Aleksei_Zhigulin - 13.07.2022 00:11:40
 
Цитата
написал:
Можно так (query folding сохраняется):
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13      let        Source = Sql.Database(  "Server"  ,   "Database"  ),         sql = Value.NativeQuery(          Source,           "SELECT *,                  SUM(Price) OVER (PARTITION BY ProductID ORDER BY   Date  ) as RT           FROM              dbo.Table",           null,           [EnableFolding = true]        )    in        sql   
 
Server, Database, Table  (и схему, если не  dbo ) подставляете свои. Вместо * можете выбрать только интересующие столбцы. Потестил с SQL Server 2019, работает. Если в Вашей базе данных нет оконок, можете посмотреть  альтернативы .
Спасибо громадное, я не очень силен в SQL, но я примерно понял как это работает, но только не вижу отбора на каждую продажу, что-бы получить все продажи ДО и ВКЛЮЧАЯ эту продажу, но не позже, тут по-моему алгоритм считает все продажи по продукту, а у меня задача посчитать все продажи по продукту на момент этой конкретной продажи, когда встретится следующая продажа то нарастающий итог подрастет. Т.е. для каждой продажу нужен именно нарастающий итог до и включая эту самую продажу, а не общая сумма по продукту. Или я неверно понял то, что Вы написали ?
Изменено: Константин Иванов - 13.07.2022 09:29:15
 
Цитата
написал:
Или я неверно понял то, что Вы написали ?
Похоже на то :) Впрочем, я тоже немного ошибся - особенно не смотрел Ваши данные, думал, дата и время будет уникальным идентификатором. Если уникальный идентификатор у Вас в столбце RN, то нужно добавить его в ORDER BY:
Код
let
  Source = Sql.Database("Server", "Database"), 
  sql = Value.NativeQuery(
    Source, 
    "SELECT *,
            SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date, RN) as RT 
    FROM
        dbo.Table", 
    null, 
    [EnableFolding = true]
  )
in
  sql

Ну или сделать более универсально (т.е. даже если уникального идентификатора строки нет):
Код
let
  Source = Sql.Database("Server", "Database"), 
  sql = Value.NativeQuery(
    Source, 
    "SELECT *,
            SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RT
    FROM
        dbo.Table", 
    null, 
    [EnableFolding = true]
  )
in
  sql

В целом, код выполняет ровно то, что Вы и хотели:
1. Добавляет столбец с накопительным итогом по каждому продукту
2. Делает это в PQ
3. Так, чтобы query folding не поломался.
Изменено: Aleksei_Zhigulin - 13.07.2022 17:50:05
 
Aleksei_Zhigulin, какие люди возвращаются! Живой! Приветствую!
Вот горшок пустой, он предмет простой...
 
PooHkrd, приветствую! Надо же, ещё помнят :) Дефицит интересных задач, вот восполняю :) А то с вашими телеграмьими скоростями едва листать успеваешь ;)  
 
Цитата
написал:
Константин Иванов, попробуйте так (должно быть быстро):
Код
    [URL=#]?[/URL]       1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28      let        data = Excel.CurrentWorkbook(){[ Name =   "data"   ]}[Content],        typed = Table.TransformColumnTypes ( data, { {   "Date"  , type datetime }, {   "Price"  , type number }, {   "RN"  , Int64.Type } } ),        sort = Table.Sort ( typed, { {   "ProductID"  , Order.Ascending }, {   "Date"  , Order.Ascending } } ),        group = Table.Group (          sort,          {   "ProductID"   },          {            {              "recs"  ,              ( t ) =>                [                  recs = List.Buffer ( Table.ToRecords ( t ) ),                  generate = List.Generate (                    () => [ i = 0, sum = recs{i}[Price], result = Record.AddField ( recs{i},   "Sum"  , sum ) ],                    each [i] < List.Count ( recs ),                    each [ i = [i] + 1, sum = [sum] + recs{i}[Price], result = Record.AddField ( recs{i},   "Sum"  , sum ) ],                    each [result]                  )                ][generate],              type {record}            }          },          GroupKind.Local        ),        toTable = Table.FromRecords ( List.Combine ( group[recs] ) )    in        toTable   
 
surkenny, работает очень быстро, как работает я тоже вобщем-то разобрался, но только делает не совсем то, что я описывал, данный код считает нарастающий итог по продукту, а мне нужен нарастающий итог по продукту с учетом даты или индекса продажи т.е. мне нужны все продажи до и включая текущую, не не включая последующие, можно опираться на дату но лучше на столбец RN что бы можно было точно понимать какие продажи были до, а какие после текущей.
Изменено: Константин Иванов - 03.08.2022 14:32:21
 
Цитата
Константин Иванов написал:
как работает я тоже вобщем-то разобрался
Если это так, тогда вообще не понял
Цитата
Константин Иванов написал:
код считает нарастающий итог по продукту, а мне нужен нарастающий итог по продукту с учетом даты или индекса продажи
А как сейчас считается? До расчета мы сортируем строки по продукту/дате. Не нравится по дате, сделайте по [RN]:
Код
= Table.Sort ( typed, { { "ProductID", Order.Ascending }, { "RN", Order.Ascending } } )

Разве тут не нарастающий итог с учетом RN?
 
Цитата
написал:
Цитата
Константин Иванов написал:
как работает я тоже вобщем-то разобрался
Если это так, тогда вообще не понял
Цитата
Константин Иванов написал:
код считает нарастающий итог по продукту, а мне нужен нарастающий итог по продукту с учетом даты или индекса продажи
А как сейчас считается? До расчета мы сортируем строки по продукту/дате. Не нравится по дате, сделайте по [RN]:
Код
    [URL=#]?[/URL]       1      = Table.Sort ( typed, { {   "ProductID"  , Order.Ascending }, {   "RN"  , Order.Ascending } } )   
 
Разве тут не нарастающий итог с учетом RN?
surkenny, да, приношу свои извинения, код работает как надо и если отсортировать затем продажи обратно, все будет гут. Но я вот написал, что понял как код работает, при ближайшем рассмотрении, вынужен признать, нет )) прошу Вашей помощи:
то, что Table.Group передает кусок таблицы сгруппированный по столбцам (в нашем случае по одному столбцу "ProductID") это я понял и легкомысленно решил, что далее все просто, но вот что я не понял это
1. Верно ли я понимаю, что далее после столбца группировки объявляется функция ( t ) =>  , разве нельзя просто там разместить List.Generate?
2. Если я все верно прочитал, то там после функции стоит [generate], вот этого я не понял совсем ))
3. Самое, пожалуй главное, как мы указываем функции List.Generate,  что именно этот сгруппированный, отфильтрованный кусок таблицы, верно ли я понимаю, что это происходит на моменте recs = List.Buffer ( Table.ToRecords ( t ) ) т.е. на моменте объявления функции собственно мы и получаем что t = кусок этой исходной таблицы ?

Я очень прше прощения за вопросы, елси они покажутся дурацкими, но мне действительно непонятно )) заранее спасибо за потраченное на меня время!
 
Цитата
Константин Иванов написал:
1. Верно ли я понимаю, что далее после столбца группировки объявляется функция ( t ) =>  , разве нельзя просто там разместить List.Generate?
Так нам бы в List.Generate в каждом шаге обращаться к новой "строке". Быстро и просто - создать из таблицы список записей, в каждом шаге List.Generate увеличивать какой-то счетчик на 1 и обращаться к соответствующему элементу списка записей ( ИмяШагаСпискаЗаписей{n} ).
Цитата
Константин Иванов написал:
2. Если я все верно прочитал, то там после функции стоит [generate], вот этого я не понял совсем ))
Конструкция
[a=..., b=..., c=...][b] эквивалентна let a=..., b=..., c=... in b.
По сути это вложенный запрос. Если понятнее, то можно код переписать так:
Код
...
  group = Table.Group (
    sort,
    { "ProductID" },
    {
      {
        "recs",
        ( t ) =>
          let
            recs = List.Buffer ( Table.ToRecords ( t ) ),
            generate = List.Generate (
              () => [ i = 0, sum = recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
              each [i] < List.Count ( recs ),
              each [ i = [i] + 1, sum = [sum] + recs{i}[Price], result = Record.AddField ( recs{i}, "Sum", sum ) ],
              each [result]
            )
          in
            generate,
        type {record}
      }
    },
    GroupKind.Local
  ),
...

Цитата
Константин Иванов написал:
на моменте объявления функции собственно мы и получаем что t = кусок этой исходной таблицы ?
Все верно. Мы объявили функцию от одной переменной t. Для каждой группы t - это часть таблицы из предыдущего шага sort. И вот с этой таблицей можете делать все, что угодно :).
Можно было бы сделать по-другому: each перед дальнейшим кодом, а вместо t использовать _. Но тогда рискуете нарваться на сложности при вложенных each внутри. Лучше объявить имя этой таблицы.

P.S. Возможно для понимания будет полезен чуть измененный код (сортируем уже таблицу t внутри группировки; группировка глобальная, а не локальная - четвертый аргумент функции Table.Group по умолчанию GroupKind.Global.
Скорее всего, такой вариант будет медленнее. Потестите на реальных данных:
Скрытый текст
Изменено: surkenny - 09.08.2022 10:34:30
 
Цитата
surkenny написал:
можно код переписать так:
Мне так понятнее)
А то голову сломал(в хорошем смысле слова) на ваших решениях :D  
Изменено: Михаил Л - 09.08.2022 10:59:47
 
Михаил Л, я это для "красоты" что-ли кода использую :)
Ну и по аналогии с переменными DAX у Феррари/Руссо не очень люблю код вида
Код
let
   a=1,
   b=2
in
   a+b

Вот так как-то лучше для меня :) :
Код
let
   a=1,
   b=2,
   res = a+b
in
   res

И более удобно для отладки кода вложенного запроса на части данных.

P.S. Пора Вам перенимать эстафету :) PooHkrd совсем редко заходит (остальные M/DAX специалисты тоже :( ), а у меня сейчас все время уходит на FineBI. Ближайшее время вообще особо не смогу помогать :(
 
Цитата
surkenny написал:
более удобно для отладки кода вложенного запроса
Я не программист), поэтому мне удобнее накликать сгенерированный запрос и изменить только необходимое.
Цитата
surkenny написал:
Пора Вам перенимать эстафету   PooHkrd  совсем редко заходит (остальные M/DAX специалисты тоже  )
Заходят наверное все (только Андрея VG не видно). А вот решений-ответов да, мало.
Я бы и рад чаще помогать, только у меня работа много времени занимает. Да и башка часто занята другим)
 
Цитата
Михаил Л написал:
Я бы и рад чаще помогать, только у меня работа много времени занимает
:D  :D  :D
Изменено: Msi2102 - 09.08.2022 12:11:09
Страницы: 1
Наверх