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

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

Reply
mgaliyev
Frequent Visitor

Table Merge Query by DATE (SQL syntax)

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.

1 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])


1.PNG

If you have other issues, please let me know.

Best Regards,
Angelia

View solution in original post

6 REPLIES 6
GilbertQ
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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 NoStartEndVehicle
11/02/20173/02/2017100
210/02/201712/02/2017201
11/02/20173/02/2017101
210/02/201712/02/2017200

 

Sections

VehicleSegmentStartEnd
100A-B1/02/20173/02/2017
101A-B1/02/20173/02/2017
100C-D1/02/20173/02/2017
101C-D1/02/20173/02/2017
100E-F1/02/20173/02/2017
101E-F1/02/20173/02/2017
201M-O10/02/201712/02/2017
200M-O10/02/201712/02/2017
201P-Q10/02/201712/02/2017
200P-Q10/02/201712/02/2017

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]
                         )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

VehicleSegmentStartEnd
100A-B2/02/20172/02/2017
101A-B4/02/20175/02/2017
100C-D2/02/20172/02/2017
101C-D2/02/20172/02/2017
100E-F2/02/20172/02/2017
101E-F2/02/20172/02/2017
201M-O11/02/201711/02/2017
200M-O11/02/201711/02/2017
201P-Q11/02/201711/02/2017
200P-Q11/02/201711/02/2017
301Y-Z1/03/20171/03/2017
301X-AA2/03/20172/03/2017

 

Service

Service NoStartEndVehicle
11/02/20173/02/2017100
210/02/201712/02/2017201
11/02/20173/02/2017101
210/02/201712/02/2017200
31/03/20175/03/2017301

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])


1.PNG

If you have other issues, please let me know.

Best Regards,
Angelia

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

 

Capture.PNG

 

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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