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
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
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 |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |