cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HenningB
Frequent Visitor

Append data based on information in third table

Hello everyone!

 

I've tried all sorts of things and have read a few articles around "merging based on date range" but that's not what I want to achieve.

 

I collect two types of tracking information. One from a device, which is attached to a trailer and continously submits data all day round and another one which is attached to a cargo unit before it is loaded on to trailer and remains on the cargo unit after discharge. Since a trailer carries a lot of cargo pieces over time, an association table exists, which captures the cargo tracker device id, the trailer id and a load/discharge date.

 

Under normal conditions, the cargo tracker cannot obtain a GPS fix during transport, so that the trailer GPS data needs to be taken into account for visualization but it should look like as if the cargo tracker continues to send data to the server (for the end user).

 

Cargo Tracker Table (simplified)

Tracker IDTimestampLATLONG
ABC1234508.04.2021 09:00-29,3234-94,7837
ABC1234508.04.2021 10:00-29,3233-94,7837
ABC1234510.04.2021 14:20-20,3231-79,7836

  

Trailer Tracker Table (simplified)

Trailer IDTimestampLATLONG
308.04.2021 07:00-29,1300-94,6350
308.04.2021 08:00-29,2940-94,7640
308.04.2021 09:00-29,3234-94,7837
308.04.2021 ...  
3...  
310.04.2021 14:00-20,3230-79,7834
310.04.2021 15:00-20,3232-78,7995

 

Association Table

Tracker IDTrailer IDLoad DateDischarge Date
ABC12345308.04.2021 10:0010.04.2021 14:20

 

Desired Output

Tracker IDTimestampLATLONGGPS SourceAssociated Trailer
ABC1234508.04.2021 09:00-29,3234-94,7837Cargo 
ABC1234508.04.2021 10:00-29,3233-94,7837Trailer3
ABC1234510.04.2021 14:20-20,3231-79,7836Trailer3
ABC1234508.04.2021 09:00-29,3234-94,7837Trailer3
ABC1234508.04.2021 ...  Trailer3
ABC12345...  Trailer3
ABC1234510.04.2021 14:00-20,3230-79,7834Trailer3
ABC1234510.04.2021 15:00-20,3232-78,7995Cargo 

 

So it looks like a merge/append combination which I can't pull off. 

 

Appreciate your support,

 

Henning

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @HenningB ,

I created a sample pbix file (see attachment), please check whether that is what you want. If no, please provide your expected result with more details. 

yingyinr_0-1618305943154.png

Best Regards

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

View solution in original post

2 REPLIES 2
yingyinr
Community Support
Community Support

Hi @HenningB ,

I created a sample pbix file (see attachment), please check whether that is what you want. If no, please provide your expected result with more details. 

yingyinr_0-1618305943154.png

Best Regards

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

View solution in original post

lbendlin
Super User III
Super User III

Yes, a table combine should suffice - you would only have to deal with the cargo lookups for the gaps. The assumption would be that the trailer GPS position would always be more accurate than the cargo GPS position, and that the reporting timestamp would be pegged somehow so that both cargo and trailer report "at the same time".

 

Here's something to get you started.  Your sample data is not good enough for more.  You may want to include more cargoes etc.

 

 

let
    Source = Table.Combine({#"Cargo Tracker Table", #"Trailer Tracker Table"}),
    #"Sorted Rows" = Table.Sort(Source,{{"Timestamp", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Timestamp"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "GPS Source", each if [Trailer ID] = null then "Cargo" else "Trailer"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Tracker ID", "Timestamp", "LAT", "LONG", "GPS Source", "Trailer ID"}),
    #"Filled Down" = Table.FillDown(#"Reordered Columns",{"Trailer ID"})
in
    #"Filled Down"

 

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors