Forum Discussion
Passing on a dynamic filter table to a join
How can I pass on a dynamic filter tbl to a join.
This is currently I am doing
let
src=#table({"yr", "category","data"}, {{2023, "fed", #table({"yr", "month","val"}, {{2023, 1,100}, {2023,1,200}}) }, {2023, "prov",#table({"yr", "month","val"}, {{2023, 1,300}, {2023, 1,400}}) }})
& #table({"yr", "category","data"}, {{2024, "fed", #table({"yr", "month","val"}, {{2024, 1,500}, {2024, 1,600}}) }, {2024, "prov",#table({"yr", "month","val"}, {{2024, 1,700}, {2024, 1,800}}) }}),
target = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTJUitUBc0wgnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [yr = _t, mo = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"yr", Int64.Type}, {"mo", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"yr", "Year"}})
in
#"Renamed Columns",
#"Merged Queries" = Table.NestedJoin(target, {"Year", "mo"}, Table.SelectRows(src, each([category]="fed")){0}[data], {"yr", "month"}, "target", JoinKind.LeftOuter)
in
#"Merged Queries"
But what I really want is to add an additional filter which is the [year] that is avaialble from the current row context of the left table to be passed on to filter outer src table (right table). The following does not work
//does not work
Table.NestedJoin(target, {"Year", "mo"}, Table.SelectRows(src, each([category]="fed" and [yr]=[Year])){0}[data], {"yr", "month"}, "target", JoinKind.LeftOuter)
//does not work
Table.NestedJoin(target, {"Year", "mo"}, Table.SelectRows(src, each([category]="fed" and [yr]=Table.SelectRows(target,(_)=>_[year]) )){0}[data], {"yr", "month"}, "target", JoinKind.LeftOuter)
Thank you in advance.
What about this?
= Table.NestedJoin(target, {"Year", "mo"}, Table.Combine(src[data]), {"yr", "month"}, "target", JoinKind.LeftOuter)or this:
= Table.NestedJoin(target, {"Year", "mo"}, Table.Combine(Table.SelectRows(src, each [category]="fed")[data]), {"yr", "month"}, "target", JoinKind.LeftOuter)But maybe I still don't understand what do you exactly need. Sorry for that.
9 Replies
Could you be more specific? You are passing Year from left table row context, but you added also category fed, which you are missing in left table. Could you tell me what should be expected output of this join please?
What about this?
= Table.NestedJoin(target, {"Year", "mo"}, Table.Combine(src[data]), {"yr", "month"}, "target", JoinKind.LeftOuter)or this:
= Table.NestedJoin(target, {"Year", "mo"}, Table.Combine(Table.SelectRows(src, each [category]="fed")[data]), {"yr", "month"}, "target", JoinKind.LeftOuter)But maybe I still don't understand what do you exactly need. Sorry for that.
Could you tell me what should be expected output of this join please? #2
let src=#table({"yr", "category","data"}, {{2023, "fed", #table({"yr", "month","val"}, {{2023, 1,100}, {2023,1,200}}) }, {2023, "prov",#table({"yr", "month","val"}, {{2023, 1,300}, {2023, 1,400}}) }}) & #table({"yr", "category","data"}, {{2024, "fed", #table({"yr", "month","val"}, {{2024, 1,500}, {2024, 1,600}}) }, {2024, "prov",#table({"yr", "month","val"}, {{2024, 1,700}, {2024, 1,800}}) }}), Custom1 = Table.SelectRows(src, each([category]="fed")), target = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTJUitUBc0wgnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [yr = _t, mo = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"yr", Int64.Type}, {"mo", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"yr", "Year"}}) in #"Renamed Columns", #"Merged Queries" = Table.NestedJoin(target, {"Year", "mo"}, Table.Combine(Table.SelectRows(src, each([category]="fed"))[data]), {"yr", "month"}, "target", JoinKind.LeftOuter) in #"Merged Queries"Same join but filtered src table to "fed" (It was in your example so I provided the same, but not only for the first row)
OK 😉 Let me know if I can help you with something else. BTW. Which one of the codes fit your needs?
