Блог

Главная - Вопросы по программированию - Запрос мощности Excel. Разные формулы для каждой строки значений из других строк

Запрос мощности Excel. Разные формулы для каждой строки значений из других строк

#excel #powerquery

#преуспеть #запрос мощности

Вопрос:

У меня есть таблица 1 из более чем 500 строк, которые обновляются несколько раз в течение месяца по запросу. И есть обновленная таблица с различными формулами (более 200), которые должны быть рассчитаны с использованием значений из таблицы 1.

Таблица 1

год продукт значение
1993 Apple 98.45
1994 Грибы 67.54
1992 Apple 95.45
2021 Дыня 112.0

Таблица 2

ID формула
1 1994/1993*100-100
2 1994-1993
3 2021/1992*100-100

и т.д…

Мой способ:

  1. в таблице table_1 я создал ключевой столбец — год.
  2. Я изменил формулу (ниже — рисунок):

таблица_2

Код для таблицы_2:

    let
        src = Excel.CurrentWorkbook(){[Name="table_2"]}[Content],
        pq_auto1 = Table.PromoteHeaders(src, [PromoteAllScalars=true]),
        pq_auto2 = Table.TransformColumnTypes(pq_auto1,{{"Id", Int64.Type}, {"formula", type text}}),
        result = Table.AddColumn(pq_auto2,"value",each Expression.Evaluate([modified_formula],#shared))
   in
        result

Можете ли вы предложить мне найти лучший способ выполнить эти вычисления по каждой строке?

Ответ №1:

Это невозможно сделать так же, как с формулой Excel, однако BI Accountant показал элегантное решение, сдвинув столбец на одну строку:

введите описание изображения здесь

 let
     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
    ,ShiftedList = {null} amp; List.RemoveLastN(Table.Column(Source, "ColumnToBeShifted"), 1)
    ,NewShiftedColumn = Table.FromColumns(Table.ToColumns(Source) amp; {ShiftedList}, Table.ColumnNames(Source) amp; {"NewColumnName"})
in
    NewShiftedColumn

При этом ваши вычисления должны быть очень простыми.