Forum Discussion
Filter One Table based on Another Table in Power Query only
Hello Everyone,
Sales Table:
| Year | Category | Sales | profit |
| 2018 | A | 500 | 50 |
| 2018 | B | 200 | 20 |
| 2018 | C | 120 | 12 |
| 2018 | A | 400 | 40 |
| 2018 | B | 300 | 30 |
| 2019 | C | 150 | 15 |
| 2019 | A | 520 | 52 |
| 2019 | B | 220 | 22 |
| 2019 | C | 130 | 13 |
Profit Filter Table :
| Year | Profit_Filter |
| 2018 | 30 |
| 2019 | 15 |
I want to filter my "Sales table: based on "Profit filter table".
Filter condition :
Remove rows from "Sales table" where profit is below of "Profit_filter" column value.
Outout :
| Year | Category | Sales | profit |
| 2018 | A | 500 | 50 |
| 2018 | A | 400 | 40 |
| 2018 | B | 300 | 30 |
| 2019 | C | 150 | 15 |
| 2019 | A | 520 | 52 |
| 2019 | B | 220 | 22 |
I need to apply this logic in Power Query only. I do not want to merge two tables.
Thank You
Vipin Bharti
Hi Vipin,
The requirement to not merge seems arbitrary. Merge is one of the faster methods for comparing data in PQ. Also, the merge can be done and still be made to appear that no merge took place. The sample below demonstrates this ability and can be used should you relax the merge requirement.
Regards,
Mike
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc4xCsAwCIXhuzhnMM8I7dj2GCH3v0bsgwZLBh0+5MfeBVoPKXLFuCq3jLL8jgEdP39iKpQ7+9tpvG9bx+i2/Pw6zo5n5z/sO7LzHzqwdYwdkzEm", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t, Sales = _t, profit = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Category", type text}, {"Sales", Int64.Type}, {"profit", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Year"}, ProfitFilter, {"Year"}, "ProfitFilter", JoinKind.Inner), #"Filtered Rows" = Table.SelectRows(#"Merged Queries", each ([profit] <= [ProfitFilter][Profit_Filter]{0}?)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ProfitFilter"}) in #"Removed Columns"
2 Replies
Hi Vipin,
The requirement to not merge seems arbitrary. Merge is one of the faster methods for comparing data in PQ. Also, the merge can be done and still be made to appear that no merge took place. The sample below demonstrates this ability and can be used should you relax the merge requirement.
Regards,
Mike
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc4xCsAwCIXhuzhnMM8I7dj2GCH3v0bsgwZLBh0+5MfeBVoPKXLFuCq3jLL8jgEdP39iKpQ7+9tpvG9bx+i2/Pw6zo5n5z/sO7LzHzqwdYwdkzEm", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t, Sales = _t, profit = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Category", type text}, {"Sales", Int64.Type}, {"profit", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Year"}, ProfitFilter, {"Year"}, "ProfitFilter", JoinKind.Inner), #"Filtered Rows" = Table.SelectRows(#"Merged Queries", each ([profit] <= [ProfitFilter][Profit_Filter]{0}?)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ProfitFilter"}) in #"Removed Columns"Hi Anonymous , as Mike mentioned Merge is one of the preffered option. To get the result without merge, try below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc4xCsAwCIXhuzhnMM8I7dj2GCH3v0bsgwZLBh0+5MfeBVoPKXLFuCq3jLL8jgEdP39iKpQ7+9tpvG9bx+i2/Pw6zo5n5z/sO7LzHzqwdYwdkzEm", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t, Sales = _t, profit = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Category", type text}, {"Sales", Int64.Type}, {"profit", Int64.Type}}), Custom1 = Table.AddColumn(#"Changed Type","Test",each List.IsEmpty(Table.SelectRows(ProfitFilter,(a) => a[Year] = [Year] and [profit] >= a[Profit_Filter])[Profit_Filter])), #"Filtered Rows" = Table.SelectRows(Custom1, each ([Test] = false)) in #"Filtered Rows"Thanks
Ankit Jain
Do Kudo the response if it seems good and helpful.
