一个人如何有条件地替换PowerQuery中的一些选定的列

Question

我有一组数据，希望替换一些包含值1、1，f的列。 每当PowerQuery看到分别包含1,1的彼此相邻的三列集时，我希望它替换一组值中的一组列，以说0、0， * 我知道如何用另一个值替换值，只要在这些值显示的情况下，我就想要BUH，以将其替换为集合。 我一直很强烈解决这些问题，有时是无济于事的。

单击此处，查看挑战：

https://ibb.co/bf3zgrn

谢谢。

Answer 1

此步骤用0替换指定列的值，其中满足了指定条件：

= Table.ReplaceValue(#"Previous Step", each [exammth] = 1 and [mth] = 1 and [mthgrade] = "F", 0, (x,y,z)=> if y then z else x, List.Select(Table.ColumnNames(#"Previous Step"), each List.Contains({"exammth","mth","mthgrade"},_)))

Answer 2

Can be done with complicated formula, but simple way to do what you want is:

Add column .. custom column .. with formula to test those 3 columns

if [exammth] = 1 and [mth] =1 and  [mthgrde] ="F" then "b" else "a"

filter for the bs using the arrows atop the column

右键单击并将三列转换为0,0，*
For the numericals, choose any transform function then change the formula to be each 0, for the alpha column use each "*"

recombine with the original data, filtered for the < strong>as

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Class", type text}, {"camth", Int64.Type}, {"exammth", Int64.Type}, {"mth", Int64.Type}, {"mthgrde", type text}, {"caecons", Int64.Type}, {"examecons", Int64.Type}, {"ecpms", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [exammth] = 1 and [mth] =1 and  [mthgrde] ="F" then "b" else "a"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "b")),
Replace1 = Table.TransformColumns(#"Filtered Rows",{{"exammth", each 0, Int64.Type}}),
Replace2 = Table.TransformColumns(Replace1,{{"mth", each 0, Int64.Type}}),
Replace3 = Table.TransformColumns(Replace2,{{"mthgrde", each "*", type text}}),
combined = Replace3 &  Table.SelectRows(#"Added Custom", each ([Custom] = "a") )
in  combined