Forum Discussion
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
| ID | Date | Value |
| B1 | 16/05/2024 | 10 |
| B2 | 24/05/2024 | 40 |
| B3 | 28/05/2024 | 60 |
| B4 | 31/05/2024 | 60 |
| B5 | 4/06/2024 | 14 |
Question: Financial Transaction
| ID | Date | Value |
| F1 | 14/05/2024 | 10 |
| F2 | 17/05/2024 | 30 |
| F3 | 19/05/2024 | 5 |
| F4 | 29/05/2024 | 5 |
| F5 | 1/06/2024 | 10 |
| F6 | 4/06/2024 | 120 |
| F7 | 5/06/2024 | 4 |
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
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"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.
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
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
