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
rj21440
Frequent Visitor

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 4
v-yulgu-msft
Employee
Employee

Hi @rj21440,

 

You could join two tables via "Merge Queries" in Query Editor mode.

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Capture.PNG

 

Anonymous
Not applicable

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 ?

Sorry for the delay, I have had to create a new account as it wouldn't let me reset the password....

 

You probably aren't going to like the work around. Basically I'm using R to clean up the data before loading it.

 

That said the data I have is so large that I haven't been able to load it into memory to do the join in R. So I now have a workaround on the workaround.

 

There is an R package which allows the data to be used in creating an SQLlite DB on disk just for the duration of a command. It takes an inordinate amount of time to run but has no restriction on the size of the data and allows some complex SQL to manipulate the data.

 

That requires 'R' to be installed (it's free public domain software and has a Microsoft supported version) and I have some experience with it. Apparently the latest BI has python available as well, but being an R person I wouldn't know much about it.

 

Did you try to create combinations of columns and use them as an index to link the two ?

 

Roy

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.