Reconciliation of data between two tables in Power...

SBC · ‎11-13-2022

Hi ,

How to compare to compare two tables data in one table,I have tried with power Query using full outer join by selecting two /three columns as a reference but some of the values are appearing as expected.

I have provided below sample data of two tables and expected output,please have a look.Let me know any one have worked such kind of scenario or ideas will be appreciated.

We need to achieve two condition by comparing two tables.

1.we need to compare each columns of the two tables data,if it is matched we need to show in separate column as “Yes/No”

2.We need to compare one column in Table1.Q2 with three columns in table2(tbl2.Q2.t1,tbl2.Q2.t2,tbl2.Q2.t3).If Table1.Q2 column values is existed in any of the three column of table2.Q2( tbl2.Q2.t1,tbl2.Q2.t2,tbl2.Q2.t3) then we need to display it as matched or else not matched .

Table1:

sell_ref	dept_ref	date	Q2	Q1
AZ90	A219	10/3/2020	100	68.558
AZ90	A219	10/3/2020	99.95	68.26
AZ90	A272	10/9/2020	24.43	17.41
BZ08		10/9/2012

Table2:

sell_ref	dept_ref	Q1	date	Q2.t1	Q2.t2	Q2.t3
AZ90	A276	10606.5	10/18/2020	15.47	1500	44
AZ90	A219	68.558	10/3/2020	10.25	28.46	100
AZ90	A219	68.26	10/3/2020	99.95	10.26	28.31
AZ90	A272	17.41	10/9/2020	27.21	24.43	72.43
BZ08	E183	82.114	10/9/2012	13.7	11.97	34.78

Output Expected:

tbl1.sell_ref

tbl2.sell_ref

Matched_sell_ref

tbl1.dept_ref

tbl2.dept_ref

Matched_dept_ref

tbl1.date

tbl2.date

Matched dates

tabl1.Q1

tabl2.Q1

Matched Q1

tbl1.Q2

tbl2.Q2.t1

tbl2.Q2.t2

tbl2.Q2.t3

Matched Q2

AZ90

Yes

A219

Yes

10/3/2020

Yes

68.558

Yes

100

10.25

28.46

100

Yes

AZ90

Yes

A219

Yes

10/3/2020

Yes

68.26

Yes

99.95

10.26

28.31

Yes

AZ90

Yes

A272

Yes

10/9/2020

Yes

17.41

Yes

24.43

27.21

24.43

72.43

Yes

AZ90

NO

A276

No

10/18/2020

No

10606.5

No

15.47

1500

44

No

BZ08

Yes

E183

No

10/9/2012

Yes

82.114

No

13.7

11.97

34.78

No

Thanks,

SBC

v-yalanwu-msft · ‎11-14-2022

Hi, @SBC ;

Do you mean to generate a new table in Power Query? , or create a table visual with measures?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

How to get your questions answered quickly -- How to provide sample data