Filter based on groups of rows with zero value

GreenBoy

GreenBoy

New member
Joined
Apr 13, 2015
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
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... :Cry:

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 :mad:)

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

Site IDActivityActivity CodePurchase Order Value
50999CONSTRUCTION - Passive14.24000.00
50999CONSTRUCTION - Active5.130000.00
50999CONSTRUCTION - Passive TopUp14.20.00
50999CONSTRUCTION - Active TopUp5.10.00
98765CONSTRUCTION - Passive14.28000.00
98765CONSTRUCTION - Active5.110100.00
98765CONSTRUCTION - Passive TopUp14.20.00
98765CONSTRUCTION - Active TopUp5.1350.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.
 
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"
 
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"
 
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
 
You must log in or register to reply here.
Back
Top