Reconciling On Dollar Amounts Power Query

[scottalan333]

Hello,
I have two statements with hotel charges on them. What I need to do is take one from the credit card company, and the other one from the database and reconcile the amounts. I added both reports to Power Query, and I have Merged as new. This works perfectly to see the unique amount that is on the credit card statement but not in the database. however, if the amount is not unique it seems to just duplicate the number where it doesn't belong. If and only if the amount is not unique. What am I doing wrong? I read something that said I should aggregate the table, but that is actually worse. Thanks for any help.

 

[alansidman]

Provide us with some anonymized samples of 10-15 records using XL2BB so that we can see what you are dealing with and offer some sound solutions that are specific to your needs. No pictures please as we cannot manipulate data in pictures.

 

[GraH]

Sounds you link on amounts, maybe you can link on more fields to ensure data integrity?
Now for reconsiliation, you might want to append.
Then group by on account number and maybe some other fields and use a sum of amount or maybe all rows as aggregation. The latter gives you a subtable with the grouped details. This can enable you to do some other analysis on the subtable.
I would also add a source field in the original table queries. That way I can always trace the data Origin.

 

[scottalan333]

Hello,
I have two statements with hotel charges on them. What I need to do is take one from the credit card company, and the other one from the database and reconcile the amounts. I added both reports to Power Query, and I have Merged as new. This works perfectly to see the unique amount that is on the credit card statement but not in the database. however, if the amount is not unique it seems to just duplicate the number where it doesn't belong. If and only if the amount is not unique. What am I doing wrong? I read something that said I should aggregate the table, but that is actually worse. Thanks for any help.

First Set of Amounts : 188,193,193,194,199,210.40,211,213.91,218.69,220.41,233.20,234,235,235,235.65,240,242,244.74,285,297.13,310.44,509.30,643.55,648.48,747.82,2282.20
Second Set of Amounts : 234,193,285,199,235.65,218.69,747.82,194,220.41,297.13,2282.20,213.91,210.40,235,240,233.20,242.00,509.30,188,235,643.55,211,648.48,310.44

Here are the sets of numbers I am trying to reconcile through Power Query. These are two statements that should match up, and the ones that don't match need to be investigated. Works great for unique values. I have tried to add an index column, and it sorta works, but not completely intuitive for the end user. Thank you so much for any help.

 

[scottalan333]

Sounds you link on amounts, maybe you can link on more fields to ensure data integrity?
Now for reconsiliation, you might want to append.
Then group by on account number and maybe some other fields and use a sum of amount or maybe all rows as aggregation. The latter gives you a subtable with the grouped details. This can enable you to do some other analysis on the subtable.
I would also add a source field in the original table queries. That way I can always trace the data Origin.

Yes unfortunately I am linking on amounts, not really by choice at this time. It would be much easier to have a unique key. I did try to append, but it isn't intuitive as much for the end user. Unfortunately no unique identifier I have at the moment.

 

[GraH]

If only information you have is an amount, then what's there to investigate?
Anyway, you can force a counter on the amount that serves as key.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Set 1", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Set 1"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Rept", each {1..[Count]}),
    #"Expanded Rept" = Table.ExpandListColumn(#"Added Custom", "Rept")
in
    #"Expanded Rept"

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Set 2", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Set 2"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Rept", each {1..[Count]}),
    #"Expanded Rept" = Table.ExpandListColumn(#"Added Custom", "Rept")
in
    #"Expanded Rept"

let
Source = Table.NestedJoin(set1, {"Set 1", "Rept"}, set2, {"Set 2", "Rept"}, "set2", JoinKind.FullOuter),
#"Expanded set2" = Table.ExpandTableColumn(Source, "set2", {"Set 2", "Rept"}, {"Set 2", "Rept.1"})
in
#"Expanded set2"

 

[scottalan333]

If only information you have is an amount, then what's there to investigate?
Anyway, you can force a counter on the amount that serves as key.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Set 1", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Set 1"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Rept", each {1..[Count]}),
    #"Expanded Rept" = Table.ExpandListColumn(#"Added Custom", "Rept")
in
    #"Expanded Rept"

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Set 2", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Set 2"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Rept", each {1..[Count]}),
    #"Expanded Rept" = Table.ExpandListColumn(#"Added Custom", "Rept")
in
    #"Expanded Rept"

let
Source = Table.NestedJoin(set1, {"Set 1", "Rept"}, set2, {"Set 2", "Rept"}, "set2", JoinKind.FullOuter),
#"Expanded set2" = Table.ExpandTableColumn(Source, "set2", {"Set 2", "Rept"}, {"Set 2", "Rept.1"})
in
#"Expanded set2"
View attachment 103257

Thank you, this is probably the best I will get. I appreciate you taking the time. Now it is much easier for them to see if one of the amounts is doubled up on one statment and not the other.

 

[GraH]

Glad to assist, given the data, I do not see nor know another way.
If it's a solution, you can consider to mark my post as a solution. You never know it might help someone else too.