Forum Discussion
smpa01
Community Champion
2 years agoPassing 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...
- 2 years ago
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.
smpa01
Community Champion
2 years agoCould 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
Community Champion
2 years agoSame join but filtered src table to "fed" (It was in your example so I provided the same, but not only for the first row)