强大的查询:通过乘以另一列来变换一列 [英] Power Query: transform a column by multiplying by another column

问题描述

我想做类似于 Power Query Transform a基于另一列"的列，但是我对如何针对特定目标修改语法感到困惑.

I want to do something similar to Power Query Transform a Column based on Another Column, but I'm getting stuck on how to modify the syntax for my particular goal.

类似于链接的问题，假设我有下表:

Similar to the linked question, assume that I have the following table:

Table 1:
Column A | Column B | Column C
------------------------------
 1       | 4        | 7
 2       | 5        | 8
 3       | 6        | 9

我不想改变列B的条件来更改列A的值，而是将多个列(列B和列C)中的值乘以列A中的值，并替换初始列中的值，这样我就可以得到以下信息:

Instead of changing the value of the Column A conditional on Column B, I want to multiply the values in multiple columns (Column B and Column C) by those in Column A and replace the values in the initial columns so that I can get the following:

Table 1:
Column A | Column B | Column C
------------------------------
 1       | 4        | 7
 2       | 10       | 16
 3       | 18       | 27

是否可以在不使用Table.AddColumn后跟Table.RemoveColumns的多个序列的情况下执行此操作?

Is this possible to do without using multiple sequences of Table.AddColumn followed by Table.RemoveColumns?

我还基于

I have also tried Table.TransformColumns based on this, but not been able to get the syntax right to achieve this.

推荐答案

Table.TransformColumns不会给您Column A，只有在您的列仅包含唯一数据的情况下，这才有可能.

Table.TransformColumns won't give you Column A unless you can index back into the table, which will only be possible if your columns only have unique data.

Table.TransformRows将让您使用所需的任何逻辑来构建新行:

Table.TransformRows will let you build new rows with whatever logic you want:

let
    Source = Csv.Document("Column A,Column B,Column C
        1,4,7
        2,5,8
        3,6,9"),
    PromotedHeaders = Table.PromoteHeaders(Source),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"Column A", type number}, {"Column B", type number}, {"Column C", type number}}),

    MultipliedRows = Table.FromRecords(Table.TransformRows(ChangedType, 
        each [
            Column A = [Column A],
            Column B = [Column A] * [Column B],
            Column C = [Column A] * [Column C]
        ]))
in
    MultipliedRows

这对于B和C列效果很好，但是如果您需要B 至 Z，则您可能希望采用更合理的逻辑来避免重复自己.

This works well for columns B and C, but if you need B through Z you might want fancier logic to avoid repeating yourself.

对于许多列，更通用的解决方案是对除"Column A"之外的所有列名，在转换列表上使用Record.TransformFields.

A more general solution for many columns is to use Record.TransformFields on a list of transforms for all column names except "Column A".

let
    Source = Csv.Document("Column A,Column B,Column C,D,E,F
        1,4,7,1,2,3
        2,5,8,4,5,6
        3,6,9,7,8,9"),
    PromotedHeaders = Table.PromoteHeaders(Source),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"Column A", type number}, {"Column B", type number}, {"Column C", type number}, {"D", type number}, {"E", type number}, {"F", type number}}),

    MultipliedRows = Table.FromRecords(Table.TransformRows(ChangedType, (row) => 
        let
            ColumnA = row[Column A],
            OtherColumns = List.RemoveItems(Record.FieldNames(row), {"Column A"}),
            Transforms = List.Transform(OtherColumns, (name) => { name, (cell) => cell * ColumnA })
        in
            Record.TransformFields(row, Transforms)))
in
    MultipliedRows

这篇关于强大的查询:通过乘以另一列来变换一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！