Power Query: Get Change Control

masterswordz

New member
Joined
Apr 28, 2020
Messages
22
Reaction score
0
Points
0
Location
Philippines
Excel Version(s)
2016
Hi

May I get some help how to get the output as shown in TABLE3 using Query Editor please?

Unique Key: Column1+Column2+Column4

TABLE1



Column1Column2Column3Column4
data1ver1ds1cp1
data1ver1ds2cp1
data1ver1ds3cp1
data2ver1ds1cp1

TABLE2



Column1Column2Column3Column4
data1ver1ds1cp1
data1ver1ds2cp1
data2ver1ds2cp1
data2ver1ds3cp1
data2ver1ds4cp1

TABLE3




Column1Column2Column3Column4Column 5
data1ver1ds1cp1no change
data1ver1ds2cp1no change
data1ver1ds3cp1deleted
data2ver1ds2cp1modified
data2ver1ds3cp1add
data2ver1ds4cp1add
 

Attachments

  • ChangeControl.xlsx
    16.2 KB · Views: 13
This is the closest I can get by merging the tables on three different join types and then appending them together:

Excel 2016 (Windows) 32 bit
A
B
C
D
E
27
Column1Column2Column3Column4Custom
28
data1ver1ds1cp1no change
29
data1ver1ds2cp1no change
30
data1ver1ds3cp1modified or deleted
31
data2ver1ds1cp1modified or deleted
32
data2ver1ds2cp1modified or add
33
data2ver1ds3cp1modified or add
34
data2ver1ds4cp1modified or add
Sheet: Sheet1

Hope it helps.
 

Attachments

  • PQ Comparing Tables AliGW.xlsx
    21.4 KB · Views: 10
Thanks for trying AliGW. :smile:
But I guess this is more complicated than it seems. I might consider using VBA solution for this.
 
Back
Top