Forum Discussion
smpa01
Community Champion
Community ChampionPassing on a dynamic filter table to a join
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", #tab...
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.
dufoq3Community Champion
Community ChampionCould 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?
smpa01Community Champion
Community ChampionCould 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"