英文:
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 "each List.Contains( {**"Col2","Col3"**}" 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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论