Forum Discussion
Multilple recursive Inner and Anti Joins for robust fuzzy date matching - Can List.Accumulate help?
I’m trying to reconcile data from two different entities regarding movement of shipping containers, by doing a bulletproof fuzzy match on dates. One is a shipping port, the other is a railway.
Here's a simplified sample file illustrating what I'm trying to do:
Match to closest day_20190722 buffer Revised.xlsx
I work at the port, and I want to ensure that the railway are charging us for the correct amout of containers they are moving to/from the port. But the dates in the two systems don’t always match for the reasons outlined below:
- Containers have a unique ID and a direction of travel common to both systems…either RAIL IN or RAIL OUT.
- The Port system records when containers enter or leave the port, on their way to/from the Rail depot.
- The Rail system records when the containers are actually put on a train and railed somewhere. (Either inwards, or outwards as containers are sent to customers who load or unload them as the case may be, and then they go to the port so they can be loaded onto ships (if full) or stored at the port (if empty).
- The containers might sit at the rail depot for minutes, days, or weeks. And they might make multiple outward or inward trips in a matter of days. (e.g. out, then in, then out, then in again).
I need to do a "Fuzzy Date match" in order to reconcile one system to the other, that doesn’t inadvertently double count when containers are moved rapidly back and forth between the port and the customer multiple times. So I need to progressively increase the 'mismatch' tolerence on those dates, and remove matches at each pass leaving just the unmatched rows to do increasingly desperate matches on. (If I don't remove the matches at each subsequent pass, then inevitably some container movements get incorrectly matched to their next similar move).
I have a hard-coded query that does this works fine, but it is cumbersome. I wonder if it is possible to use something like List.Accumulate to make the query dynamic. But I have 2 seed tables, and need three output tables, so it might be a pipe dream.
Here's a screenshot of sample input and output. I've color coded the matching records from each table to the 'Matched Records' table below. (Edit: I've revised this as per reply to ImkeF below)
My manual query performs the following steps:
- Loads data from Rail and from Port, and does an inner join.
- Effectively removes these matches from the original Rail and Port tables via an anti-join against each table.
- Does an inner join on the remaining records, but this time offsets the dates in the Port table by +/- 1 day. This finds some more matches.
- Performs steps 2 and 3 over and over, increasing the date offset by 1 each time until I have captured all date mismatches up to a week.
Here’s how that looks in the query dependency view:
And here's how I do the 'Fuzzy Date Match": At each 'pass', my Day X Matched step takes the unmatched records from the two tables at the end of the previous Day X Matched step, and creates a 'Date Offset' column using this general approach:
= Table.AddColumn(Source, "Table2.Date", each List.Dates(Date.AddDays([Date],-X),2,#duration(2X,0,0,0)))
That X bit successively pads out the date in one table so that it will match dates 1, 2, ..., X days before or after.
All this requires a lot of different Queries. Here's the amount of queries required just to handle 5 days' worth of date offsets:
What I would like to do is use List.Accumulate or similar to make this dynamic. Here’s how that would look:
Uwe from the data-insights blog has a fantastic 3 part series on recursion in PQ, including great references to the usual suspects (Chris Webb, ImkeF , and others). This shows some great uses of List.Accumulate and List.Generate. But working out whether these functions are flexible enough to do the above is beyond me.
Anybody feeling brave enough to take a stab, or even give me their thoughts as to whether this is possible?
Match to closest day_20190722 buffer Revised.xlsx
9 Replies
- ImkeF
Community Champion
I think a "simpler" group and sort would do the job here as well:
1) Group by "ID" and "Direction"
2) Sort by "Date" and add Index column
3) Merge on ID, Direction and new Index column in FullOuter-mode: Matches and non-matches will be shown
See attached file
- JeffWeir
Advocate V
Hi ImkeF
Unfortunately that approach isn’t robust under all circumstances, as it will always match an entry in one table with a previous entry in the other table even if a closer match is available. And this is a very real possibility in this dataset.
Revised sample file and screenshot: Match to closest day_20190722 buffer Imke.xlsx
- ImkeF
Community Champion