Power Query基于另一列转换一列 [英] Power Query Transform a Column based on Another Column

问题描述

我一直认为这应该很容易，但是答案在逃避我.在Excel Power Query中，我想基于另一列的值来转换列的每一行中的值.例如，假设我的Table1如下:

I keep thinking this should be easy but the answer is evading me. In Excel Power Query, I would like to transform the value in each row of a column based on another column's value. For example, assume I have Table1 as follows:

Column A | Column B
-------------------
X        | 1
Y        | 2

我想基于列B中的值来转换列A中的值，而不必添加新列并替换原始列A.我尝试使用TransformColumns，但是输入只能是目标列的值-我无法从TransformColumns函数中访问行/记录中的其他字段值.我会希望能够做这样的事情:

I would like to transform the values in Column A based on the values in Column B, without having to add a new column and replace the original Column A. I have tried using TransformColumns but the input can only be the target column's value - I can't access other field values in the row/record from within the TransformColumns function. I would like to be able to do something like this:

=Table.TransformColumns(Table1, {"Column A", each if [Column B]=1 then "Z" else _ })

这将导致:

Column A | Column B
-------------------
Z        | 1
Y        | 2

我知道有很多方法可以做到这一点，但是我试图找到一种步骤/转换最少的方法.例如，我知道我可以使用Table.AddColumn基于查看列B的函数添加 new 列A，但是随后我必须删除原始的列A并将其替换为新的A列需要执行其他多个步骤.

I know there are ways to do this, but I'm trying to find one with the least amount of steps/transformations. For example, I know I could use Table.AddColumn to add a new Column A based on a function that looks at Column B, but then I have to remove the original Column A and replace it with the new Column A which requires multiple additional steps.

推荐答案

这就是我最终这样做的方式:

Here is how I ended up doing this:

Table1:
Column A | Column B
-------------------
X        | 1
Y        | 2

= Table.FromRecords(Table.TransformRows(Table1,
    (r) => Record.TransformFields(r,
        {"A", each if r[Column B]="1" then "Z" else _})))

结果:

Column A | Column B
-------------------
Z        | 1
Y        | 2

这样，您可以使用Record.TransformFields函数中的嵌套列表一次转换多列.

This way you can transform multiple columns at once by using a nested list in the Record.TransformFields function.

这篇关于Power Query基于另一列转换一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！