Table.TransformColumns
不会给你Column A
除非您可以索引回表,这只有在您的列仅具有唯一数据的情况下才可能实现。
Table.TransformRows
将允许您使用您想要的任何逻辑构建新行:
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 列效果很好,但如果您需要 BthroughZ 你可能需要更奇特的逻辑来避免重复自己。
编辑:对于许多列来说,更通用的解决方案是使用Record.TransformFields
在所有列名称的转换列表上,除了"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