cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

 

ColumnData Type
RDdatetime
Scheduleint
Datasetint

 

Table : tb

 

ColumnData Type
SDdatetime
EDdatetime
Datasetint
RowsCopiedint

 

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_eventDatea_Schedulea_Datasetb_SDb_EDb_Datasetb_RowsCopied
2020-01-01 08:00:00112020-01-01 07:45:002020-01-01 08:45:001300
2020-01-01 08:00:00122020-01-01 07:47:002020-01-01 08:25:002100
2020-01-01 08:00:0013NULLNULLNULLNULL
2020-01-01 08:00:00142020-01-01 07:45:002020-01-01 08:35:00410
2020-01-01 08:00:0015NULLNULLNULLNULL
2020-01-01 14:00:0026NULLNULLNULLNULL
2020-01-01 14:00:00222020-01-01 14:00:002020-01-01 14:15:00225
2020-01-02 08:00:00112020-01-02 07:45:002020-01-02 08:45:00130
2020-01-02 08:00:00122020-01-02 07:45:002020-01-02 08:05:00240
2020-01-02 08:00:00132020-01-02 07:45:002020-01-02 08:15:00320
2020-01-02 08:00:0014NULLNULLNULLNULL
2020-01-02 08:00:0015NULLNULLNULLNULL
2020-01-02 14:00:00262020-01-02 14:00:002020-01-02 14:15:00620
2020-01-02 14:00:0022NULLNULLNULLNULL

 

 

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)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Left Join Between dates

Hi @Armand_Theron 

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",

Capture5.JPG

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"

Capture4.JPG

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User VI
Super User VI

Re: Left Join Between dates

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.

 

2020-04-26 09_40_49-.png

 

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Community Support
Community Support

Re: Left Join Between dates

Hi @Armand_Theron 

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",

Capture5.JPG

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"

Capture4.JPG

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.

View solution in original post

Highlighted
Frequent Visitor

Re: Left Join Between dates

Hi 

 

  Thanks for the reply however the solution from 

Highlighted
Frequent Visitor

Re: Left Join Between dates

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 !

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors