Remove rows for last 3 weeks - power query

[Anonymous]

I have a query with several columns and want to filter it dynamicly removing last 3  "Week id" from a column.

Product Week id

X               1

Y               1

Z               2

X               4

Z               4

Z               5

Y               7

The output should look like this:

Product Week id

X               1

Y               1

Z               2

X               4

Z               4

Weeks, 7,6,5 was removed. Although there was no data for week id = 6 it should be counted.

I would have this in PQ.

Thanks in advance

[Stachu · Accepted solution]

try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTJUitWJVoqEs6KALCMwCyRrAhdDsEzhOsyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Tab = _t, WeekId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tab", type text}, {"WeekId", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [WeekId] < List.Max(#"Changed Type"[WeekId])-2)
in
    #"Filtered Rows"

for your application you should use the last line and replace the blue parts with names specific to your setup

[Anonymous]

Thanks for clear answer. Works Great!