Data Reconciliation between two tables in Power BI

[SBC]

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

AZ90

Yes

A219

A219

Yes

10/3/2020

10/3/2020

Yes

68.558

68.558

Yes

100

10.25

28.46

100

Yes

AZ90

AZ90

Yes

A219

A219

Yes

10/3/2020

10/3/2020

Yes

68.26

68.26

Yes

99.95

99.95

10.26

28.31

Yes

AZ90

AZ90

Yes

A272

A272

Yes

10/9/2020

10/9/2020

Yes

17.41

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

BZ08

Yes

E183

No

10/9/2012

10/9/2012

Yes

82.114

No

13.7

11.97

34.78

No

Thanks,

SBC

[v-rongtiep-msft]

Hi @SBC ,

Please have a try.

let
    Source = Table.NestedJoin(table1, {"sell_ref", "date", "Q1"}, table2, {"sell_ref", "date", "Q1"}, "table2", JoinKind.RightOuter),
    #"Expanded table2" = Table.ExpandTableColumn(Source, "table2", {"sell_ref", "dept_ref", "Q1", "date", "Q2t1", "q2t2", "q2t3", "Index"}, {"table2.sell_ref", "table2.dept_ref", "table2.Q1", "table2.date", "table2.Q2t1", "table2.q2t2", "table2.q2t3", "table2.Index"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded table2", {"table2.Q1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Duplicates",{"sell_ref", "table2.sell_ref", "dept_ref", "table2.dept_ref", "date", "table2.date", "Q2", "Q1", "table2.Q1", "table2.Q2t1", "table2.q2t2", "table2.q2t3", "table2.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"table2.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [sell_ref] = [table2.sell_ref] then "Yes" else "No"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "sell"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"sell_ref", "table2.sell_ref", "sell", "dept_ref", "table2.dept_ref", "date", "table2.date", "Q2", "Q1", "table2.Q1", "table2.Q2t1", "table2.q2t2", "table2.q2t3"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Reordered Columns1", "Custom", each if [dept_ref] = [table2.dept_ref] then "Yes" else "No"),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "dept"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"sell_ref", "table2.sell_ref", "sell", "dept_ref", "table2.dept_ref", "dept", "date", "table2.date", "Q2", "Q1", "table2.Q1", "table2.Q2t1", "table2.q2t2", "table2.q2t3"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Reordered Columns2", "dateCustom", each if [date] = [table2.date] then "Yes" else "No"),
    #"Reordered Columns3" = Table.ReorderColumns(#"Added Conditional Column2",{"sell_ref", "table2.sell_ref", "sell", "dept_ref", "table2.dept_ref", "dept", "date", "table2.date", "dateCustom", "Q1", "table2.Q1", "Q2", "table2.Q2t1", "table2.q2t2", "table2.q2t3"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Reordered Columns3", "Custom", each if [Q1] = [table2.Q1] then "Yes" else "No"),
    #"Reordered Columns4" = Table.ReorderColumns(#"Added Conditional Column3",{"sell_ref", "table2.sell_ref", "sell", "dept_ref", "table2.dept_ref", "dept", "date", "table2.date", "dateCustom", "Q1", "table2.Q1", "Custom", "Q2", "table2.Q2t1", "table2.q2t2", "table2.q2t3"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns4",{{"Custom", "Q1Cu"}}),
    #"Added Conditional Column4" = Table.AddColumn(#"Renamed Columns2", "Custom", each if [Q2] = [table2.Q2t1] then "Yes" else if [Q2] = [table2.q2t2] then "Yes" else if [Q2] = [table2.q2t3] then "Yes" else "No")
in
    #"Added Conditional Column4"

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SBC]

Hi @v-rongtiep-msft ,

Thanks for reply,even in right out join dint meet our requirement,"BZ08" value is existed in table1 but it is showing Blank value and when we apply conditional column it is displaying the "NO"

Output we are expecting:

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

AZ90

Yes

A219

A219

Yes

10/3/2020

10/3/2020

Yes

68.558

68.558

Yes

100

10.25

28.46

100

Yes

AZ90

AZ90

Yes

A219

A219

Yes

10/3/2020

10/3/2020

Yes

68.26

68.26

Yes

99.95

99.95

10.26

28.31

Yes

AZ90

AZ90

Yes

A272

A272

Yes

10/9/2020

10/9/2020

Yes

17.41

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

BZ08

Yes

E183

No

10/9/2012

10/9/2012

Yes

82.114

No

13.7

11.97

34.78

No

Thanks,

SBC

[v-rongtiep-msft · Accepted solution]

Hi @SBC ,

Please refer to my pbix file to see if it helps you.

let
    Source = Table.NestedJoin(table1, {"sell_ref", "date"}, table2, {"sell_ref", "date"}, "table2", JoinKind.RightOuter),
    #"Expanded table2" = Table.ExpandTableColumn(Source, "table2", {"sell_ref", "dept_ref", "Q1", "date", "Q2t1", "q2t2", "q2t3"}, {"sell_ref.1", "dept_ref.1", "Q1.1", "date.1", "Q2t1", "q2t2", "q2t3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded table2",{"sell_ref", "dept_ref", "date", "Q2", "Q1", "Q1.1", "sell_ref.1", "dept_ref.1", "date.1", "Q2t1", "q2t2", "q2t3"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Custom", each if [Q1] = [Q1.1] or [Q1] = null then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows",{"sell_ref", "sell_ref.1", "dept_ref", "dept_ref.1", "date", "date.1", "Q1", "Q1.1", "Q2", "Q2t1", "q2t2", "q2t3", "Custom"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Reordered Columns1", "sell", each if [sell_ref] = [sell_ref.1] then "Yes" else "No"),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Conditional Column1",{"sell_ref", "sell_ref.1", "sell", "dept_ref", "dept_ref.1", "date", "date.1", "Q1", "Q1.1", "Q2", "Q2t1", "q2t2", "q2t3", "Custom"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Reordered Columns2", "dept", each if [dept_ref] = [dept_ref.1] then "Yes" else "No"),
    #"Reordered Columns3" = Table.ReorderColumns(#"Added Conditional Column2",{"sell_ref", "sell_ref.1", "sell", "dept_ref", "dept_ref.1", "dept", "date", "date.1", "Q1", "Q1.1", "Q2", "Q2t1", "q2t2", "q2t3", "Custom"}),
    #"Added Conditional Column3" = Table.AddColumn(#"Reordered Columns3", "datecust", each if [date] = [date.1] then "Yes" else "No"),
    #"Reordered Columns4" = Table.ReorderColumns(#"Added Conditional Column3",{"sell_ref", "sell_ref.1", "sell", "dept_ref", "dept_ref.1", "dept", "date", "date.1", "datecust", "Q1", "Q1.1", "Q2", "Q2t1", "q2t2", "q2t3", "Custom"}),
    #"Added Conditional Column4" = Table.AddColumn(#"Reordered Columns4", "Q11", each if [Q1] = [Q1.1] then "Yes" else "No"),
    #"Reordered Columns5" = Table.ReorderColumns(#"Added Conditional Column4",{"sell_ref", "sell_ref.1", "sell", "dept_ref", "dept_ref.1", "dept", "date", "date.1", "datecust", "Q1", "Q1.1", "Q11", "Q2", "Q2t1", "q2t2", "q2t3", "Custom"}),
    #"Added Conditional Column5" = Table.AddColumn(#"Reordered Columns5", "Q22", each if [Q2] = [Q2t1] then "Yes" else if [Q2] = [q2t2] then "Yes" else if [Q2] = [q2t3] then "Yes" else "No")
in
    #"Added Conditional Column5"

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.