Create a summarized table from two different tables/files

[mitapate]

I have two files which contains transaction data coming from two different applications. 

Table 1 

Transaction Date

Order ID

A_Order Value

Debit/Credit

SourceName

Table 2 

Transaction Date

Order No.

B_Value

Source Name

I want to create a summarized table wherein both these tables are compared and difference can be displayed and alos categorized. 

Summarized Table 

Transaction Date	Date from Table 1
Order ID	From Table 1 (same as Order no. in Table2)
Count of A_Orders	Count of Order ID in Table 1
Value of A_Orders	Sum of Orders with same Order ID in Table 1
Count of B_Orders	Count of Order No in Table 2
Value of B_Orders	Sum of Orders with same Order no in Table 2
Difference	Difference in Value of A_orders and B_orders
Category	IF diff =0 then no issue ELSE duplicate entries

Can anyone help on how this can be implemented directly in Power BI ? Especially on creating this kind of summarized table. 

[v-angzheng-msft]

Hi, @mitapate 

Could you share some dummy data and post expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here. It makes it easier to give you a solution.

Best Regards,
Community Support Team _ Zeon Zheng

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

[amitchandak]

@mitapate , You should create two common dimensions Order (Order ID, Order number) 

Source( Source, Source)

Order =distinct(union(distinct(Table1[Order ID]),distinct(Table2[Order No])))

Source =

distinct(union(distinct(location1[Source ]),distinct(location2[Source ])))

Join them with both tables and then you can use them to analyze data together

Else append these two table in power query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query