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
StephenF
Responsive Resident
Responsive Resident

Joining 2 tables with BETWEEN and another value

I wish to join 2 Tables based on slowly changing dimension data.

 

So Table A:

 

Dates are in numeric YYYYMMDD Format

 

CLNTID   ORIG DATE

223       20190101

456    20190101

2134    20190201

 

Table B:

 

CLNTID   Start Date Expire Date CLNT Name

223       20190101   20190201   Pepsi

223       20190202   20191231   Old Pepsi

456    20190101   20191231  7up

2134    20190101   20191231  Coca Cola

 

So basically Left outerjoin A to be on a.ClntiD=b.clntid and where a.origdate = between b.[start date] and b.[expire date].

 

What does the code look like here?

 

I am stuck here(col names are slightly different in reality):

 

= Table.NestedJoin(#"Expanded camp", {"clnt_code", "origination_date"}, clnm, {"clnt_code", "clnm_stt_date"}, "clnm", JoinKind.LeftOuter)

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @StephenF ,

 

M code for your reference.

let
    Source = Table.NestedJoin(#"Table A", {"CLNTID1"}, #"Table B", {"CLNTID"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(Source, "Table B", {"Start Date", "Expire Date", "CLNT Name"}, {"Table B.Start Date", "Table B.Expire Date", "Table B.CLNT Name"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table B", "Custom", each if [ORIG DATE]>=[Table B.Start Date] and [ORIG DATE]<=[Table B.Expire Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @StephenF ,

 

M code for your reference.

let
    Source = Table.NestedJoin(#"Table A", {"CLNTID1"}, #"Table B", {"CLNTID"}, "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(Source, "Table B", {"Start Date", "Expire Date", "CLNT Name"}, {"Table B.Start Date", "Table B.Expire Date", "Table B.CLNT Name"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table B", "Custom", each if [ORIG DATE]>=[Table B.Start Date] and [ORIG DATE]<=[Table B.Expire Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi,

 

How to achieve this with SSAS. Is it possible to join a de facto table with a calendar date using 2 dates from my de facto table with between?

 

Thanks You.

 

Best Regards,

Amine 

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.