Forum Discussion
Identify data not used/matched
I work with a number of large reconciliations where I have to match then entries from one table (actual) to the entires in several other tables (source).
There are a few items I need to accomplish in this process.
1) I need to identify in Source, the corrisponding row in Actual
2) I need to identify in Actual, What row has been used in Source
3) I need to identify in Actual, What rows are not included in Source
4) As a complication, the value in Actual will always be 100% of the value, however the Source, could be made up of two rows. (see Transaction 303 in the below example)
As an example below I will just use one source, though it really comes from four.
Source Data Actual Data
Row Transaction Value Transaction Value
1 101 200 101 200
2 102 150 420 275
3 103 125 104 200
4 104 200 300 180
5 300 180 303 750
6 301 250 103 125
7 303 500
8 303 250
9 420 275
My problems are below:
1) I need to identify in Source, the corrisponding row in Actual
If I do a 'lookupvalue' in Actual in transactions, I can ID the row, however when I run into transaction 303, I need to identify the result as both row 7 & row 8. any thoughts?
2) I need to identify in Actual, What row has been used in Source
I can just to a 'lookupvalue' in Source to actual using the row that has just been identified. in step 1.
3) I need to identify in Actual, What rows are not included in Source
Looking at it, step two would then provide me the results needed through no results.
So I guess I am looking to resolve my issue in step 1, and then if any other ideas or insights would be great.
My data sets are a few thousand lines long, and there are more complexities than this, however it is a good starting point.
Thanks!
1 Reply
Mr_Stern Not sure if I understood it correctly but to start with create this calculated column in your source table
IsInActual = VAR _totalByTransaction = CALCULATE(SUM(Source[Value]),ALLEXCEPT(Source,Source[Transaction])) VAR _check = LOOKUPVALUE(Source[Transaction],Actual[Transaction],Source[Transaction],Actual[Value],_totalByTransaction) RETURN IF(_check <> BLANK(),"Exist","Does not exist")I'm doing the lookup of transaction and total value per transaction in actual table. This way transaction 303 does exist in actual.
