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
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.
Solved! Go to 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
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
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 |
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] )
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 |
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 |
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |