代码之家 › 专栏 › 技术社区 › mickeyt

Power Query中的嵌套if语句

powerquery excel-2013

mickeyt · 技术社区 · 8 年前

我昨天发现并开始使用Power Query。我编写了一些查询来填充三个下拉列表,第二个和第三个依赖于第一个列表中选择的内容,第三个则进一步依赖于第二个列表中选择的内容。

当我在这些列表中创建可能项目的列表时,我会在每个列表的顶部添加一条All记录,作为查询的一部分。我在填充第二个列表时使用了if语句 如果用户在第一个框中选择了“全部”,请转到“获取所有记录”,否则将返回特定列中的值等于在第一个列表中选择的值的记录 .

然而,由于第三个列表需要检查第一个和第二个列表框中是否都选择了All,因此我认为需要执行两个if语句。昨天下午,我在互联网上搜索了一个两个嵌套if语句的示例,但成功率有限。每当我自己尝试这样做时,就会出现各种语法错误,比如我缺少一个标记Eof或token then,或者缺少一个逗号等等。

有谁能给我举一个简单的例子,说明如何布置嵌套的if语句吗?

非常感谢。

更新:

谢谢你的回答。我仍然有真正的问题。这是我的查询,目前有效。

if Wholesaler_Cmbo_Lnkd_Cell <> "All wholesalers" then

let

    Source = Table.SelectRows(Query_GSR, each [Wholesaler number] = Wholesaler_Cmbo_Lnkd_Cell),
    #"GroupedSource" = Table.Group(Source, {"Month_Name", "Year_No", "Item Category", "Item Category Type", "Tariff status", "Weekday","Transaction type","Group number","Customer account number"}, {{"Gross sales", each List.Sum([Gross sales])},
                                                                                                                                            {"Rebates", each List.Sum([Rebates])},
                                                                                                                                            {"Net sales", each List.Sum([Net sales])},
                                                                                                                                            {"Direct materials", each List.Sum([Direct materials])},
                                                                                                                                            {"Packaging", each List.Sum([Packaging])},
                                                                                                                                            {"Lab consumables", each List.Sum([Lab consumables])},
                                                                                                                                            {"QC costs", each List.Sum([QC costs])},
                                                                                                                                            {"Stock adjustments", each List.Sum([Stock adjustments])},
                                                                                                                                            {"Product margin", each List.Sum([Product margin])},
                                                                                                                                            {"Gross profit", each List.Sum([Gross profit])},
                                                                                                                                            {"Carriage income", each List.Sum([Carriage income])},
                                                                                                                                            {"Carriage expense", each List.Sum([Carriage expense])},
                                                                                                                                            {"Orders", each List.Count([ID])}})
in

    #"GroupedSource" 

else

let

    Source = Query_GSR,
    #"GroupedSource" = Table.Group(Source, {"Month_Name", "Year_No", "Item Category", "Item Category Type", "Tariff status", "Weekday", "Transaction type", "Group number", "Customer account number"}, {{"Gross sales", each List.Sum([Gross sales])},
                                                                                                                                            {"Rebates", each List.Sum([Rebates])},
                                                                                                                                            {"Net sales", each List.Sum([Net sales])},
                                                                                                                                            {"Direct materials", each List.Sum([Direct materials])},
                                                                                                                                            {"Packaging", each List.Sum([Packaging])},
                                                                                                                                            {"Lab consumables", each List.Sum([Lab consumables])},
                                                                                                                                            {"QC costs", each List.Sum([QC costs])},
                                                                                                                                            {"Stock adjustments", each List.Sum([Stock adjustments])},
                                                                                                                                            {"Product margin", each List.Sum([Product margin])},
                                                                                                                                            {"Gross profit", each List.Sum([Gross profit])},
                                                                                                                                            {"Carriage income", each List.Sum([Carriage income])},
                                                                                                                                            {"Carriage expense", each List.Sum([Carriage expense])},
                                                                                                                                            {"Orders", each List.Count([ID])}})
in

    #"GroupedSource"

我知道我的布局很糟糕,需要改进。我宁愿它看起来像这样:

if Wholesaler_Cmbo_Lnkd_Cell <> "All wholesalers" then

let

    Source = Table.SelectRows(Query_GSR, each [Wholesaler number] = Wholesaler_Cmbo_Lnkd_Cell)

in

    Source

else

let

    Source = Query_GSR

in

    Source

let

    #"GroupedSource" = Table.Group(Source, {"Month_Name", "Year_No", "Item Category", "Item Category Type", "Tariff status", "Weekday", "Transaction type", "Group number", "Customer account number"}, {{"Gross sales", each List.Sum([Gross sales])},
                                                                                                                                            {"Rebates", each List.Sum([Rebates])},
                                                                                                                                            {"Net sales", each List.Sum([Net sales])},
                                                                                                                                            {"Direct materials", each List.Sum([Direct materials])},
                                                                                                                                            {"Packaging", each List.Sum([Packaging])},
                                                                                                                                            {"Lab consumables", each List.Sum([Lab consumables])},
                                                                                                                                            {"QC costs", each List.Sum([QC costs])},
                                                                                                                                            {"Stock adjustments", each List.Sum([Stock adjustments])},
                                                                                                                                            {"Product margin", each List.Sum([Product margin])},
                                                                                                                                            {"Gross profit", each List.Sum([Gross profit])},
                                                                                                                                            {"Carriage income", each List.Sum([Carriage income])},
                                                                                                                                            {"Carriage expense", each List.Sum([Carriage expense])},
                                                                                                                                            {"Orders", each List.Count([ID])}})


in

    #"GroupedSource"

它说需要一个标记Eof,当我试图将整个内容包装在另一个“let”和“in”中时,它说第一个#“GroupedSource”上需要一个标记逗号。我尝试了各种额外的“in”和“let”包含,但很明显,我对“let”、“in”以及为“variables”指定答案的工作方式缺乏基本的理解。

你知道我做错了什么吗?

3 回复 | 直到 8 年前

JJB 8 年前

看起来您可以使用Table实现同样的效果。SelectRows(相当于Excel中过滤器的Power查询)。下面修改了您的代码。

let
    Source = Query_GSR,
    
    #"Added Custom" = Table.AddColumn(Source, "Orders", each if [Transaction type] = "Credit" then -1 else 1),
    
    Narrow = if Wholesaler_Cmbo_Lnkd_Cell = "" then #"Added Custom"
        else if Wholesaler_Cmbo_Lnkd_Cell = "All wholesalers" then #"Added Custom"
        else Table.SelectRows(#"Added Custom", each [Wholesaler number] = Wholesaler_Cmbo_Lnkd_Cell)
    
in
    Narrow

希望有帮助。您可能希望对批发商\u Cmbo\u Lnkd\u单元输入使用数据验证。

满意的

Alexis Olson 8 年前

PowerQuery中的嵌套if语句如下所示:

if [condition1]
    then (if [condition2] then [true1-true2 result] else [true1-false2 result])
    else [false result]

您还可以将if添加到else条件:

if [condition1]
    then (if [condition2] then [true1-true2 result]  else [true1-false2 result])
    else (if [condition3] then [false1-true3 result] else [false1-false3 result])

mickeyt 8 年前

好的,这完全不同,这可能是我在学习如何正确使用Power Query。我使用以下代码实现了同样的效果:

let
    Source = Query_GSR,
    #"Added Custom" = Table.AddColumn(Source, "Orders", each if [Transaction type] = "Credit" then -1 else 1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WorkOutFilter", each if Wholesaler_Cmbo_Lnkd_Cell = "All wholesalers" then "Keep" else if [Wholesaler number] = Wholesaler_Cmbo_Lnkd_Cell then "Keep" else "Remove"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([WorkOutFilter] = "Keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"WorkOutFilter"})
in
    #"Removed Columns"

难以置信的我不需要像写代码那样思考,而应该像这样,在每条记录上使用if语句,而不是在代码中!

再次感谢你的帮助,这确实帮了大忙。