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
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 |
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
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.
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.