Compare Data (Codes, Amounts, Other Details) From Two Data Sources

[AkshayManke]

Hi,

I have two excel files which are containing data File: 1.xslx & 2.xlsx. There are some fields are common for example: Name, Code, Type and Amount. There may be differnece in the amounts in each file for example 1.xslx for a specific user, the amount is 100 and in the 2.xlsx the amount may be 110. 

What i want is: i want to check the see where the difference is. i tried but somehow i am unable to get the desired results.

Below some snaps for reference. Help would be really appreciated.

Base Data Example.

Below the relationship.

The result i am getting.

Expected Results

Many Thanks in advance.!!!

Regards,

Akshay

[v-robertq-msft · Accepted solution]

Hi, @AkshayManke 

According to your description and sample picture, It’s hard to find out the true reason for the problem. But I think it’s related to the relationship between the two tables. Can you try to connect the two tables directly without the intermediate table? The foreign key can be [Code] or [Name] and the filter direction should be “Both”.

If you still have a problem after these tryings. Would you like to post some sample Excel file or pbix file(without sensitive data) so that we can find the original cause and help you to solve it in advance?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft · Accepted solution]

Hi, @AkshayManke 

According to your description and sample picture, It’s hard to find out the true reason for the problem. But I think it’s related to the relationship between the two tables. Can you try to connect the two tables directly without the intermediate table? The foreign key can be [Code] or [Name] and the filter direction should be “Both”.

If you still have a problem after these tryings. Would you like to post some sample Excel file or pbix file(without sensitive data) so that we can find the original cause and help you to solve it in advance?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AkshayManke]

@v-robertq-msft 

Hi Robert,

Thanks for the help on the issue. I did not get chance to test the case however thanks for marking that answer as acceptance. I will test and will get back to you with results.

Thanks again.!

Akshay

[AkshayManke]

@v-robertq-msft 

Hi Robert,

Thanks for suggesting one more option. I will try the same and will get back with the results. If needed, will share the pbix file for further analysis.

Thanks and Regards,

Akshay

[v-robertq-msft · Accepted solution]

Hi, @AkshayManke 

OK, just reply to me and share the sample pbix file if this solution doesn’t work.

If this solution works, you can consider accepting my reply as a solution.

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@AkshayManke , why code to Table 2 is many to 1, It should be one to many; correct that.

Afte that it should simply sum(Table1[Amount]) - Sum(Table2[Amount])

[AkshayManke]

@amitchandak 

Hi Amit,

Thanks for helping on the query. I tried the suggested steps by you but not getting the desired results. I have added the test case only for demonstration however the actual data is large and having different columns in the 1 and 2 files. The problem here is the values are not matching. I may be taking a wrong approach. could you please help further?