Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Armand_Theron
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
v-juanli-msft
Community Support
Community Support

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
v-juanli-msft
Community Support
Community Support

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.

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 !

edhans
Super User
Super User

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

Hi 

 

  Thanks for the reply however the solution from 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors