Results 1 to 4 of 4

Thread: Filter based on groups of rows with zero value

  1. #1
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    24
    Articles
    0
    Excel Version
    Office 365

    Question Filter based on groups of rows with zero value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, sorry if this is an easy one but my company has a new Finance Director, and its been a looooooong week, and my brain has already decided to leave for the weekend...

    So here is the situation

    I have a report where i summarise the lines of expenditure on construction works. These are covered under Purchase orders (PO) and each PO has a Passive and an Active line for the original PO value and a Passive and Active line for a 'Top-Up' (a project cost adjustment at final account - can be negative if were unlucky )

    The below is a pseudo dump to show the data setup in very cut down form.

    Site ID Activity Activity Code Purchase Order Value
    50999 CONSTRUCTION - Passive 14.2 4000.00
    50999 CONSTRUCTION - Active 5.1 30000.00
    50999 CONSTRUCTION - Passive TopUp 14.2 0.00
    50999 CONSTRUCTION - Active TopUp 5.1 0.00
    98765 CONSTRUCTION - Passive 14.2 8000.00
    98765 CONSTRUCTION - Active 5.1 10100.00
    98765 CONSTRUCTION - Passive TopUp 14.2 0.00
    98765 CONSTRUCTION - Active TopUp 5.1 350.00


    - My new FD does not like all the Zeros in the Order Value column and has asked me to remove them - but only where both the Passive and Active are Zero - therefore in the example above i need to filter out the Top-Up rows for site ID 50999 but NOT for site 98765

    I am sure this shouldn't be massively difficult to do (famous last words?), but given that my brain has left for the day this seems like Rocket Surgery to me.

    Any help, guidance, or pointers gratefully received.
    Have a great weekend.

    David
    PS - I'm using Excel 2016 - so new techniques if any in Office 365 wont work here.

  2. #2
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    I think steps are (a) Filter for TopUp (b) Group Purchase Order Value by SiteID (c) merge that back to original data table (d) filter out any row containing TopUp with 0 in Purchase Order Value with merged total =0

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Activity], "TopUp")),
        Grp = Table.Group(#"Filtered Rows" , {"Site ID"}, {{"POV_Sum", each List.Sum([Purchase Order Value]), type number}}),
        #"Merged Queries" = Table.NestedJoin(Source,{"Site ID"},Grp,{"Site ID"},"Grp",JoinKind.LeftOuter),
        #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Grp", {"POV_Sum"}, {"POV_Sum"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if Text.Contains([Activity],"TopUp") and [Purchase Order Value]=0 and [POV_Sum]=0 then "remove" else null),
        #"Filtered Rows2" = Table.SelectRows(#"Added Custom", each ([Custom] = null)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"POV_Sum", "Custom"})
    in
        #"Removed Columns"

  3. #3
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Slightly shorter version if you'd rather use a custom function to replace the whole group-merge-expand part
    Code:
    let
       Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
       #"Added Custom1" = Table.AddColumn(Source, "Total Amount",(i) => List.Sum(Table.SelectRows(Source, each ([Site ID] = i[Site ID] and Text.Contains([Activity],"TopUp")))[Purchase Order Value]), type number),
       #"Added Custom2" = Table.AddColumn(#"Added Custom1" , "Custom", each if Text.Contains([Activity],"TopUp") and [Purchase Order Value]=0 and [Total Amount]=0 then "remove" else null),
       #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom] = null)),
       #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Total Amount", "Custom"})
    in  #"Removed Columns"

  4. #4
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    24
    Articles
    0
    Excel Version
    Office 365
    Hi Horseyride - these are great.

    I do apologies that i haven't responded before, but work... you know.. Anyway i am a poor excuse for a human being and i humbly beg your forgiveness.

    Thanks again kind sir.
    GreenBoy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •