强大的查询:通过乘以另一列来变换一列 [英] 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屋!