英文:

power query editor - Replacing values in select columns based on multiple conditions

问题

我正在使用Power Query编辑器工作。我希望有人能帮助我替换数值。我的真实数据集包含成千上万行和多达30列，但我已经提供了下面的一个简化示例数据集。

我想要根据多个条件替换多列中的值。对于这个示例... 当Row2 = A时，我想将Column2和Column4中的空值替换为零。如果Row2不等于A，那么我想保留单元格中的原始值。

在我的数据集中，我需要同时为多列执行此操作（不仅仅是两列），所以我更愿意一次性完成这个操作，而不是为每列创建一个替换步骤。谢谢！

为了更清楚明白：多个条件是...

• Column1的值- 在这个示例中，我说它必须是A。我的真实数据集中有六种可能的值，对于这一列的每个值，我都需要对不同的列执行替换操作。
• 要替换的列- 在这个示例中，我说它必须是Column2和Column4。
• 要替换的单元格的值必须为空。

我认为我需要执行6个单独的替换步骤（针对Column1的每个可能值一个）。

英文:

I am working in power query editor. I was hoping someone could help me with replacing values. My real data set contains thousands of rows and upwards of 30 columns, but i have provided a simplified example data set below.

I would like to replace values in multiple columns based on multiple conditions
For this example... I would like to replace the null values in Column2 and Column4 with a zero, ONLY when Row2 = A.
If Row2 does not equal A, then I would like to keep the original value in the cell.

In my data set, I will need to do this for many columns at once (not just two) so I would prefer to have this done in a single step, rather than creating a step for replacing within each column. Thank you!

Edit for clarity: the multiple conditions are...

• The value of Column1- In this example, I said that it must be A. My real data set has six possible values for this column, and I will need to perform replacements on different columns for different values of Column1.
• The columns being replaced- In this example, I said that it must be Column2 and Column4.
• The value of the cell being replaced must be null.

I think I will need to perform 6 separate replacement steps (one for each possible value of Column1)

答案1

得分: 1

根据您最近的评论，请尝试修改以下代码。仅将适当的 List.Transform 参数连接到 Record.TransformFields 函数，以进行不同的更改。

我只是使用了您展示的列，但是代码应该很容易根据您的实际需求进行修改。

let
    // 更改下一行以反映实际数据源
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],

    // 将适应任意列数的代码，假设列2到n为整数类型
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type})),

    // 请注意，我们已经创建了一个要转换的列的列表，而不是硬编码每个单独的列
    // 在您的示例中，您提到了列2和列4，所以我使用了List.Alternate来引用偶数列
    // 但您可以以许多不同的方式构建列表。
    #"Replace nulls" = Table.TransformRows(#"Changed Type", (r)=>
        Record.TransformFields(r, 
            List.Transform({"Column2","Column4"}, 
                each {_, each if r[Column1] = "A" and _ = null then 0 else _})
            & List.Transform({"Column3"},
                each {_, each if r[Column1] = "B" and _ = null then 0 else _}))),

    #"Converted to Table" = Table.FromList(#"Replace nulls", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Table.ColumnNames(Source)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type}))

in
    #"Changed Type1"

<details>
<summary>英文:</summary>

Based on your recent comment, try modifying the code below. Merely concatenate appropriate `List.Transform` arguments to the `Record.TransformFields` function for your different changes.

I just used the columns you showed, but the code should be easy to modify for your actual requirements.

let

//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],

//will adjust to any number of columns, assuming columns 2..n are integer types
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type})),

//Note we have created a list for the columns to be transformed instead of hard coding each individual column
//In your example, you referred to columns 2 and 4, so I used List.Alternate to refer to the even-numbered columns
//But you can construct the list in many different ways.
#"Replace nulls" = Table.TransformRows(#"Changed Type", (r)=>
Record.TransformFields(r,

        List.Transform({"Column2","Column4"}, 
            each {_, each if r[Column1] = "A" and _ = null then 0 else _})
            
        & List.Transform({"Column3"},
            each {_, each if r[Column1] = "B" and _ = null then 0 else _}))),
        
        
#"Converted to Table" = Table.FromList(#"Replace nulls", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Table.ColumnNames(Source)),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type}))

in
#"Changed Type1"

[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/rQxy8.png

</details>



# 答案2
**得分**: 0

你可以按如下修改 Power Query。如果要在其他列应用更改，可以在“每个 List.Contains( {**“Col2”、“Col3”**}”中添加列名。

let
Source = Excel.Workbook(File.Contents("C:\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col1", type text}, {"Col2", Int64.Type}, {"Col3", Int64.Type}}),
Custom1 = Table.ReplaceValue(#"Promoted Headers", each [Col1]="A", 0, (x,y,z)=> if y then z else x, List.Select(Table.ColumnNames(#"Changed Type"),
each List.Contains( {"Col2","Col3"},_)
)
)
in
Custom1

<details>
<summary>英文:</summary>

You can modify the power query as below. You can add the column name in &quot;each  List.Contains( {**&quot;Col2&quot;,&quot;Col3&quot;**}&quot; if you want to apply the changes in other columns

let
Source = Excel.Workbook(File.Contents("C:\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col1", type text}, {"Col2", Int64.Type}, {"Col3", Int64.Type}}),
Custom1 = Table.ReplaceValue(#"Promoted Headers", each [Col1]="A", 0, (x,y,z)=> if y then z else x, List.Select(Table.ColumnNames(#"Changed Type"),
each List.Contains( {"Col2","Col3"},_)
)
)
in
Custom1

</details>