Forum Discussion

Frequent Visitor

2 years ago

Solved

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 tha...

Need Help

Ashish_Mathur
2 years ago
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.

bhanu_gautam

Super User

2 years ago

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