PowerQuery 根据键列选择值 [英] PowerQuery choose values based on a key column

问题描述

我有非常大的文件，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 根据键列选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！