none
row generation based on filter criteria RRS feed

  • Question

  • Hi! This has had me stumped for a while now. I'm working in Power Query in Excel.

    I'm trying to align QA tests from one source with production quantities from another source. the production list has every pallet number made but the test list only lists what has been tested. So for tests with a 1 in 5 or 1 in 3 frequency, there will be a pallet number gap.

    This is an issue when 2 consecutive tests fail, I need the pallet numbers in between to match to the production list so their quantity can be counted. because each pallet has multiple different tests, I need to invoke a function that will create the missing pallet numbers for each failed test. 

    The function needs to filter the results by batch number then by test type, arrange them in sequence, for for fail/fail sequential pairs (fail/pass, pass/fail and pass/pass can be ignored) then generate the pallet numbers in between as new rows.

    I've got as far as filtering and arranging in sequence but its the fail/fail pairs that are stumping me as I get a column of pass or fail results and a column of pallet numbers. I've using a 0 index and a 1 index to try grab the next pallet in the sequence but I just can't get the code right to pull the pallet value into a column. I've got code that can generate the sequence but it needs the inputs to be in two columns on the same line.

    Any help appreciated! 



    • Edited by Nicholas Warner Monday, July 1, 2019 10:44 AM clarify power query in excel
    Monday, July 1, 2019 10:43 AM

Answers

  • So, you have such table:

    Test result Item sampling
    Pass BU038520
    Pass BU038525
    Pass BU038530
    Fail BU038535
    Fail BU038540
    Fail BU038545
    Pass BU038550

    Then you may use following code to get desired output:

    let
        Source = YourTable,
        i = Table.AddIndexColumn(Source, "i", 0, 1),
        j = Table.AddIndexColumn(i, "j", 1, 1),
        join = Table.NestedJoin(j,{"j"},j,{"i"},"k"),
        expand = Table.ExpandTableColumn(join, "k", {"Test result", "Item sampling"}, {"k.Test result", "k.Item sampling"}),
        add = Table.AddColumn(expand, "Fail to", each if List.MatchesAll({"Fail", [Test result]}, (x)=> x=[k.Test result]) then [k.Item sampling] else null),
        final = Table.SelectColumns(add,{"Test result", "Item sampling", "Fail to"})
    in
        final

    Tuesday, July 2, 2019 9:55 AM

All replies

  • Hi Nicholas,

    Could you provide data sample and desired output, please?

    Monday, July 1, 2019 12:13 PM
  • Hi Aleksei,

    This is what my function returns so far:

    Test result Item sampling
    Pass BU024824
    Pass BU024844
    Pass BU024864
    Fail BU024884
    Fail BU024905

    If test result is Fail and followed by Fail it needs to pull the Item sampling entry below into a new column. like so:

    Test result Item sampling Fail to 
    Pass BU024824  
    Pass BU024844  
    Pass BU024864  
    Fail BU024884 BU024905
    Fail BU024905  

    Here's another potential example:

    Test result Item sampling Fail to 
    Pass BU038520
    Pass BU038525
    Pass BU038530
    Fail BU038535 BU038540
    Fail BU038540 BU038545
    Fail BU038545
    Pass BU038550


    It needs to do this within a function that I can use in an invoke function column on the main data. this is my function so far which returns the first table. It has some if logic so it doesn't run on lines it doesn't need to, passed tests and comp tests which already have full pallet ranges generated.

    (BatchNumber,TestID,TestPassFail,IsCompPallet) =>
    let
        //THEN Code Block
        // returns list of pallets
        Source = #"Combined Tests",
        #"Filtered Rows" = Table.SelectRows(Source, each ([Batch number] = BatchNumber) and ([Test] = TestID)),
        #"Filtered Columns" = Table.SelectColumns(#"Filtered Rows", {"Test result","Item sampling"}, MissingField.UseNull),
    #"Sort" = Table.Sort(#"Filtered Columns",{{"Item sampling", Order.Ascending}}),
        //ELSE Code Block
       #"null" = null,
    
    result =
    if TestPassFail = "Fail" and IsCompPallet = "False"
    then #"Filtered Columns"
    else #"null"
    in
        #"result"

    Hope that makes sense!


    • Edited by Nicholas Warner Monday, July 1, 2019 9:34 PM changed white text to black
    Monday, July 1, 2019 9:24 PM
  • So, you have such table:

    Test result Item sampling
    Pass BU038520
    Pass BU038525
    Pass BU038530
    Fail BU038535
    Fail BU038540
    Fail BU038545
    Pass BU038550

    Then you may use following code to get desired output:

    let
        Source = YourTable,
        i = Table.AddIndexColumn(Source, "i", 0, 1),
        j = Table.AddIndexColumn(i, "j", 1, 1),
        join = Table.NestedJoin(j,{"j"},j,{"i"},"k"),
        expand = Table.ExpandTableColumn(join, "k", {"Test result", "Item sampling"}, {"k.Test result", "k.Item sampling"}),
        add = Table.AddColumn(expand, "Fail to", each if List.MatchesAll({"Fail", [Test result]}, (x)=> x=[k.Test result]) then [k.Item sampling] else null),
        final = Table.SelectColumns(add,{"Test result", "Item sampling", "Fail to"})
    in
        final

    Tuesday, July 2, 2019 9:55 AM
  • Thank you! That step has been driving me crazy.

    I saw in one of your answers on another post a link to an article on environment and thought that was probably where I was going wrong too. I'm going to have to study that a bit more because a lot of my work is subsets of subsets. 

    Wednesday, July 3, 2019 5:15 AM