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.
dufoq3
Community Champion
2 years agoCould 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?
- smpa012 years ago
Community Champion
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
- dufoq32 years ago
Community Champion
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.
- smpa012 years ago
Community Champion
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"