Filter function in Power Query Editor

[Diego11]

Hi everyone,

 I'm trying to filter a table in Power Query Editor by IDs that are in "Stage 3" but also show me their values from their previous stages. 

E.g.

Initial table 

ID	Stage	Score
001	1	50%
001	2	30%
001	3	70%
002	1	15%
002	2	27%
003	1	10%
003	2	66%
003	3	30%
004	1	10%
005	1	26%
005	2	37%

Filtered table

ID	Stage	Score
001	1	50%
001	2	30%
001	3	70%
003	1	10%
003	2	66%
003	3	30%

I've tried but I only get values in Stage 3. Does anyone know how I can do it?

Thank you so much in advance,

[tackytechtom · Accepted solution]

Hi @Diego11 ,

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDQAgCATAXUjsLBAEhzHuv4ZixGAsvrnweXoHxAIZLIIJRnahFX6EV9oVOq0iUXaaC/sNRrEb1Sj8bNWvJUdIo+wPbWtM", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Stage " = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Stage ", Int64.Type}, {"Score", Percentage.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Stage "] = 3)),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Filtered Rows", {"ID"}, "Filtered Rows", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Filtered Rows"})
in
    #"Removed Columns"

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Diego11]

Hi Tom.

 It worked. Thank you very much for your help 🙂.