Страницы: 1
RSS
Power Query аналог формулы СУММЕСЛИМН
 
Уважаемые, а кто подскажет возможно ли в PQ сделать конструкцию по аналогии с формулой из Excel СУММЕСЛИМН? Чтобы при добавлении столбца можно было бы прописать суммирование по выборке?
Изменено: PooHkrd - 03.02.2022 12:27:10
Вот горшок пустой, он предмет простой...
 
В PQ есть функции "Колонка по условию"(Conditoinal column) и "Пользовательская колонка" (Custom column). В первой можно использовать механизм "что...если...", а во второй писать пользовательские формулы.

Формулы на языке M для PQ можно найти здесь https://msdn.microsoft.com/library/1ed840b1-7e20-4419-ad2f-d82054c9b2ab
 
Denis87,это мне известно. А как имея таблицу:
Товар / Город / Продажи
дыня / Тамбов / 5
дыня / Липецк / 15
тыква / Тамбов / 10
тыква / Липецк / 19

получить на выходе таблицу
Товар / Город / Продажи / Сумма продаж
дыня / Тамбов / 5 / 20
дыня / Липецк / 15 / 20
тыква / Тамбов / 10 / 29
тыква / Липецк / 19 / 29
Где в столбце сумма продаж должна посчитаться сумма продаж в разрезе товаров по всем городам? В екселе это 1 минута, а в PQ я такое могу сделать только через создание отдельного запроса с группировкой по товару исходного массива и потом добавлением к исходному массиву столбца через слияние из запроса с группировкой.

А мне вот хочется прописать формулой, но не очень понимаю как. А прописывать условия для каждого товара слишком жирно, у меня ассортимент 15 тыщ СКЮ. Причем есть сильное подозрение, что это можно реализовать через перемножение записей с функцией Record.FieldValues(_) (слава товарищу Зеленскому за её для меня открытие). но голова что-то не варит как.
Изменено: PooHkrd - 08.12.2021 08:55:48
Вот горшок пустой, он предмет простой...
 
PooHkrd, выложите файл-пример чтобы можно было экспериментировать, иначе трудно что-то предлагать.
 
Да не вопрос. Выложил.
Изменено: PooHkrd - 25.01.2021 17:18:56
Вот горшок пустой, он предмет простой...
 
Нашел такой код, который, если я правильно понимаю, как раз делает то, что вам нужно, но пока сам разбираюсь как его под ваши переменные переписать:
Код
Table.AggregateTableColumn(  
  
    Table.FromRecords(  
  
    {[t = Table.FromRecords({[a=1, b=2, c=3], [a=2,b=4,c=6]}), b = 2]}, type table [t = table [a=number, b=number, c=number], b = number]  
  
    ), "t",  
  
    {{"a", List.Sum, "sum of t.a"},  
  
     {"b", List.Min, "min of t.b"},  
  
     {"b", List.Max, "max of t.b"},  
  
     {"a", List.Count, "count of t.a"}})  
 
Основной вопрос - зачем заставлять PQ считать формулы, которые потом можно выполнить в DAX (в разы быстрее). Уверен, сам по себе такой столбец вам не нужен, потом все равно будете делить одно на другое и так далее, считать разные ABC и т.п.
Задача PQ - подготовить данные для дальнейших расчетов.

Если всё же надо именно так, то три варианта одной формулой:
1. Почти СУММЕСЛИМН
Код
= Table.AddColumn(Источник, "Сумма Продаж по товару", each List.Sum(Table.SelectRows(Источник, (r)=>r[Код]=_[Код] and r[Товар]=_[Товар])[Продажи]))

2. Можно так (две группировки - сумма и "все строки"), потом разворачиваем:
Код
    #"Grouped Rows" = Table.Group(Источник, {"Код", "Товар"}, {{"tab", each _, type table}, {"Сумма продаж по товару", each List.Sum([Продажи]), type number}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "tab", {"Город", "Продажи"}, {"Город", "Продажи"})

3. Но лучше и гораздо быстрее на вашем объеме данных - через джойн (слияние) с группировкой. Группировки и джойн в PQ оптимизированы, и выполняется быстрее, чем группировка и разворачивание.
Код
= Table.Join(Источник, {"Код", "Товар"}, Table.Group(Источник, {"Код", "Товар"}, {{"Сумма Продаж по товару 2", each List.Sum([Продажи])}}), {"Код", "Товар"})

Не стоит в PQ пытаться всегда соблюсти логику формул Excel, это не всегда лучший путь.
F1 творит чудеса
 
Слегка обновил пример, оказывается группировать и объединять можно прямо в одном запросе. Это уже интереснее и многое упрощает, плюс уменьшает время на вычисления в больших массивах.
Denis87, большое вам спасибо за попытку помочь. Если не сможете найти решение, то и фиг с ним, буду делать как в приложенном примере.
Изменено: PooHkrd - 16.05.2017 15:24:05
Вот горшок пустой, он предмет простой...
 
Максим Зеленский, ну до второго примера уже сам до копался на просторах интернетов. А вот третий просто прекрасен.
На счет логики формул Ексель - очень трудно её выковыривать из головы, когда за 4 года решение всех задач делал через формулы, а потом автоматизацию заказывал у ВБАшников. С PQ знаком полтора месяца - меня ВБАшники наши забыли уже как выгляжу. И все задачи решаются в 2-3 раза быстрее. Но голову потихоньку переформатирую, за это в том числе и вам спасибо.
Вопрос закрыт, всем еще раз спасибо!.
Вот горшок пустой, он предмет простой...
 
Добрый день Максим Зеленский,
Цитата
Максим Зеленский написал:
зачем заставлять PQ считать формулы, которые потом можно выполнить в DAX (в разы быстрее)
DAX это уже Power BI   или в Power Pivot тоже он?

выше в теме мы предложили несколько вариантов решения задачи с суммированием
с точки зрения оптимизации кода и ускорения обновления запросов какой вариант будет быстрее там где меньше строк кода или где меньше промежуточных вычислений? к чему нужно стремиться при работе запросами?
Изменено: Blood81 - 20.06.2017 13:11:24
 
Цитата
Blood81 написал:
DAX это уже Power BI   или в Power Pivot тоже он?
Тоже он. Еще и в SSAS Tabular тоже он.
Цитата
Blood81 написал:
какой вариант будет быстрее там где меньше строк кода или где меньше промежуточных вычислений?
Количество строк кода вряд ли сильно влияет :) но абстрактно - сложно давать совет. Код быстрее, когда он не создает сущности сверх необходимого, и когда использует оптимальный набор действий с точки зрения алгоритма.
Конечно, чем меньше вычислений, тем быстрее - меньше загружаем память, меньше дергаем разные источники данных, и т.п. Изучайте встроенные функции, чтобы не делать лишние телодвижения. Их много - почти 600, для большинства простых и не очень операций. Но только практика и анализ готовых решений даст результат. Тут, как и в Excel, одну задачу можно решить несколькими способами, и постепенно научитесь не делать лишние вычисления и операции.

Вообще оптимизация кода в PQ - задача нетривиальная и иногда неочевидная, к тому же различающаяся немного в Power BI и в Excel.
F1 творит чудеса
 
Blood81, для себя вывел такое правило: через PQ тащу все что нужно из источников и через джойны формирую столбцы таблицы, которая будет основой для строк итогового отчета, потом все остальные таблицы со значениями также затаскиваю в модель, и настраиваю связи от них к основной. А уже в модели на DAX  добавляю новые столбцы с нужными вычислениями. Ну и результаты в сводную.
Так работает на порядок быстрее если нужно переженить друг с другом порядка 20-ти таблиц по 20к-100к записей в каждой.

Изначально пытался делать всё через PQ в результате оно или зависало или рассчитывалось по 3-5 часов (я так понял это из-за "ленивых" вычислений в нем).

Для меня только одна проблема: в PQ разобраться значительно проще, т.к. конструктор запросов в нем сделан так, что при наличии справки не разберется разве что только полный имбецил. А вот с РР все не так привычно из-за чего кажется, что сложнее. Но это ничего - освоим.
Вот горшок пустой, он предмет простой...
 
Совет 1: изучайте правила построения аналитической модели данных. Неправильная модель данных приводит к тормозам и неудобству и сложности
Совет 2: делайте прототип модели данных, готовьте исходные таблицы в PQ, анализируте данные в DAX. Некоторые вычисления можно и нужно делать в PQ (группировки, например), но большая часть вычислений быстрее делается в DAX. В общем, задача PQ - подготовить модель данных, а анализ - DAX
Совет 3: Поменьше расчетных доп.столбцов (в 90% случаев нужен не столбец, а мера).
F1 творит чудеса
Страницы: 1
Наверх