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_refdept_refdateQ2Q1AZ90A21910/3/2020 10068.558AZ90A21910/3/2020 99.9568.26AZ90A27210/9/2020 24.4317.41BZ08 10/9/2012 Table2:sell_refdept_refQ1dateQ2.t1Q2.t2Q2.t3AZ90A27610606.5 10/18/2020 15.47150044AZ90A21968.55810/3/202010.2528.46100AZ90A21968.2610/3/202099.9510.2628.31AZ90A27217.4110/9/2020 27.2124.4372.43BZ08E18382.114 10/9/201213.711.9734.78 Output Expected:tbl1.sell_reftbl2.sell_refMatched_sell_reftbl1.dept_reftbl2.dept_refMatched_dept_reftbl1.datetbl2.dateMatched datestabl1.Q1tabl2.Q1Matched Q1tbl1.Q2tbl2.Q2.t1tbl2.Q2.t2tbl2.Q2.t3Matched Q2AZ90AZ90YesA219A219Yes10/3/202010/3/2020Yes68.55868.558Yes10010.2528.46100YesAZ90AZ90YesA219A219Yes10/3/202010/3/2020Yes68.2668.26Yes99.95 99.9510.2628.31YesAZ90AZ90YesA272A272Yes10/9/202010/9/2020Yes17.41 17.41 Yes24.4327.2124.4372.43Yes AZ90NO A276No 10/18/2020 No 10606.5 No 15.47150044NoBZ08BZ08Yes E183No10/9/2012 10/9/2012Yes 82.114No 13.711.9734.78No Thanks,SBC

Forum Discussion

SBC

Helper III

3 years ago

Solved

Data Reconciliation between two tables in Power BI

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

A276

10/18/2020

10606.5

15.47

1500

BZ08

Yes

E183

10/9/2012

Yes

82.114

13.7

11.97

34.78

Thanks,

SBC

DAX

Help appreciated

Anonymous

3 years ago

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.

Data Reconciliation between two tables in Power BI.pbix127 KB

3 Replies

Anonymous

Not applicable

3 years ago

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.

Data Reconciliation between two tables in Power BI.pbix115 KB

SBC

Helper III

3 years ago

Hi Anonymous ,

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

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

A276

10/18/2020

10606.5

15.47

1500

BZ08

Yes

E183

10/9/2012

Yes

82.114

13.7

11.97

34.78

Thanks,

SBC

Anonymous

Not applicable

3 years ago

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.

Data Reconciliation between two tables in Power BI.pbix127 KB

Forum Discussion

Data Reconciliation between two tables in Power BI

3 Replies

Related content

Numbers reconciliation in PowerQuery with M language

Best way to do reconciliation of tables, row, primary key, column counts without Notebook

Building a transactional reconciliation in power bi desktop

Matrix table

Tables combine