none
Output value only if parameter is not null RRS feed

  • Question

  • Hi

    Struggling with this for hours despite searches and tests so it's time to ask. Below script (shortened) works as expected. However I would like the last output "line" to be returned only if the varProduct (named range in XL workbook) parameter is not null:

    let
        // SOURCE & PARAMETER (value of named ranged "QryProduct")
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        varProduct = Excel.CurrentWorkbook(){[Name="QryProduct"]}[Content]{0}[Column1],

        (code removed)

        // Store Avg. Amount for PRODUCT = varProduct (AVERAGEIF) if at least 1 product matches, otherwise store "n/a"
        #"Filter on varProduct" = Table.SelectRows(#"Uppercased Product", each ([Product] = Text.Upper(varProduct))),
        AvgAmount_varProduct = if Table.RowCount(#"Filter on varProduct") = 0 then "n/a"
                               else List.Average(Table.Column(#"Filter on varProduct", "Amount")),

        // Output table w/desired measures
        #"Ouput" = #table(
            type table[Measures = text, Value = any],
            {
                {"Unique Products", NbUniqProd},
                {"Total Amount", TotalAmount},
                // IF varProduct is null do not output next line
                {"Avg. Product " & varProduct, AvgAmount_varProduct}
            }
        )
    in
        #"Ouput"

    Thanks in advance for any suggestion
    Cheers


    • Edited by Lz._ Tuesday, March 20, 2018 12:25 PM typo
    Tuesday, March 20, 2018 7:42 AM

Answers

  • Hi

    The UE in Powerquery is so easy to use that when we begin with it we may not see all the potential with M. It is the drawback: the UE provides some quick and dirty code that works very well, but it is hiding 90% of the power of PQ (like the macro recorder in Excel that allows 10% of what VBA can do).

    In your case, you need to structure your query and then you will easily get to the result you want:

    let
    
        #"Query in case 1" =
            let
                // Here is all your query in case the test is positive
                Source = "",
                // ...
                Output = "Result in Case 1"
            in Output,
    
        #"Query in case 2" =
                // Here is all your query in case the test is negative
            let
                Source = "",
                // ...
                Output = "Result in Case 2"
            in Output,
    
        #"Test" = 
                // Here is the test (based on your parameter)
            let
                Source = "",
                Output = true //or false
            in Output,        
    
        #"Result" =    if #"Test" 
                    then #"Query in case 1" 
                    else #"Query in case 2"
    
    
    in
    Result


    in your specific case, it would give something like the following, and of course you have to adapt according your needs and your inputs:

    let
    
        #"Query in case 1" =
            let
                // SOURCE & PARAMETER (value of named ranged "QryProduct")
                Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
                varProduct = Excel.CurrentWorkbook(){[Name="QryProduct"]}[Content]{0}[Column1],
    
                //(code removed)
    
                // Store Avg. Amount for PRODUCT = varProduct (AVERAGEIF) if at least 1 product matches, otherwise store "n/a"
                #"Filter on varProduct" = Table.SelectRows(#"Uppercased Product", each ([Product] = Text.Upper(varProduct))),
                AvgAmount_varProduct = List.Average(Table.Column(#"Filter on varProduct", "Amount")),
    
                // Output table w/desired measures
                #"Ouput" = #table(
                    type table[Measures = text, Value = any],
                    {
                        {"Unique Products", NbUniqProd},
                        {"Total Amount", TotalAmount}//,
                        // IF varProduct is null do not output next line
                        //{"Avg. Product " & varProduct, AvgAmount_varProduct}
                    }
                )
            in #"Output",
    
        #"Query in case 2" = 
                #table(
                    type table[Measures = text, Value = any],
                  {
                        //{"Unique Products", NbUniqProd},
                        //{"Total Amount", TotalAmount},
                        // IF varProduct is null do not output next line
                        {"Avg. Product " & varProduct, AvgAmount_varProduct}
                    }
    
     
        #"Test" = 
            // Here is the test (based on your parameter)
            let
                // Store Avg. Amount for PRODUCT = varProduct (AVERAGEIF) if at least 1 product matches, otherwise store "n/a"
                // ... the needed code here
                #"Filter on varProduct" = Table.SelectRows(#"Uppercased Product", each ([Product] = Text.Upper(varProduct))),
                #"TestAvgAmount_varProduct" = if Table.RowCount(#"Filter on varProduct") = 0 
                                       then true
                                       else false
    
            in TestAvgAmount_varProduct,
    
        #"Result" =    if #"Test" 
                    then #"Query in case 1" 
                    else #"Query in case 2"
    
    
    in
    Result


    Note1: the order of the "Main" queries is not relevant: you can put  the "Test" part at the top if you prefer.

    Note2: you can make as many alternatives as you may need, by using in the #"Result":
    if ... then ... else if ... then ... else if ... then ... else

    Note3: usually I prefer all the output alternatives to be under the same Type (for example 3-column Table, one populated and one not populated). I found it easier to manage. Up to you the produce different output types for the different alternatives (for example a table in case1 and null in case2).

    Hope it can help,

    Anthony

    • Marked as answer by Lz._ Tuesday, March 20, 2018 12:11 PM
    • Edited by anthony34 Tuesday, March 20, 2018 12:37 PM
    Tuesday, March 20, 2018 10:26 AM

All replies

  • Hi

    The UE in Powerquery is so easy to use that when we begin with it we may not see all the potential with M. It is the drawback: the UE provides some quick and dirty code that works very well, but it is hiding 90% of the power of PQ (like the macro recorder in Excel that allows 10% of what VBA can do).

    In your case, you need to structure your query and then you will easily get to the result you want:

    let
    
        #"Query in case 1" =
            let
                // Here is all your query in case the test is positive
                Source = "",
                // ...
                Output = "Result in Case 1"
            in Output,
    
        #"Query in case 2" =
                // Here is all your query in case the test is negative
            let
                Source = "",
                // ...
                Output = "Result in Case 2"
            in Output,
    
        #"Test" = 
                // Here is the test (based on your parameter)
            let
                Source = "",
                Output = true //or false
            in Output,        
    
        #"Result" =    if #"Test" 
                    then #"Query in case 1" 
                    else #"Query in case 2"
    
    
    in
    Result


    in your specific case, it would give something like the following, and of course you have to adapt according your needs and your inputs:

    let
    
        #"Query in case 1" =
            let
                // SOURCE & PARAMETER (value of named ranged "QryProduct")
                Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
                varProduct = Excel.CurrentWorkbook(){[Name="QryProduct"]}[Content]{0}[Column1],
    
                //(code removed)
    
                // Store Avg. Amount for PRODUCT = varProduct (AVERAGEIF) if at least 1 product matches, otherwise store "n/a"
                #"Filter on varProduct" = Table.SelectRows(#"Uppercased Product", each ([Product] = Text.Upper(varProduct))),
                AvgAmount_varProduct = List.Average(Table.Column(#"Filter on varProduct", "Amount")),
    
                // Output table w/desired measures
                #"Ouput" = #table(
                    type table[Measures = text, Value = any],
                    {
                        {"Unique Products", NbUniqProd},
                        {"Total Amount", TotalAmount}//,
                        // IF varProduct is null do not output next line
                        //{"Avg. Product " & varProduct, AvgAmount_varProduct}
                    }
                )
            in #"Output",
    
        #"Query in case 2" = 
                #table(
                    type table[Measures = text, Value = any],
                  {
                        //{"Unique Products", NbUniqProd},
                        //{"Total Amount", TotalAmount},
                        // IF varProduct is null do not output next line
                        {"Avg. Product " & varProduct, AvgAmount_varProduct}
                    }
    
     
        #"Test" = 
            // Here is the test (based on your parameter)
            let
                // Store Avg. Amount for PRODUCT = varProduct (AVERAGEIF) if at least 1 product matches, otherwise store "n/a"
                // ... the needed code here
                #"Filter on varProduct" = Table.SelectRows(#"Uppercased Product", each ([Product] = Text.Upper(varProduct))),
                #"TestAvgAmount_varProduct" = if Table.RowCount(#"Filter on varProduct") = 0 
                                       then true
                                       else false
    
            in TestAvgAmount_varProduct,
    
        #"Result" =    if #"Test" 
                    then #"Query in case 1" 
                    else #"Query in case 2"
    
    
    in
    Result


    Note1: the order of the "Main" queries is not relevant: you can put  the "Test" part at the top if you prefer.

    Note2: you can make as many alternatives as you may need, by using in the #"Result":
    if ... then ... else if ... then ... else if ... then ... else

    Note3: usually I prefer all the output alternatives to be under the same Type (for example 3-column Table, one populated and one not populated). I found it easier to manage. Up to you the produce different output types for the different alternatives (for example a table in case1 and null in case2).

    Hope it can help,

    Anthony

    • Marked as answer by Lz._ Tuesday, March 20, 2018 12:11 PM
    • Edited by anthony34 Tuesday, March 20, 2018 12:37 PM
    Tuesday, March 20, 2018 10:26 AM
  • @Anthony

    As you guessed I'm quite new to Power Query and very much appreciate your explainations that really helped to make it work as expected.

    Thanks also for providing a sample of how the code should be structured in such situation. With all of that I was able to do what I wanted (w/o duplicating the Source and some other calcs) in more than 15 mins :-)

    THANKS MUCH AGAIN!!!

    Tuesday, March 20, 2018 12:11 PM