Forum Discussion
Filter function in Power Query Editor
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,
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/
2 Replies
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/Hi Tom.
It worked. Thank you very much for your help 🙂.
