Filter One Table based on Another Table in Power Query only

[Anonymous]

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

[AnkitBI]

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.

[Anonymous · Accepted solution]

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"