Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Omid_Motamedise
Memorable Member
Memorable Member

Reconciliation

Hi, I post every other day a challenge on my LinkedIn page, and this week challenge number 110 (provided in the below link) was one of the hard ones, I am eager about other possible solutions:
https://www.linkedin.com/feed/update/urn:li:activity:7238668463412592640/

This is the challenge:

 

Challenge 110: Reconciliation!

After reconciling the company’s bank transactions with the records in the financial department, discrepancies were found between 5 rows of bank transactions and 7 rows of financial records. This indicates that a single bank transaction might have been split into multiple entries in the financial records, or vice versa.

Extract all the possible matching scenarios of bank transactions with a or combination of financial records, ensuring that:
-Each transaction is used only once.

-Transactions cannot be split.

-Exclude any scenarios where multiple rows of bank transactions match with multiple rows of financial records.

Question: Bank Transaction

IDDateValue
B116/05/202410
B224/05/202440
B328/05/202460
B431/05/202460
B54/06/202414


Question: Financial Transaction

IDDateValue
F114/05/202410
F217/05/202430
F319/05/20245
F429/05/20245
F51/06/202410
F64/06/2024120
F75/06/20244


and this is the result;+>

Senarios
B1=F1, B2=F2+F3+F4, B3+B4=F6, B5=F5+F7
B1=F1, B2=F2+F5, B3+B4=F6, B5=F3+F4+F7
B1=F3+F4, B2=F1+F2, B3+B4=F6, B5=F5+F7
B1=F3+F4, B2=F2+F5, B3+B4=F6, B5=F1+F7
B1=F5, B2=F1+F2, B3+B4=F6, B5=F3+F4+F7
B1=F5, B2=F2+F3+F4, B3+B4=F6, B5=F1+F7



6 REPLIES 6
dufoq3
Super User
Super User

Hi, I've played with this a while and I can provide possible combinations of Finacial Transactions for each Bank Transactions (I know that assignment was different)

 

Output

dufoq3_0-1726137390932.png

let
    BankTransactions = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjJU0lEyNFCK1QGyjYBsEyjbGMg2g7JNkNimIPUmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    FinancialTransactions = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjNU0lEyNFCK1QGyjYBsYyjbGMg2hTBNEExTJNVmILYRlGMO5JgoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    BTChangedType = Table.TransformColumnTypes(BankTransactions,{{"Value", type number}}),
    FTChangedType = Table.TransformColumnTypes(FinancialTransactions,{{"Value", type number}}),
    FTBufferedIDList = List.Buffer(FTChangedType[ID]),
    Ad_FTCombinations = Table.AddColumn(FTChangedType, "Combinations", each 
        [ a = List.PositionOf(FTBufferedIDList, [ID]),
          b = List.Buffer(List.RemoveRange(FTBufferedIDList, 0, a+1)), //list for current row combinations
          c = List.Count(b),
          d = List.Accumulate({1..c}, {[ID]}, (state, current)=> state &
                    List.Transform({0..c-1}, (y)=> 
                            [ d1 = List.Range(b, y, current),
                              d2 = if List.Count(d1) < current then null else Text.Combine({[ID]} & d1, "+")
                            ][d2]) ),
          e = if b = {} then {[ID]} else List.RemoveNulls(d) 
        ][e], type list),
    AllCombinations = Table.FromList(List.Combine(Ad_FTCombinations[Combinations]), (x)=> {x}, type table[ID=text]),
    Ad_Value = Table.AddColumn(AllCombinations, "Value", each 
        [ a = Text.Split([ID], "+"),
          b = List.Transform(a, (x)=> List.First(Table.SelectRows(FTChangedType, (y)=> y[ID] = x)[Value])),
          c = List.Sum(b)
        ][c], type text),
    Filtered = Table.SelectRows(Ad_Value, each [Value] <= List.Max(BTChangedType[Value])),
    BufferedCombinations = Table.Buffer(Filtered),
    StepBackToBankTransactions = BTChangedType,
    Ad_Combinations = Table.AddColumn(StepBackToBankTransactions, "Combinations", each BufferedCombinations, type table),
    AddedCustom = Table.AddColumn(Ad_Combinations, "Custom", each Table.SelectRows([Combinations], (x)=> x[Value] = [Value])),
    ExpandedCustom = Table.ExpandTableColumn(AddedCustom, "Custom", {"ID"}, {"Possible Combinations"}),
    RemovedColumns = Table.RemoveColumns(ExpandedCustom,{"Combinations"})
in
    RemovedColumns

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

lbendlin
Super User
Super User

What's the point of the Date column? Is there a dependency?

No, at this stage it is not important, but at the next level I want to add the limitation for time frame, for example the transaction with difference less than 15 days can be matched.

First step:  find all allowed combinations on either side.  Here's the sample code for the Bank table (five items).  For later reference to the values an index column is added as a convenience (not strictly required)

 

Bank table

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjJU0lEyNNM3MNU3MjAyAXEMlGJ1gBJGQLaRCZKECVTCGCRhgSRhBpUAsY0NsUiYgnTrG5jBrTBRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Currency.Type}, {"Date", type date}},"en-GB"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

 

 

 

Combine function to find and deduplicate combinations

 

 

(tablea,listb)=>
let 
    Next = Table.AddColumn(tablea,"id",each listb),
    #"Expanded id" = Table.ExpandListColumn(Next, "id"),
    #"Replaced Value1" = Table.ReplaceValue(#"Expanded id",each [ID],each List.Sort(List.Distinct([ID] & {[id]})),Replacer.ReplaceValue,{"ID"})
in 
    Table.Distinct(Table.SelectColumns(#"Replaced Value1",{"ID"}))

 

 

 

Final result for combinations of five items based on the Bank table

 

 

let
    Source = List.Accumulate({1..Table.RowCount(Bank)-1},Table.FromList(List.Split(Bank[Index],1), Splitter.SplitByNothing(), {"ID"}),(state,current)=>Combine(state,Bank[Index])),
    #"Extracted Values" = Table.TransformColumns(Source, {"ID", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

 

 

 

lbendlin_0-1726101654074.png

 

Then you can lookup the sum value for all these combinations, repeat the process for the seven items of the Financials table, and the do yet another cartesian to match the sums.

 

lbendlin_1-1726103093917.png

 

Thank you for the solution.
It does work (of course we need another column equal to the total values of the combination), but for the case facing many rows, it is too time-consuming. 
Imaging the case of 100 rows (which is not too much) in the bank table, the combinations are too much.

Do you think it is possible to increase the efficency in another way?

While you cannot cheat combinatorics, the results are immutable.  I only computed them as a finger exercise.  For better performance you want to have them precomputed.  The actual value aggregation and comparison does not grow as exponential as it is less and less likey that values match.  Not sure if it is linear  (only real world data can tell) but definitely not exponential.

 

In real life the statement timing constraints will play a much larger role.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors