I discovered and started using Power Query yesterday. I have written some queries to populate three drop down lists, with the second and third dependent on what is selected in the first, and the third then further dependent on what is selected in the second.
When I am creating the list of possible items in these lists I am adding an “All” record at the top of each as part of my queries. I have used an if statement when populating the second list that essentially says if user has selected All in the first box, go get all records, otherwise bring back records where the value in a particular column equals what has been selected in that first list.
However, as the third list requires a check to see if “All” has been selected in both the first and second list box, I believe I need to do two if statements. I have searched the internet all afternoon yesterday to find an example of two nested if statements with limited success, and whenever I try to do it myself I get a variety of syntax errors that I am missing a token Eof or token then or a comma is missing and so on.
Would anyone be able to show me a simple example of how to lay out nested if statements please?
Thank you
UPDATE:
Thank you for your answer. I am still having real problems. Here is my query, which currently works.
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"
I know my layout is awful - something to work on. I would much rather it looked like this:
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"
Its saying a token Eof is expected, and when I have tried to wrap the entire thing in another "let" and "in", it says a token comma is expected on the first #"GroupedSource". I have tried all sorts of additional "in" and "let" inclusions but clearly I am missing a fundamental understanding of how "let", "in" and assigning answers to "variables" works.
Do you know what I am doing wrong?
Looks like you can achieve the same with Table.SelectRows (the Power Query equivalent to a filter in Excel). Your code modified below.
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
NarrowHope it helps. You might want to use data validation on the Wholesaler_Cmbo_Lnkd_Cell input.
Jake