Table Merge Query by DATE (SQL syntax)

[mgaliyev]

Hi there,

I am hopeing someone knowledgable can help me do a SQL statement for the Table Merge join.

2 Tables that I want linked by:

- Vehicle Number

- Dates (where date of one table (TripDetail[TripSectionStartTime] is >= date of another table ServiceData[DepOriginActual])

I would like it done as a LEFT OUTTER JOIN where all records in TripDetail table will have some from ServiceData table.

My starting point:

let
    Source = Table.NestedJoin(#"Trip Detail",{"TripSectionStartTime", "VehicleNumber"},
        #"Service Data",{"Dep Origin Actual", "Value"},"NewColumn",JoinKind.LeftOuter)     
in
    Source

Thanks guys. I am stuck and google search didnt yield much help.

[GilbertQ]

Hi @mgaliyev,

I would suggest that if your data is stored in SQL Server, then to do the query in SQL Server. This will ensure that the data loads quickly into your Power BI Model, as well as being most efficient.

The rough TSQL would be

Select * 

from TripDetail as TD

left join ServiceData as SD

on TD.VehicleNumber = SD.VehicleNumber

and TD.Date = SD.Date

[mgaliyev]

I am just prototyping here, so the final solution may move into SQL Server.

I am now investigating the following as means of gettign the job done, but again its not cooperating.

TableMerge = SELECTCOLUMNS (
    FILTER (crossjoin('SectionsPrototype', 'ServicePrototype'), SectionsPrototype[Vehicle] = 'ServicePrototype'[Vehicle] && SectionsPrototype[Start] >= 'ServicePrototype'[Start] && SectionsPrototype[End]<= 'ServicePrototype'[End]), "ServiceNo", 'ServicePrototype'[Service No])

Example of the data:

Services

Service No	Start	End	Vehicle
1	1/02/2017	3/02/2017	100
2	10/02/2017	12/02/2017	201
1	1/02/2017	3/02/2017	101
2	10/02/2017	12/02/2017	200

Sections

Vehicle	Segment	Start	End
100	A-B	1/02/2017	3/02/2017
101	A-B	1/02/2017	3/02/2017
100	C-D	1/02/2017	3/02/2017
101	C-D	1/02/2017	3/02/2017
100	E-F	1/02/2017	3/02/2017
101	E-F	1/02/2017	3/02/2017
201	M-O	10/02/2017	12/02/2017
200	M-O	10/02/2017	12/02/2017
201	P-Q	10/02/2017	12/02/2017
200	P-Q	10/02/2017	12/02/2017

[Phil_Seamark]

There is a Left Join function in DAX so give this a whirl.  I added some vehicles to the Service table and it behaved as I would expect in TSQL

TableMerge = SELECTCOLUMNS(
                        FILTER(
                             NATURALLEFTOUTERJOIN('ServicePrototype','SectionsPrototype'),
                            'ServicePrototype'[Start] >='ServicePrototype'[Start]
                            && 'SectionsPrototype'[Vehicle] = 'SectionsPrototype'[Vehicle]
                            && 'SectionsPrototype'[End] <= 'SectionsPrototype'[End]
                            ),
                         
                         "Service No",'ServicePrototype'[Service No] ,
                         "Start",'ServicePrototype'[Start],
                         "End",'ServicePrototype'[End],
                         "Vehicle",'ServicePrototype'[Vehicle],
                         "Segment" , 'SectionsPrototype'[Segment]
                         )

[mgaliyev]

Thanks Phil.

I got it this far.

The only remaining problem is the values in the Calculated Table dont show Sections that couldnt be matched to a Service. I was hopeing that NATURALLEFTOUTERJOIN will have this effect.

So in scenario below the crosed out Section should show up as blank as it doesnt have a valid Service.

Result now: the Section (crossed out) doesnt show in the Calculated Table. Any ways to change that so my table shows all Sections irrespective of whether it has a match or not?

Sections

Vehicle	Segment	Start	End
100	A-B	2/02/2017	2/02/2017
101	A-B	4/02/2017	5/02/2017
100	C-D	2/02/2017	2/02/2017
101	C-D	2/02/2017	2/02/2017
100	E-F	2/02/2017	2/02/2017
101	E-F	2/02/2017	2/02/2017
201	M-O	11/02/2017	11/02/2017
200	M-O	11/02/2017	11/02/2017
201	P-Q	11/02/2017	11/02/2017
200	P-Q	11/02/2017	11/02/2017
301	Y-Z	1/03/2017	1/03/2017
301	X-AA	2/03/2017	2/03/2017

Service

Service No	Start	End	Vehicle
1	1/02/2017	3/02/2017	100
2	10/02/2017	12/02/2017	201
1	1/02/2017	3/02/2017	101
2	10/02/2017	12/02/2017	200
3	1/03/2017	5/03/2017	301

[v-huizhn-msft · Accepted solution]

Hi @mgaliyev,

I try to reproduce your scenario, I name your sample tables as 'SectionsPrototype1' and 'ServicePrototype1'.

Create a table using the following formula.

Test2-TableMerge = SELECTCOLUMNS (
    CROSSJOIN('SectionsPrototype1', 'ServicePrototype1'),  
                         "Section-Vehicle", SectionsPrototype1[Vehicle],
                         "Section-Start",'SectionsPrototype1'[Start],
                         "Section-End",'SectionsPrototype1'[End],                      
                         "Section-Segment" , 'SectionsPrototype1'[Segment],
                         "Service-Start",'ServicePrototype1'[Start1],"Servie-End",'ServicePrototype1'[End1],"Service-no",ServicePrototype1[Service No],"Service-vehicle",ServicePrototype1[Vehicle1])

Create a calculated column to identify a match using the formula.

Match = IF(AND('Test2-TableMerge'[Service-Start]>='Test2-TableMerge'[Section-Start],'Test2-TableMerge'[Servie-End]<='Test2-TableMerge'[Section-End]),'Test2-TableMerge'[Service-no])

Finally, create a new table to display the result.

Expected result = SELECTCOLUMNS(FILTER('Test2-TableMerge','Test2-TableMerge'[Section-Vehicle]='Test2-TableMerge'[Service-vehicle]),"Vehicle",'Test2-TableMerge'[Section-Vehicle],"Segment",'Test2-TableMerge'[Section-Segment],"Start",'Test2-TableMerge'[Section-Start],"End",'Test2-TableMerge'[Section-End],"Match",'Test2-TableMerge'[Match])

If you have other issues, please let me know.

Best Regards,
Angelia

[apatil]

Hi @v-huizhn-msft

Thank you for your solution, I am trying to follow your steps for similar case but while identifing the match I am getting duplicate values in my calculated column. can you please sugget where I am doing wrong?

My PeriodDetails table contains the period start and end date information.

I want to join this table with AssignmentDetails Table where ApproximateEndDate is between period StartDate and EndDate.

First, I joined two table as per your post - 

Test2-TableMerge = SELECTCOLUMNS (
    CROSSJOIN('report AssignmentDetails', 'report PeriodDetails'),"AssignmentId", 'report AssignmentDetails'[AssignmentId],  
              "DealId",'report AssignmentDetails'[DealId],"ApproximateEndDate",'report AssignmentDetails'[ApproximateEndDate], "EmployeeId",'report AssignmentDetails'[EmployeeId], "Period" , 'report PeriodDetails'[Period], "PeriodYear",'report PeriodDetails'[Year],"Quarter",'report PeriodDetails'[Quarter], 
              "StartDate",'report PeriodDetails'[StartDate], "EndDate",'report PeriodDetails'[EndDate])                       		 

Then created a calculated cloumn  

Match = IF(AND('Test2-TableMerge'[ApproximateEndDate]>='Test2-TableMerge'[StartDate],'Test2-TableMerge'[ApproximateEndDate]<='Test2-TableMerge'[EndDate]),'Test2-TableMerge'[Period])

And displayed result in new table 

Expected result = SELECTCOLUMNS(FILTER('Test2-TableMerge','Test2-TableMerge'[AssignmentId]='Test2-TableMerge'[AssignmentId]),"AssignmentId",'Test2-TableMerge'[AssignmentId],"DealId",
'Test2-TableMerge'[DealId],"ApproximateEndDate",'Test2-TableMerge'[ApproximateEndDate],"EmployeeId",'Test2-TableMerge'[EmployeeId],
"Period",'Test2-TableMerge'[Period],"Year",'Test2-TableMerge'[PeriodYear],"Quarter",'Test2-TableMerge'[Quarter],"StartDate",'Test2-TableMerge'[StartDate],"EndDate",'Test2-TableMerge'[EndDate],
"Match",'Test2-TableMerge'[Match])

Table rows are repeating if i add match column in my table