Forum Discussion
data lineage
I'm trying to join tables but I keep getting an error regarding data types and lineage.
The data types are the same (text) but two tables are from diferent sources.
The field to join on is a vehicle registration, hence text. The first table is telematic location data received from an exernal supplier as XML, the second table is the job allocated to each vehicle and comes from an Access DB.
Is there a method of turning off the data lineage so the two tables will actually join ?
Using the DAX command NATURALLEFTOUTERJOIN to build a new table gives the message about data type and lineage. Using relationships within the editor allows me to connect the fields/columns via a registraion table (to give 1-to-many on either side) but the relationship is not traversed when attempting to connect the two for a report.
In essence, I'm trying to identify locations, driver and times for each job a vehicle is used for.
Any thoughts would be appreciated.
4 Replies
Hi rj21440,
You could join two tables via "Merge Queries" in Query Editor mode.
Best regards,
Yuliana Gu
Joining the two queries within the Query Editor has resulted in any changes I make to the resulting query taking a long time (30 mins +) this is having reduced the telematics data XML to a single day which is just over 1500 files and 120 MB (We have 3 years in total). The other Access DB is 1.2 million rows and I'm not in a position to touch that, only load it and reduce it with a query as it's live.
The ideal solution would be to create a relationship between the two tables after loading the queries so the data does not balloon to a size where the PC is struggling to do anything.
This could be done if Power BI could ignore the data lineage and perform something similar to a SQL join. That's why the data lineage appears to be causing a problem. These two sources are related by vehicle registration number/tag and date but come from totally diferent systems.
The wider relationship is shown below. I'm trying to traverse the relationship with VehicleFact in the middle. Some of the relationships have been disabled for now.
I'm in exactly the same situation than you.
It seems that PowerBI doesnt allow LEFT OUTER JOIN relations tables as SQL.
I just found one solution but it doesnt seduce me: subject here
I don't understand the aim to merge large tables by creating a new one (and getting multiples colums) when we do know that will reduce the performance.
Any help please ?
