Forum Discussion
Power Query Editor - filter a query column based on another query column
I have the following situation:
One Date table with that includes information until the last date included in the SQL.
Google Analytics connection that includes Google Adwords Costs until the very moment the report is refreshed.
What I want to do: remove the days that are not covered in SQL (Date table) from the Google Adwords Costs.
I know that it can be done by using relationships, I know it can be done by using some merge transformations but due to the data model and other later merge operations I can not use that.
Is there any way to filter a column in a table in Power Query Editor based on a column in another table?
I attach a sample file here:
https://drive.google.com/file/d/13DSn7o8YSjLbaX4LVGL948qeWhEN0nHB/view?usp=sharing
This is the desired result in the sample file, eliminate every date after the MaxDate:
Thank you!
= let max =MaxDate[MaxDate]{0} in Table.SelectRows(#"Changed Type", each [Date] <= max)
2 Replies
= let max =MaxDate[MaxDate]{0} in Table.SelectRows(#"Changed Type", each [Date] <= max)Thank you CNENFRNL !
It worked.
Full syntax used:
let
max =MaxDate[MaxDate]{0},
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjBCQAgDAPAXfIWtLVWnEW6/xoiJPe8e5Hdug83NBiq/XBFMKZiMkKRjKVwRioOYys2qh4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= max)
in
#"Filtered Rows"