Hello Everyone,I have a situation where I am trying to use Power Query to match a list of data, see table below.We have Columns ID, Match, Net, Branch & Dates which are important in this process:Bank has Positive Numbers in Net ColumnOR has Negative Numbers in Net ColumnI would like to make a one to one match of Bank Numbers against OR Numbers and populate in Column B Match with value 1 for all those amounts matching.If the numbers match exactly with each other that's fantastic, but in general it is not the case,and I am prepared to allow a tolerance limit for the difference from minus 2 to plus 2.So if the compared amounts throw out a difference within this range it can be assumed they are matched.Some Rulesthe matching should be between Bank vs OR on absolute values eg plus 100 against minus 100 assumed matchednumbers matching within each branch not against another branch numberfor the moment we can leave out date criteriaIDMatchNet TypeBranch DateBANK 102.67 11804-Oct-19BANK 183.42 11804-Oct-19BANK 313.01 11805-Oct-19OR 183.75 11806-Oct-19OR -102.57 11806-Oct-19OR -183.17 11806-Oct-19OR -183.17 11806-Oct-19OR -312.96 11806-Oct-19OR -237.24 11827-Nov-19BANK 237.25 11827-Nov-19OR -249.11 11827-Nov-19BANK 336.94 11827-Nov-19OR -337.04 11827-Nov-19BANK 348.9 11827-Nov-19BANK 588.14 11827-Nov-19OR -588.63 11827-Nov-19BANK 1616.85 15710-Oct-19OR -1637.54 15710-Oct-19BANK 879.22 15721-Oct-19BANK 336.94 15722-Oct-19OR -337.04 15722-Oct-19BANK 364.09 15722-Oct-19OR -364.15 15722-Oct-19OR -879.22 15722-Oct-19

In your query you are allowing a difference of up to +/- 100 by grouped sum by Branch. That does't give us the required accuracy level at each distinct amount. That is why I mentioned in my post that at each match the difference can only go up to + / - 2. What we expect from the posted data is similar to the attached picture, we are left with four rows highlighted in RED not matched:

Hi mbuhary Very sorry, I'm struggling to understand the requirement, can you explain why this 4 not matched? Is there any rule to how you match Bank and Or? Thanks Mariusz

Yes, I will try to explain more.If you see first amount in Red -249.11-there is no exact match of a positive amount (or Bank amount) within the same branch (118);- also there isn't any other positive amount (or Bank amount) within the same branch (118) which would come as close as it would be only differ by plus or minus 2. An exact match is like for example in Branch 118 if we had Bank & Net 25.5 matched against OR & Net -25.5.But in the data sheet we dont have this situation. All amounts come close but doesn't match to the 2nd decimal point. To give you a an of examples where we matched with allowing our threshold for difference (+/- 2) is you can look at:data row 1 +102.67 & 5 -102.57 (1 is "Bank" & 5 is "OR" while both are within same branch (118) and difference of the sum of both is within +/- 2 ( 102.57 - 102.67 = -0.10). I hope its cleat enough.

Hello mbuhary and what should happen when a row can match 2 items form the table? BANK: 102 / OR: 101 OR:102? Jimmy

Power Query to Match Bank vs OR amounts within each Branch

19 Replies

Mariusz

Community Champion

6 years ago

Hi mbuhary

Please see the attached file with a solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdLBDsIgDAbgVzE7j4a2UOCoV5Mt8brs5AN4MT6/bE4nSlYPXggkX/7SwjA0h313bNoGLYGEvNlNB4x5tc7056vB1Izt6iKDI90xMlgsnX93/WlJC75U8qXMdDkfdJbT8G+MkSCJyogDkCsYBdNdbh8DmZ3fcEucS4CoxzELpK2ySxO5qv3hduwiJJ35GAH1qhMT1uNQUCC+hjI/MdrKi0luw7sNtwTGkICocIS131lM7+Go8gWK6VXYM04c2KTHZYZeZbUmVjbeAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Net " = _t, #" Type" = _t, Branch = _t, #" Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Net ", type number}, {" Type", type text}, {"Branch", Int64.Type}, {" Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Branch"}, {{"tbl", each _, type table [ID=text, #"Net "=number, #" Type"=text, Branch=number, #" Date"=date]}, {"sum", each List.Sum([#"Net "]), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Branch"}, {{"tbl", each _, type table [ID=text, Branch=number, tbl=table, sum=number]}, {"sum", each List.Sum([sum]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Match", each if [sum] <= 100 and [sum] >= -100 then 1 else 0),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Match", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Branch", "tbl", "Match"}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Removed Other Columns", "tbl", {"ID", "tbl"}, {"ID", "tbl.1"}),
    #"Expanded tbl.1" = Table.ExpandTableColumn(#"Expanded tbl", "tbl.1", {"Net ", " Type", " Date"}, {"Net ", " Type", " Date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded tbl.1",{{" Date", type date}, {"Net ", type number}})
in
    #"Changed Type2"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Power Query to Match Bank vs OR amounts within each Branch.pbix27 KB

mbuhary
Helper I
6 years ago
In your query you are allowing a difference of up to +/- 100 by grouped sum by Branch. That does't give us the required accuracy level at each distinct amount.

That is why I mentioned in my post that at each match the difference can only go up to + / - 2.

What we expect from the posted data is similar to the attached picture, we are left with four rows highlighted in RED not matched:
- Mariusz
  Community Champion
  6 years ago
  Hi mbuhary
  
  Very sorry, I'm struggling to understand the requirement, can you explain why this 4 not matched?
  
  Is there any rule to how you match Bank and Or?
  
  Thanks
  
  Mariusz

Forum Discussion

Power Query to Match Bank vs OR amounts within each Branch

19 Replies

Related content

Node Branch Visual

Mirroring, DTAP workspaces and branches

branching out issues

Commonality across branches

Git Branch Feature Question

ID	Match	Net	Type	Branch	Date
BANK		102.67		118	04-Oct-19
BANK		183.42		118	04-Oct-19
BANK		313.01		118	05-Oct-19
OR		183.75		118	06-Oct-19
OR		-102.57		118	06-Oct-19
OR		-183.17		118	06-Oct-19
OR		-183.17		118	06-Oct-19
OR		-312.96		118	06-Oct-19
OR		-237.24		118	27-Nov-19
BANK		237.25		118	27-Nov-19
OR		-249.11		118	27-Nov-19
BANK		336.94		118	27-Nov-19
OR		-337.04		118	27-Nov-19
BANK		348.9		118	27-Nov-19
BANK		588.14		118	27-Nov-19
OR		-588.63		118	27-Nov-19
BANK		1616.85		157	10-Oct-19
OR		-1637.54		157	10-Oct-19
BANK		879.22		157	21-Oct-19
BANK		336.94		157	22-Oct-19
OR		-337.04		157	22-Oct-19
BANK		364.09		157	22-Oct-19
OR		-364.15		157	22-Oct-19
OR		-879.22		157	22-Oct-19