Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
PQ.Автоматическое добавление новых столбцов в расчет
 
#1
04.06.2020 14:20:58
Здравствуйте, уважаемые знатоки!
В столбцах исходной таблицы есть данные по Доходу, Расходу нескольких продуктов
По каждому продукту нужно получить столбцы Доход-Расход и Доход/Расход
Подскажите, пожалуйста, как лучше автоматизировать процесс добавления этих расчетных столбцов по входящему списку Продуктов?
Пытался сделать с помощью List.Generate, но не понял, как сделать динамическое название столбцов и развертывание в строки
В боевом файле >200 тыс строк, 10 продуктов и 5 вычисляемых столбцов
Код
let
    Продукты = {"Продукт1", "Продукт2","Продукт3"},
    таблица= Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    ListGen = 
    Table.AddColumn(таблица, "Тест", (rec)=>
         Table.FromRecords(
                            List.Generate(()=>
                                              [i=0 
                                               , доход=try Record.Field(rec,"Доход"&Продукты{i}) otherwise 0
                                               , расход=try Record.Field(rec,"Расход"&Продукты{i}) otherwise 0
                                              ]
                                             , each [i]<List.Count(Продукты)
                                             , each [ 
                                                      i=[i]+1
                                                    , доход=try Record.Field(rec,"Доход"&Продукты{i}) otherwise 0
                                                    , расход= try Record.Field(rec,"Расход"&Продукты{i}) otherwise 0
                                                    ]
                                             , each [Прод = Продукты{[i]}
                                                     ,#"Доход-Расход"=[доход]-[расход]
                                                     ,#"Расход/Доход"=[расход]/[доход]
                                                     ]
                                          ) 
                          ))
in
    ListGen
Изменено: Student64 - 04.06.2020 14:21:39
 
 
 
#2
04.06.2020 14:43:49
Student64, а на фига так усложнять? не проще через Pivot/Unpivot?
Вот горшок пустой, он предмет простой...
 
 
 
#3
04.06.2020 14:47:31
PooHkrd, по ощущениям, Pivot/Unpivot очень тормозит... объем большой. Думал, что с list.generate можно шустрее
 
 
 
#4
04.06.2020 15:01:22
Student64, вы с какой-то целью задачи решаемые сводными отчетами через PowerPivot пытаетесь решать через PQ. Зачем? Это ETL инструмент, используйте его по назначению, загрузите 2 таблицы с доходами и расходами в модель данных, соедините их через справочники, а потом считайте мерами в сводной все что заблагорассудится.
Вот горшок пустой, он предмет простой...
 
 
 
#5
04.06.2020 15:02:35
Student64, по старинке:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"Клиент"}, "Атрибут", "Сумма"),
    split = Table.SplitColumn(unpivot, "Атрибут", (x)=>let a = if Text.StartsWith(x,"Д") then "Доход" else "Расход" in {a, Text.Split(x,a){1}}, {"Тип", "Продукт"}),
    pivot = Table.Pivot(split, List.Distinct(split[Тип]), "Тип", "Сумма"),
    replace = Table.ReplaceValue(pivot,null,0,Replacer.ReplaceValue,{"Доход","Расход"}),
    add = Table.AddColumn(replace, "Доход - Расход", each List.Sum({[Доход],[Расход]})),
    add1 = Table.AddColumn(add, "Расход/Доход", each [Расход]/[Доход]),
    unpivot1 = Table.UnpivotOtherColumns(add1, {"Клиент", "Продукт"}, "Атрибут", "Сумма"),
    merge = Table.CombineColumns(unpivot1,{"Атрибут","Продукт"},Text.Combine,"temp"),
    pivot1 = Table.Pivot(merge, List.Distinct(merge[temp]), "temp", "Сумма")
in
    pivot1
Порядок столбцов другой, но, возможно, так даже сподручней.
 
 
 
#6
04.06.2020 15:05:38
Aleksei_Zhigulin, спасибо, но хотелось что то побыстрее pivot / unpivot...Если это возможно
 
 
 
#7
04.06.2020 15:07:07
PooHkrd, это один из промежуточных этапов расчетов, подготовка перед pivot
 
 
 
#8
04.06.2020 15:30:06
Student64, не надо такие вещи готовить для PP, оно само их считает причем разительно быстрее PQ.
Вот горшок пустой, он предмет простой...
 
 
 
#9
04.06.2020 15:31:06
Цитата
Student64 написал:
хотелось что то побыстрее pivot / unpivot
Если скорость - проблема, тогда, как и предложил PooHkrd, лучше уж в Power Pivot.
 
 
 
#10
04.06.2020 15:40:05
PooHkrd, спасибо
я понимаю, что такой расчет легко будет крутиться на PP, но
1 - добавление этих столбцов - это лишь промежуточный этап обработки.Потом применяются разные join и доп. обработки( с учетом значений, полученных в этих столбцах)
2- даже в PQ нет проблем для этих формул если вбивать их в лоб (они простые). Я понимаю, что сложные вычисления должны жить в PP
Я лишь хотел уйти от ручного ввода формул по каждому продукту(оптимизировать/навести красоту в редакторе)


upd: Алексей, и по вашему примеру, зачем здесь FILTER? По-моему, без него тот же результат. Или он на что то влияет?
Код
Доход:=CALCULATE( SUM( PL[Сумма] ); FILTER( 'PL'; 'PL'[Тип] = "Доход" ) )
Код
Доход:=CALCULATE( SUM( PL[Сумма] ); 'PL'[Тип] = "Доход" )
Изменено: Student64 - 04.06.2020 15:59:34
 
 
 
#11
04.06.2020 15:46:51
Aleksei_Zhigulin, проблем со скоростью нет, если вбивать вручную) Если нет какого то волшебного LIst.Generate или ему подобных, то попробую через пользовательскую функцию, которая будет добавлять нужные столбцы при вводе названия продукта
 
 
 
#12
04.06.2020 15:56:51
Цитата
Student64 написал:
Потом применяются разные join и доп. обработки( с учетом значений, полученных в этих столбцах)
Вот! Все что вы сказали, РР чудесно и быстро считает за счет связей таблиц. Вы объясните лучше что за задача стоит, а не как по вашему нужно её решать.
Вот горшок пустой, он предмет простой...
 
 
 
#13
04.06.2020 16:14:41
PooHkrd, например, полученные данные участвуют в такой конструкции ссылка, то ее тоже на PP нужно переносить?
Далее, функции по обработке текста, прочее...
Т.е. у меня пока нет сомнений, что этот этап должен жить на PQ, хотелось чуть попроще
 
 
 
Страницы: 1
Наверх