matching debits and credits based on line item description

[cparrot]

I have a list of thousands of transactions at the line item level, and I am trying to match corresponding debits and credits and mark them as match and  unmatched. i was trying to use the absolute value to create a duplicate key, but if a line item has multiple same debit amounts (see keyboard cleaner), i dont want two similar debits canceling each other, leaving the credit as unmatched. that would throw off my totals and i would not be able to reconcile dollar amounts.

Line Item Text		D/C		ABS
Parking		30		30
Bank Charges		-48.81		48.81		Match
Bank Charges		48.81		48.81		Match
Bank Charges		10		10
Bank Charges		25		25
Neck Pillow		15		15
Earbuds		38		38
S&P Global Inc		-3150		3150
Keyboard Cleaner		95.48		95.48		match
Keyboard Cleaner		95.48		95.48			<-- cannot use abs here since multiple debits and do not want false match
Keyboard Cleaner		-95.48		95.48		match
Keyboard Cleaner		250.83		250.83
Keyboard Cleaner		-323.55		323.55
Meals		119.04		119.04
Meals		26.74		26.74
Meals		27.79		27.79
Meals		123.22		123.22
Screen Protector		-27.39		27.39		Match
Screen Protector		27.39		27.39		Match
Screen Protector		8.43		8.43

[bolfri · Accepted solution]

IMPORTANT! I have , as a decimal, so you need to remove step "Replaced Value" to work 🙂

Step. 1: Group by same values and give them index, so for the Keyboard Cleaner you will get 2 rows inside, but Power BI will now know that they are not the same (even if the rest of the values are).

= Table.Group(#"Changed Type", {"Line Item Text", "D/C"}, {{"AllRows", each Table.AddIndexColumn(_,"Index"), type table [Line Item Text=nullable text, #"D/C"=nullable number, #"Index"=nullable number]}})

Step 2. Remove all other columns and expand this table.

If you want I can show you how to finish it in the Power Query M, but if you prefer DAX:

Match = 
var line_item = [Line Item Text]
var dc_value = [D/C]
var index_value = [Index]
var maching =
    COUNTROWS(
        FILTER('Sample',
                [Line Item Text] = line_item
                &&
                [D/C] = dc_value*-1
                &&
                [Index] = index_value
        )
    )
return
    IF(maching,"Match")

[D/C] = dc_value * -1 is very important, because we want to find a mirror for that row, not abs value

 Results:

PBIX FILE: https://we.tl/t-mO7BcQ7gc8

[bolfri · Accepted solution]

IMPORTANT! I have , as a decimal, so you need to remove step "Replaced Value" to work 🙂

Step. 1: Group by same values and give them index, so for the Keyboard Cleaner you will get 2 rows inside, but Power BI will now know that they are not the same (even if the rest of the values are).

= Table.Group(#"Changed Type", {"Line Item Text", "D/C"}, {{"AllRows", each Table.AddIndexColumn(_,"Index"), type table [Line Item Text=nullable text, #"D/C"=nullable number, #"Index"=nullable number]}})

Step 2. Remove all other columns and expand this table.

If you want I can show you how to finish it in the Power Query M, but if you prefer DAX:

Match = 
var line_item = [Line Item Text]
var dc_value = [D/C]
var index_value = [Index]
var maching =
    COUNTROWS(
        FILTER('Sample',
                [Line Item Text] = line_item
                &&
                [D/C] = dc_value*-1
                &&
                [Index] = index_value
        )
    )
return
    IF(maching,"Match")

[D/C] = dc_value * -1 is very important, because we want to find a mirror for that row, not abs value

 Results:

PBIX FILE: https://we.tl/t-mO7BcQ7gc8

[cparrot]

Hi. thanks again for the help. This logic worked except in a few rare cases. (see screenshot below) in this case i have 9 identical line items (which is why i've included an index),

6 should match and offset, 3 should go into the unmatched. but for some reason, i get an odd match that shouldnt be. it should be in the unmatched bucket.

[bolfri]

This is weird. Also the fact that you have like 12750 unique indexes for one line item and value.

In your case if you have 9 unique values, which 3 of them are negative and 5 of them are positive: then you should end with like index 5 MAX. 

I think you didn't use my solution at all 🙂 I've changed the source data in the solution that I've described you. I've added 9 more lines with Excam Coding Free. 3 of them are Negative with value -300 and rest of them are positive with value 300.

Input:

Output:

I think it's working fine.

[cparrot · Accepted solution]

Hi, I did, indeed, use your solution. The index is added because i have 100K lines of data but not for the same exam coding fee item.

i added the grouping and match index

which gave me this

and used DAX in the front end table

@16Match =

var line_item = '16 Debits and Credits'[Line Item Text]

var dc_value = '16 Debits and Credits'[Group Currency]

var index_value = '16 Debits and Credits'[Match]

var matching =

    COUNTROWS(

        FILTER('16 Debits and Credits',

                '16 Debits and Credits'[Line Item Text] = line_item

                &&

                '16 Debits and Credits'[Group Currency] = dc_value*-1

                &&

                '16 Debits and Credits'[Match] = index_value

        )

    )

return

    IF(matching,"Match")

and, as stated, it works very well, except for a few areas. 

*Exam Coding fee is what im filtering on to show as an example.

but thousands of other lines are working fine. it's just a few that seem to hiccup.  and i dont know why.

[cparrot]

I have rebuilt this multiple times from scratch and the failures occur on the same exact examples of line items every time. the error is not random. it must be data issue with those particular line item descriptions. i have tried trimming to remove any padding, next i will try to lowercase everything. 

[bolfri]

Can you show me the values in this "Match" column with index number from my solution? 🙂 It will be easier to understand what's goin on.

[cparrot]

Hi, Thanks again. it's working fine now. i trimmed, lowercased, sorted, cleaned, lost, found, restructured, and finally trimmed again and now all the fields are working correctly. 😁

[cparrot]

Thanks! yes, you are correct. matching on Abs value was causing issues with similar debits being flagged as matching, thus leaving the credit as unmatched. This worked very well. 

[Ahmedx]

see attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhgCvMt2xlt4fkBtc?e=WhKwtW

[cparrot]

Hi, thank you for the quick response. i do seem some inconsistency with the "match" marking.

some pairs only show "match" on either the debit or the credit, but others show "match" on both debit and credit.