Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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"

     

AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

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.