Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ID | Timestamp | LAT | LONG |
ABC12345 | 08.04.2021 09:00 | -29,3234 | -94,7837 |
ABC12345 | 08.04.2021 10:00 | -29,3233 | -94,7837 |
ABC12345 | 10.04.2021 14:20 | -20,3231 | -79,7836 |
Trailer Tracker Table (simplified)
Trailer ID | Timestamp | LAT | LONG |
3 | 08.04.2021 07:00 | -29,1300 | -94,6350 |
3 | 08.04.2021 08:00 | -29,2940 | -94,7640 |
3 | 08.04.2021 09:00 | -29,3234 | -94,7837 |
3 | 08.04.2021 ... | ||
3 | ... | ||
3 | 10.04.2021 14:00 | -20,3230 | -79,7834 |
3 | 10.04.2021 15:00 | -20,3232 | -78,7995 |
Association Table
Tracker ID | Trailer ID | Load Date | Discharge Date |
ABC12345 | 3 | 08.04.2021 10:00 | 10.04.2021 14:20 |
Desired Output
Tracker ID | Timestamp | LAT | LONG | GPS Source | Associated Trailer |
ABC12345 | 08.04.2021 09:00 | -29,3234 | -94,7837 | Cargo | |
ABC12345 | 08.04.2021 10:00 | -29,3233 | -94,7837 | Trailer | 3 |
ABC12345 | 10.04.2021 14:20 | -20,3231 | -79,7836 | Trailer | 3 |
ABC12345 | 08.04.2021 09:00 | -29,3234 | -94,7837 | Trailer | 3 |
ABC12345 | 08.04.2021 ... | Trailer | 3 | ||
ABC12345 | ... | Trailer | 3 | ||
ABC12345 | 10.04.2021 14:00 | -20,3230 | -79,7834 | Trailer | 3 |
ABC12345 | 10.04.2021 15:00 | -20,3232 | -78,7995 | Cargo |
So it looks like a merge/append combination which I can't pull off.
Appreciate your support,
Henning
Solved! Go to Solution.
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.
Best Regards
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.
Best Regards
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"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |