Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to 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"
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"
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
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |