Passing on a dynamic filter table to a join

[smpa01]

@AlexisOlson @ImkeF @CNENFRNL 

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.

[smpa01]

@dufoq3  this works and giving me what I need. I would still keep it open just to see if dynamic filter can be passed on to join

[dufoq3]

OK 😉 Let me know if I can help you with something else. BTW. Which one of the codes fit your needs?

[dufoq3]

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?

[smpa01]

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"

[dufoq3]

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)

[smpa01]

Good stuff !!! thanks

[dufoq3]

You're welcome.

[smpa01]

@dufoq3 the "fed" filter makes this avaialbale to the join, but on top of this, I need to filter this table where [yr]=[Year] coming from the row context of the left table

[dufoq3 · Accepted solution]

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.