Forum Discussion
Merging records from two tables
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.
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.
5 Replies
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
AddVarianceAnd I have done it with sample date please find the attached PBIX
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.
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.
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
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
