Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.