Forum Discussion

Zimanko's avatar
Zimanko
New Member
2 years ago

Filtering groups based on two columns with a twist

Hello everyone!

I need to filter the following table:


If in_qty_oh > 0 and ord_pol = "LFL" but if the conditions are met I need to show all rows from the In_part group.
So the results should look like this:

Need Help

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Explanation in code comments:

    We group by part, then test each grouped table.

     

    let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"ln_part", type text}, {"in_site", Int64.Type}, {"in_qty_oh", Int64.Type}, {"ord_pol", type text}}),

//Group by Part with no aggregation
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ln_part"}, {
        {"all", each _, 
        type table [ln_part=nullable text, in_site=nullable number, in_qty_oh=nullable number, ord_pol=nullable text]}}),

//Check if filtered, grouped table has any row that meets criteria
//   Return true/false for filtering
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", 
        each Table.RowCount(Table.SelectRows([all], (t)=>
                t[in_qty_oh]>0 and t[ord_pol]="LFL")) > 0, type logical),

//Remove the grouped rows where the filter of the grouped did not return any rows
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),

//remove filter column and re-expand the "groups"
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", {"ln_part", "in_site", "in_qty_oh", "ord_pol"}, {"ln_part.1", "in_site", "in_qty_oh", "ord_pol"})
in
    #"Expanded all"