Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
@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
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?
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"
Good stuff !!! thanks
@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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |