我有非常大的文件,PowerQuery 似乎可以很好地处理这些文件.我需要使用列 d 和来自列 a、b 或 c 的值进行一些数学运算,基于key 列的值.我的第一个想法是隔离显着值,创建一个名为 Salient 的列,它选择我需要的值,然后从那里开始.在 Excel 中,这可能是:=INDEX($A:$E, ROW(F2), MATCH(A2,$A$1:$D$1)).
I have very large files which PowerQuery seems to handle nicely. I need to do some mathematical operations using column d and the value from columns a, b or c based on the value of the key column. My first thought is to isolate the salient value making a column called Salient which selects the value I need and then go from there. In Excel, this might be: =INDEX($A:$E, ROW(F2), MATCH(A2,$A$1:$D$1)).
实际上,我有 50 到 100 列以及数百万行,因此计算效率加分.
In reality, I have between 50 and 100 columns as well as millions of rows, so extra points for computational efficiency.
推荐答案您可以定义一个自定义列 Salient ,只需将其作为定义:
You can define a custom column Salient with just this as the definition:
Record.Field(_, [Key])整个步骤的 M 代码如下所示:
The M code for the whole step looks like this:
= Table.AddColumn(#"Prev Step Name", "Salient", each Record.Field(_, [Key]), Int64.Type)_代表当前行,是一种记录数据类型,可以表示为例如
The _ represents the current row, which is a record data type that can be expressed as e.g.
[Key = "a", a = 17, b = 99, c = 21, d = 12]并且您使用 Record.Field 来选择Key 对应的字段.
and you use Record.Field to pick the field corresponding to the Key.
更多推荐
PowerQuery 根据键列选择值
发布评论