Recon 2 tables and additionally against status in separate 3rd table

[Pafik_S]

Hi All,
I'm struggling to get a recon status for the following case and hope to get some suppot from smarter minds 🙂

Objective:
Automatic recon between System X & Y including the status of exceptions (and that's is the tricky part I can't yet solve)

Case:
Table 1 - System X

Doc type	Doc ID	Doc Date	GUID
A	...	....	...
B	...	...	...
C	...	...	...
D	...	...	....
E	...	...	...

Table 2 - System Y

Doc type	Doc ID	Doc Date	GUID
A	...	....	...
B	...	...	....
C	...	...	...
D	...	...	...
E	...	...	...

Table 3 - Exceptions

System X	System Y	Status
A	A1	OK
A	A2	OK
*	A3	OK

* - stands for any status in system X

Desired outcome:

Automatic recon between System X & Y including the exceptions (and that's is the tricky part I can't yet solve)

System X	System Y	Status
A	A	OK
A	A1	OK
B	C	NOK
E	E	OK
A	A2	OK
B	E	NOK

Many thanks in advance for looking into the topic!

[Pafik_S]

I want to get a report that would compare extracts from 2 systems. So I want to see if the document registered in system X is also in the same status in system Y. However, the system Y has some extra statuses that do not reconsile 1:1 with system X, but are still OK. So I want to verify those extra statuses against a defined exception list and have the outcome in the final report.
Example:
System X has 'Doc A', System Y has a 'Doc A1'. Direct recon tells me it's a mismatch, but when I look at the exception list (A = A1 --> OK) I see that's actually fine and hence the final status for this line item should be OK. Meaning no further action needed. 

[dufoq3]

Ok, I understand but they might be other factors. Could you please update SAMPLE DATA (replace dots with some text or document) and provide also expected result based on sample data please. If your data are not sensitive - just share with us real sample.

[Pafik_S]

@dufoq3 
many thanks for your patience. Here is an example / sample:

System X

Doc ID	Doc Type	Doc Date	GUID
43543	ABCD	25.12.2023	dsgfdfgs452w33
33325	EDFG	25.11.2023	wasr5546tdsf
87645	CDOM	04.02.2024	fsdt43w4refdfhsse5
54656	OPRG	14.01.2024	dfse4r53fdsdag
54702	DSGT	23.02.2024	w5q3rsdfsd
12430	AFRA	05.03.2023	wt5433qfaewa
33325	EDFG	25.11.2023	wasr5546tdsf
45165	AFRA	23.03.2024	yrthgbd6xszfv
56432	AFRA	03.03.2024	54y7fxcd5yxu

System Y

Doc ID	Doc Type	Doc Date	GUID
43543	ABCD	25.12.2023	sdgfse564ag
33325	EDFG-ODF23	25.11.2023	sg65ewefdfg
87645	CDOM	04.02.2024	cnxbd4rtye5dhx
54656	OPRG	14.01.2024	zds4td6hxfd
65654	CURD	15.02.2024	vbgz4t6djncx
12430	AFRA-YXF	05.03.2023	syrs456shxfd
33325	EDFG	12.14.2023	sg65ewefdfg
45165	AFRA-3245cx	23.03.2024	tgferfdbx45
56432	AFRA	03.03.2024	54y7fxcd5yxu

Mapping (exceptions) - this table needs to be dynamic so that additonal exceptions can be added (without adding conditional clauses in PQ)

System X - Doc Type	System Y - Doc Type	Status
AFRA	AFRA-YXF	OK
AFRA	AFRA-3245cx	OK
EDFG	EDFG-ODF23	OK

Expected output:

System X	System Y	Status
ABCD	ABCD	OK
AFRA	AFRA	OK
AFRA	AFRA-3245cx	OK
AFRA	AFRA-YXF	OK
CDOM	CDOM	OK
DSGT		NOK
EDFG	EDFG	OK
EDFG	EDFG-ODF23	OK
OPRG	OPRG	OK
	CURD	NOK

The bold items need to be validated against the Mapping (exceptions) table and I'm not clear how to do it smart in PQ 😞 

----

The rest was easy:
What I did was to create an ID in table X & Y (e.g. 43543-ABCD-25.12.2023 --> 'Doc Id - Doc  type - doc date') that would allow me to merge the tables and get the overview. 

[dufoq3]

• Merged by [Doc Type] and [Doc Date] columns.
• I expect that you have only 1 record per day. If you have more - I have to edit my query.
  (I've edited your sample, because there were multiple records with same date for [Doc Type] EDFGH in SystemX and unknow date 12.14.2023 in SystemY)
• I also expect that dates between SystemX and SystemY matches! - Let me know if they don't. 

If you don't need columns like Doc ID and Doc Date in result table let me know --> query could be simpler.

BTW I haven't used MAPPING table. With your sample data it was enough to extract text before delimiter "-". If it is not that simple with real data and we need such mapping table - let me know, but you have to edit sample data to be more realistic.

Result:

let
    SystemX = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZCxUsMwDIbfxXMvZ1uSw1oa2qlXrrDlMgQcp2utUCc8PXJoIEsnn63vlz65rhUCIaiN2j7vKjksFcYWVtv85rkPPvSMZBOAaja1AgBLUnqp9oc7rhc8tRyJ0A2ewww/lQ4zvKtORzk0FnrujXIJ7AeEhLGTERfmjuaIxMlJ+fR6zv2NRMwS8YE7jATBs2/7O15qK6Xq7fCedWA1IdEVIkvIz6ixCDovuj9vswwVGv7MB/kEuIa2S+2jNc3DNZGMo//OWQIWiSkOl/7Du5G/w+1X2SHYlceaJpzKMH56msYv1TQ/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Doc ID" = _t, #"Doc Type" = _t, #"Doc Date" = _t, GUID = _t]),
    SystemY = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDNboMwEITfxecE+WfX6TWFkFNFhVSpFeJQsE3UQw6YtiZP37VDEJeeVl5/M55x0zBQCIrt2PE5L2hIzITMJJdx583gvEUNnwNrdw1TSkmk/akoz/uqKBMUFXxVDBrtr3XG3RVPBw1RkRfVCw0OGU/uQIf+GjoD4zRbNJeQcASNmq6q1/pMQxAuHvjNeJiMvgRnEkskxn3+VsfgAjfWP91wg0mbr2t/NxYSFI8ty/q4/3gvYxYSqDX3PHpA7Vf3bdWlpPivJKDQ+DBXEpAeJYla/GOgaXB2dKYL9BupqAYlF0nMsmUR5oMLvcE5fLO2/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Doc ID" = _t, #"Doc Type" = _t, #"Doc Date" = _t, GUID = _t]),
    Ad_DocTypeX = Table.AddColumn(SystemY, "Doc Type X", each Text.BeforeDelimiter([Doc Type], "-"), type text),
    StepBack_to_SystemX = SystemX,
    MergedQueries = Table.NestedJoin(StepBack_to_SystemX, {"Doc Type", "Doc Date"}, Ad_DocTypeX, {"Doc Type X", "Doc Date"}, "SystemY", JoinKind.FullOuter),
    RenamedColumns = Table.RenameColumns(MergedQueries,{{"Doc Type", "Doc Type X"}}),
    Ad_Status = Table.AddColumn(RenamedColumns, "Status", each if [Doc Type X] = [SystemY][Doc Type X]{0}? then "OK" else "NOK", type text),
    Ad_FinalRecord = Table.AddColumn(Ad_Status, "FinalRecord", each
        if [Doc ID] <> null
        then Record.RemoveFields(_, {"SystemY", "GUID"}) & [Doc Type Y = [SystemY][Doc Type]{0}]
        else Record.RenameFields(Record.RemoveFields(Table.ToRecords([SystemY]){0}?, {"Doc Type X", "GUID"}), {"Doc Type", "Doc Type Y"}) & [Status = [Status]], type record),
    FinalRecord = Table.FromRecords(Ad_FinalRecord[FinalRecord], null, MissingField.UseNull),
    ReorderedColumns = Table.ReorderColumns(FinalRecord,{"Doc ID", "Doc Type X", "Doc Type Y", "Status", "Doc Date"})
in
    ReorderedColumns

[Pafik_S]

Hiya dufoq3,
many thanks for your effort and time! Really appreciate it. Just a few add-ons to the above :S

• Context: System X is a CRM system (doc registration to identify events e.g. contact details change, while System Y is an archive for the document itself e.g. scanned mail, e-mails, forms etc)
• There are tens to hundreds of records per day. The dates may not match i.e. The event may be registered today in System X, but archive may happen on the next day(s). It can actually be also the other way round (due to human error)
• Doc ID and Doc Date are needed for recon purposes / quality control check. There are more columns that would be compared, but I just captured those to show the idea / get the mechanics I can leverage.
• The mapping table - it's actually needed, as it allows to catch exceptions (i.e. old archive was migrated into a new one and some doc types (forms) have not been adjusted correctly). Hence ideally the mapping table is dynamic for exceptions to be added once identified (not all know yet).

[dufoq3]

Hi, you should prepare new sample data covering all possible issues and combinations, because creating queries with inappropriate sample is just wasting of time... Let us know when you prepare such data. Don't forget to provide also expected result based on sample data. If it is better to share some document, you can upload excel file to google drive for instance.

[dufoq3]

Hi, I'd like to help you but to be honest I don't understand what do you want to achieve.