Joining 2 tables with BETWEEN and another value

[StephenF]

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)

[v-frfei-msft · Accepted solution]

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"

[Anonymous]

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