Power Query Excel показывает значения в процентах от столбца

Question

Обычно сводные таблицы используются для представления данных в определенном порядке. Для этого конкретного "вопроса" мне нужно представить цифры и цель. Это конфликтует в Pivots, поскольку процент столбца также учитывает цель.

Не беспокойтесь, с PowerPivot должна быть возможность решить эту проблему. До сих пор мне удалось создать таблицу со следующим макетом:

         1     2    3
cat A    5    10    7
cat B    10    8    9
cat C    0     2    1

Где 1, 2 и 3 - первые три дня месяца (для простоты остальное не учтено).

Я могу получить итоги столбцов следующим образом:

= Table.FromRows({List.Transform({"1","2","3"}, each List.Sum(Table.Column(prevStep, _)))}, {"1","2","3"})

Кроме того, я могу разделить каждое значение столбца на число:

= Table.TransformColumns(prevStep, List.Transform({"1","2","3"}, each {_, (this) =>  this / 42, type number}))

Теперь я хотел бы заменить 42 итоговыми значениями, рассчитанными ранее для столбцов столбцов. Обратите внимание, что "{" 1 "," 2 "," 3 "}" будут вычислены автоматически на следующем шаге.

Может кто-нибудь уточнить, как этого добиться? Ожидаемый результат:

         1     2    3
cat A    0.33  0.5    0.41
cat B    0.67  0.4    0.53
cat C    0     0.1    0.06

Answer 1

@Marc Pince; спасибо за ваши предложения. После некоторой борьбы я смог приготовить следующее:

Сначала я поменял строку расчета общей суммы следующим образом:

totMinDay =  Table.ToRows    (Table.FromRows({List.Transform(daysOfMonth , each List.Sum(Table.Column(prevStep, _)))}, daysOfMonth )){0},

Где daysOfMonth - это дни этого фактического месяца, полученные в другом месте.

Далее я рассчитываю процент следующим образом:

perc= Table.TransformColumns(prevStep, List.Transform(daysOfMonth , each {_, (this) =>  Number.Round(this/ ( totMinDay {Number.FromText( _ ) - 1} ), 3), type number})),

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

Спасибо за все вклады.

Answer 2

В этом случае вы можете получить доступ к имени столбца, используя конструкцию: Table.Column(TableName, ColumnName).

В вашем примере это будет: Table.Column(prevStep, _), где "_" обращается к каждому столбцу.

Итак, ваш код должен быть таким:

      = Table.FromRows({List.Transform({"1","2","3"}, each List.Sum(Table.Column(prevStep, _)))}, {"1","2","3"})
= Table.TransformColumns(prevStep, List.Transform({"1","2","3"}, each {_, (this) =>  this / Table.Column(prevStep, _), type number}))

Answer 3

Я не мог придумать способ просто заменить 42 в вашем коде, но так как я увидел, что это математическая математика, я основал это решение для вашей проблемы ниже на блоге Гила Равива о умножении матриц.

Я начал с этого как Таблица1:

Затем, чтобы добраться до этого...

... Я использовал Table1 в качестве источника для нового запроса, и этот код:

let
    Source = Table1,
    #"Removed Columns" = Table.RemoveColumns(Source,{""}),
    MatrixA = Table.TransformColumnTypes(#"Removed Columns",{{"1", type number}, {"2", type number}, {"3", type number}}),
    Summed = Table.FromRows({List.Transform({"1","2","3"}, each List.Sum(Table.Column(MatrixA, _)))}, {"1","2","3"}),
    MatrixB = Table.TransformColumnTypes(Summed,{{"1", type number}, {"2", type number}, {"3", type number}}),
    IndexedMatrixA = Table.AddIndexColumn(MatrixA, "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(IndexedMatrixA, {"Index"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", Int64.Type}}),
    RenamedColumnsMatrixA = Table.RenameColumns(#"Changed Type",{{"Index", "Row"}, {"Attribute", "Column"}}),
    IndexedMatrixB = Table.AddIndexColumn(MatrixB, "Index", 1, 1),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(IndexedMatrixB, {"Index"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns1",{{"Attribute", Int64.Type}}),
    RenamedColumnsMatrixB = Table.RenameColumns(#"Changed Type1",{{"Index", "Row"}, {"Attribute", "Column"}}),
    #"Merged Queries" = Table.NestedJoin(RenamedColumnsMatrixA,{"Column"},RenamedColumnsMatrixB,{"Column"},"RenamedColumnsMatrixB",JoinKind.LeftOuter),
    #"Expanded RenamedColumnsMatrixB" = Table.ExpandTableColumn(#"Merged Queries", "RenamedColumnsMatrixB", {"Row", "Column", "Value"}, {"B.Row", "B.Column", "B.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded RenamedColumnsMatrixB", "AB", each [Value]/[B.Value]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Row", "B.Column"}, {{"AB", each List.Sum([AB]), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"B.Column", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"B.Column", type text}}, "en-US")[B.Column]), "B.Column", "AB"),
    #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Index", 1, 1),
    IndexedSource = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index",{"Index"},IndexedSource,{"Index"},"IndexedSource",JoinKind.LeftOuter),
    #"Expanded IndexedSource" = Table.ExpandTableColumn(#"Merged Queries1", "IndexedSource", {""}, {"Column1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded IndexedSource",{"Row", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Column1", "1", "2", "3"})
in
    #"Reordered Columns"

Вы заметите, что я немного "перепрыгнул", используя предыдущие Applied Steps (которые в основном являются состояниями таблицы запросов) в качестве "таблиц" в коде... как в этих Applied Steps:

• Индекс MatrixB (IndexedMatrixB = Table.AddIndexColumn(MatrixB, "Index", 1, 1), где MatrixB предыдущий Прикладной Шаг); а также

• Объединенные запросы (#"Merged Queries" = Table.NestedJoin(RenamedColumnsMatrixA,{"Column"},RenamedColumnsMatrixB,"Column"},"RenamedColumnsMatrixB",JoinKind.LeftOuter), где RenamedColumnsMatrixA а также RenamedColumnsMatrixB являются предыдущими Приложенными Шагами).