Merging records from two tables

[AhmadJamil]

Hi,

I am sure this question has been asked before and must have a solution, nevertheless, I am struggling so asking for help. I have two tables, like following, and want to merge them in a way that I get records from both sides and where the rows match I need them on one row and where no match the amount should be left blank/zero as MergedTable below:

Table1
ID1	ID2	ID3	Amount
1	2	1	20
1	2	2	30
2	4	1	40

Table2
ID1	ID2	ID3	Amount
1	2	1	30
1	2	3	15
1	3	4	50
3	5	2	35

MergedTable
ID1	ID2	ID3	Table1_Amount	Table2_Amount	Variance
1	2	1	20	30	-10
1	2	2	30		30
1	2	3		15	-15
1	3	4		50	-50
1	4	1	40		40
1	5	2		35	-35

Just to add that I tried Megeing Queries but couldn't find the desired result.

[v-kongfanf-msft]

Hi @AhmadJamil ,

Did @Ashish_Mathur  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[Ashish_Mathur · Accepted solution]

Hi,

Rename the amount column to Amount 1 in Table2.  Append both tables in the Query Editor to get 5 columns.  In your matrix visual, drag ID1, ID2 and ID3 columns.  Write these measures

Measure = sum(Data[Amount])

Measure1 = sum(Data[Amount1])

Variance = [Measure]-[Measure1]

Hope this helps.

[AhmadJamil]

Hi Ashih, 
Thank you for your response. The technique seems working fine with the sample data, I will apply it on the actual tables and will let you know if I need more help.

Thanks
Ahmad

[bhanu_gautam]

@AhmadJamil , You can do it using Power Query

let
// Load Table1
Source1 = Table1,
// Load Table2
Source2 = Table2,
// Merge Tables
MergedTables = Table.NestedJoin(Source1, {"ID1", "ID2", "ID3"}, Source2, {"ID1", "ID2", "ID3"}, "Table2", JoinKind.FullOuter),
// Expand Merged Table
ExpandedTable = Table.ExpandTableColumn(MergedTables, "Table2", {"Amount"}, {"Table2_Amount"}),
// Rename Columns
RenamedColumns = Table.RenameColumns(ExpandedTable, {{"Amount", "Table1_Amount"}}),
// Replace Nulls with 0
ReplaceNulls1 = Table.ReplaceValue(RenamedColumns, null, 0, Replacer.ReplaceValue, {"Table1_Amount"}),
ReplaceNulls2 = Table.ReplaceValue(ReplaceNulls1, null, 0, Replacer.ReplaceValue, {"Table2_Amount"}),
// Add Variance Column
AddVariance = Table.AddColumn(ReplaceNulls2, "Variance", each [Table1_Amount] - [Table2_Amount])
in
AddVariance

And I have done it with sample date please find the attached PBIX

[AhmadJamil]

Hi Bhanu_gautam, 
Thank you for the response. Following is the merged table in your file:

I was having the same issue that the IDs for one of the table go blank and that creates orphan records in relationships.