Forum Discussion
Left Join Between dates
Hi
I've recently started dabling with powerbi. I have a scenario where I want to left join two tables with an aditional condition checking if the date values from table A is between a Start and End Date in Table B.
If the data is not present in Table B then Table A's rows still needs to be returned. In SQL this is a pretty straight forward query, but for the life of me i'm strugling to figure this out in Powerbi.
What would the syntax be in either DAX or M Query to achieve the same result set as posted below ?
Bellow I have listed the 2 tables , sample query written in SQL and the expected results. I have also attached the scripts to generate the sample set.
Thanks in advance 🙂
Table : ta
| Column | Data Type |
| RD | datetime |
| Schedule | int |
| Dataset | int |
Table : tb
| Column | Data Type |
| SD | datetime |
| ED | datetime |
| Dataset | int |
| RowsCopied | int |
SQL : Query :
select
a.RD as a_eventDate ,
a.Schedule as a_Schedule,
a.Dataset as a_Dataset,
b.SD as b_SD ,
b.ED as b_ED,
b.Dataset as b_Dataset,
b.RowsCopied as b_RowsCopied
from ta as a
left join tb as b on a.Dataset = b.Dataset
and a.RD >= b.SD and a.RD <= b.ED
SQL Results :
| a_eventDate | a_Schedule | a_Dataset | b_SD | b_ED | b_Dataset | b_RowsCopied |
| 2020-01-01 08:00:00 | 1 | 1 | 2020-01-01 07:45:00 | 2020-01-01 08:45:00 | 1 | 300 |
| 2020-01-01 08:00:00 | 1 | 2 | 2020-01-01 07:47:00 | 2020-01-01 08:25:00 | 2 | 100 |
| 2020-01-01 08:00:00 | 1 | 3 | NULL | NULL | NULL | NULL |
| 2020-01-01 08:00:00 | 1 | 4 | 2020-01-01 07:45:00 | 2020-01-01 08:35:00 | 4 | 10 |
| 2020-01-01 08:00:00 | 1 | 5 | NULL | NULL | NULL | NULL |
| 2020-01-01 14:00:00 | 2 | 6 | NULL | NULL | NULL | NULL |
| 2020-01-01 14:00:00 | 2 | 2 | 2020-01-01 14:00:00 | 2020-01-01 14:15:00 | 2 | 25 |
| 2020-01-02 08:00:00 | 1 | 1 | 2020-01-02 07:45:00 | 2020-01-02 08:45:00 | 1 | 30 |
| 2020-01-02 08:00:00 | 1 | 2 | 2020-01-02 07:45:00 | 2020-01-02 08:05:00 | 2 | 40 |
| 2020-01-02 08:00:00 | 1 | 3 | 2020-01-02 07:45:00 | 2020-01-02 08:15:00 | 3 | 20 |
| 2020-01-02 08:00:00 | 1 | 4 | NULL | NULL | NULL | NULL |
| 2020-01-02 08:00:00 | 1 | 5 | NULL | NULL | NULL | NULL |
| 2020-01-02 14:00:00 | 2 | 6 | 2020-01-02 14:00:00 | 2020-01-02 14:15:00 | 6 | 20 |
| 2020-01-02 14:00:00 | 2 | 2 | NULL | NULL | NULL | NULL |
Creating the sample data set scripts:
create table ta (RD datetime , Schedule int , Dataset int)
create table tb (SD datetime , ED datetime, Dataset int , RowsCopied int)
insert into ta values('2020-01-01 8:00' ,1,1 )
insert into ta values('2020-01-01 8:00' ,1,2 )
insert into ta values('2020-01-01 8:00' ,1,3 )
insert into ta values('2020-01-01 8:00' ,1,4 )
insert into ta values('2020-01-01 8:00' ,1,5 )
insert into ta values('2020-01-01 14:00' ,2 ,6 )
insert into ta values('2020-01-01 14:00' ,2 ,2 )
insert into ta values('2020-01-02 8:00' ,1,1 )
insert into ta values('2020-01-02 8:00' ,1,2 )
insert into ta values('2020-01-02 8:00' ,1,3 )
insert into ta values('2020-01-02 8:00' ,1,4 )
insert into ta values('2020-01-02 8:00' ,1,5 )
insert into ta values('2020-01-02 14:00' ,2,6 )
insert into ta values('2020-01-02 14:00' ,2,2 )
insert into tb values('2020-01-01 7:45','2020-01-01 8:45' ,1,300)
insert into tb values('2020-01-01 7:47','2020-01-01 8:25' ,2,100)
insert into tb values('2020-01-01 7:45','2020-01-01 8:35' ,4,10)
insert into tb values('2020-01-02 7:45','2020-01-02 8:45' ,1,30)
insert into tb values('2020-01-02 7:45','2020-01-02 8:05' ,2,40)
insert into tb values('2020-01-02 7:45','2020-01-02 8:15' ,3,20)
insert into tb values('2020-01-02 14:00','2020-01-02 14:15' ,6,20)
insert into tb values('2020-01-01 14:00','2020-01-01 14:15' ,2,25)
Please open "Transform data", then check my queries below:
in "tableb" merge queries with "tablea" in "right outer" way,
then add custom columns and filter rows in "tableb",
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc5LCsAgDATQqxTXgvm1Fq8i3v8aNVaRiODCRcLLODk7DBgICK6Y5HZ+zm+f62MAV7y10VpSS7rb2CWXdZZmLUVJ9dqbBY7g+sOPacmlTd8ThZEqR9oKsPKFzrZk2z4dlw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [b_SD = _t, b_ED = _t, b_Dataset = _t, b_RowsCopied = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"b_SD", type datetime}, {"b_ED", type datetime}, {"b_Dataset", Int64.Type}, {"b_RowsCopied", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"b_Dataset"}, Tablea, {"a_Dataset"}, "Tablea", JoinKind.RightOuter), #"Expanded Tablea" = Table.ExpandTableColumn(#"Merged Queries", "Tablea", {"a_eventDate", "a_Schedule", "a_Dataset"}, {"Tablea.a_eventDate", "Tablea.a_Schedule", "Tablea.a_Dataset"}), #"Added Custom" = Table.AddColumn(#"Expanded Tablea", "Custom", each if [b_Dataset]<>null and [Tablea.a_eventDate]>=[b_SD] and [Tablea.a_eventDate]<=[b_ED] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)) in #"Filtered Rows"create a new query1 which uses "tablea" data,
then in new query, merge query with "tableb" based on two columns "datatset" and "eventdate"
let Source = Tablea, #"Merged Queries" = Table.NestedJoin(Source, {"a_Dataset", "a_eventDate"}, Tableb, {"b_Dataset", "Tablea.a_eventDate"}, "Tableb", JoinKind.LeftOuter), #"Expanded Tableb" = Table.ExpandTableColumn(#"Merged Queries", "Tableb", {"b_SD", "b_ED", "b_Dataset", "b_RowsCopied"}, {"Tableb.b_SD", "Tableb.b_ED", "Tableb.b_Dataset", "Tableb.b_RowsCopied"}), #"Sorted Rows" = Table.Sort(#"Expanded Tableb",{{"a_eventDate", Order.Ascending}, {"a_Dataset", Order.Ascending}}) in #"Sorted Rows"Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Please open "Transform data", then check my queries below:
in "tableb" merge queries with "tablea" in "right outer" way,
then add custom columns and filter rows in "tableb",
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc5LCsAgDATQqxTXgvm1Fq8i3v8aNVaRiODCRcLLODk7DBgICK6Y5HZ+zm+f62MAV7y10VpSS7rb2CWXdZZmLUVJ9dqbBY7g+sOPacmlTd8ThZEqR9oKsPKFzrZk2z4dlw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [b_SD = _t, b_ED = _t, b_Dataset = _t, b_RowsCopied = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"b_SD", type datetime}, {"b_ED", type datetime}, {"b_Dataset", Int64.Type}, {"b_RowsCopied", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"b_Dataset"}, Tablea, {"a_Dataset"}, "Tablea", JoinKind.RightOuter), #"Expanded Tablea" = Table.ExpandTableColumn(#"Merged Queries", "Tablea", {"a_eventDate", "a_Schedule", "a_Dataset"}, {"Tablea.a_eventDate", "Tablea.a_Schedule", "Tablea.a_Dataset"}), #"Added Custom" = Table.AddColumn(#"Expanded Tablea", "Custom", each if [b_Dataset]<>null and [Tablea.a_eventDate]>=[b_SD] and [Tablea.a_eventDate]<=[b_ED] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)) in #"Filtered Rows"create a new query1 which uses "tablea" data,
then in new query, merge query with "tableb" based on two columns "datatset" and "eventdate"
let Source = Tablea, #"Merged Queries" = Table.NestedJoin(Source, {"a_Dataset", "a_eventDate"}, Tableb, {"b_Dataset", "Tablea.a_eventDate"}, "Tableb", JoinKind.LeftOuter), #"Expanded Tableb" = Table.ExpandTableColumn(#"Merged Queries", "Tableb", {"b_SD", "b_ED", "b_Dataset", "b_RowsCopied"}, {"Tableb.b_SD", "Tableb.b_ED", "Tableb.b_Dataset", "Tableb.b_RowsCopied"}), #"Sorted Rows" = Table.Sort(#"Expanded Tableb",{{"a_eventDate", Order.Ascending}, {"a_Dataset", Order.Ascending}}) in #"Sorted Rows"Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks ...
This seems to solve the question at hand.
It just blows my mind the round about way one needs to go to get a simple problem solved....
Thanks again !
Since I cannot read SQL out of the box, I'm not sure what you are doing exactly, but it sounds like you want an left-Anti-Join. Do a Merge between your tables and tell it to only return the rows in the first or second table, depending on which way you are going.
If that isn't what you need, provide sample data in a table format for both tables and the expected result using the "how to provide sample data" instructions below.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
