Dynamic Filter

[Anonymous]

Hi Folks,

I'm trying to filter every 3 last months > 0.

e.g.: 

The last three months to "DEF" was 0. I need to remove it from my table.

Any ideias?

Ty guys!!

[edhans · Accepted solution]

Try this code @Anonymous 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc49DoAgDIbhu3QmKVAjOKr4dwbC/a8hoUvN5/INz/C2tVJiCRx9DORo3fa+npqrlMHj8IXFf12G2045zt+OdduxbjvX/fTN0FFP0FHXzgx/TuB6F1073dsL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Prev3Mo", each Date.IsInPreviousNMonths([Date],3)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Attribute", "Prev3Mo"}, {{"Total", each List.Sum([Value]), type nullable number}, {"All Rows", each _, type table [Date=nullable date, Attribute=nullable text, Value=nullable number, Prev3Mo=logical]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Total] <> 0)),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Filtered Rows", "All Rows", {"Date", "Value"}, {"Date", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All Rows",{"Total", "Prev3Mo"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Attribute", "Value"})
in
    #"Reordered Columns"

It turns this:

into this

What I did is this:

1. Included 4 months of data.
2. Added a function that evaluated the date and returns TRUE if it is in the last 3 months. It is Date.IsInPreviousNMonths.
3. I grouped by the Prev3Mo and Attribute column, and summed the value, and added an All Rows aggregation.
4. 
5. Then I filtered out the Total where it was zero. Here you can optionally also discard all of the false values for the Prev3Mo if you won't want them, or keep them. I kept them here, so this code will not destroy old data unless you filter them out.
6. Then I expanded the All Rows column repopulating the table with the Date and Value only. Attribute was already there.
7. Then I removed the extra columns.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[KNP]

Should be simple enough.

Got any sample data (not a screenshot)?

And perhaps your current M code from your queries?