Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
smpa01
Super User
Super User

Passing 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", #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.

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

9 REPLIES 9
smpa01
Super User
Super User

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3
Super User
Super User

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?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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)


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Good stuff !!! thanks

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

You're welcome.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@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

smpa01_0-1711556346870.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors