Comparing two sets of tables to validate each column

Question

Hi, I am looking to create an excel template to validate two sets of data.

I want to use power query to build this. Say I have two tables of data (primary and secondary). I want to pull all columns in the secondary data table against primary (and vice verse) and compare each column to see if there is a true or false.

I am using larger data set, but let me lay it out a sample data and how I want to build this below ( I can upload a spreadsheet if it is easy to understand).

Primary Data Table

Household ID	Household Name	Family	Province
A0349-A	John Doe	John Doe FAMILY	BC
A1940-B	Jane Doe	Jane Doe FAMILY	MB
A1940-A	Dan Winkler	Dan Winkler FAMILY	QC
A1965-A	Adam Santos	Adam Santos FAMILY	NL
A1483-A	David Shine	David Shine FAMILY	NB
A0146-A	William Turn	William Turn FAMILY	AB
A1356-A	Roy Gant	Roy Gant FAMILY	NB
A2028-A	Steve Rue	Steve Rue FAMILY	MB
A0723-A	Lila Wayne	Lila Wayne FAMILY	CB
A0066-A	Shane Waug	Shane Waug FAMILY	TB

Secondary Data
Household ID	Household Name	Family	Province
A0349-A	John Doe	John Doe FAMILY	BC
A1940-B	Jane Doe	Jane Doe FAMILY	MB
A1940-A	Dan Winkler	Dan Winkler FAMILY	QC
A1965-A	Adam Santos	Adam Santos FAMILY	NL
A1483-A	David Shine	David Shine FAMILY	NB
A0146-A	William Turn	William Turn FAMILY	AB
A1356-A	Roy Gant	Roy Gant FAMILY	NB
A2028-A	Steve Rue	Steve Rue FAMILY	MB
A0723-A	Lila Wayne	Lila Wayne FAMILY	CB
A0066-A	Shane Waug	Shane Waug FAMILY	TB
A0088-A	Tina Rush	Tina Rush FAMILY	AB
A0095-A	Roy Sack	Roy Sack FAMILY	AB

This is the way I want to pull the output: can somebody help me to build a template to pull the below?

Output ONE using power query
Primary Data				Pull Secondary Data using power query against primary data table	Validation
Household ID	Household Name	Family	Province	Household ID	Household Name	Family	Province	Household ID	Household Name	Family	Province
A0349-A	John Doe	John Doe FAMILY	BC	A0349-A	John Doe	John Doe FAMILY	BC	TRUE	TRUE	TRUE	TRUE
A1940-B	Jane Doe	Jane Doe FAMILY	MB	A1940-B	Jane Doe	Jane Doe FAMILY	MB	TRUE	TRUE	TRUE	TRUE
A1940-A	Dan Winkler	Dan Winkler FAMILY	QC	A1940-A	Dan Winkler	Dan Winkler FAMILY	QC	TRUE	TRUE	TRUE	TRUE
A1965-A	Adam Santos	Adam Santos FAMILY	NL	A1965-A	Adam Santos	Adam Santos FAMILY	NL	TRUE	TRUE	TRUE	TRUE
A1483-A	David Shine	David Shine FAMILY	NB	A1483-A	David Shine	David Shine FAMILY	NB	TRUE	TRUE	TRUE	TRUE
A0146-A	William Turn	William Turn FAMILY	AB	A0146-A	William Turn	William Turn FAMILY	AB	TRUE	TRUE	TRUE	TRUE
A1356-A	Roy Gant	Roy Gant FAMILY	NB	A1356-A	Roy Gant	Roy Gant FAMILY	NB	TRUE	TRUE	TRUE	TRUE
A2028-A	Steve Rue	Steve Rue FAMILY	MB	A2028-A	Steve Rue	Steve Rue FAMILY	MB	TRUE	TRUE	TRUE	TRUE
A0723-A	Lila Wayne	Lila Wayne FAMILY	CB	A0723-A	Lila Wayne	Lila Wayne FAMILY	CB	TRUE	TRUE	TRUE	TRUE
A0066-A	Shane Waug	Shane Waug FAMILY	TB	A0066-A	Shane Waug	Shane Waug FAMILY	TB	TRUE	TRUE	TRUE	TRUE

Output TWO using power query
Secondary Data			Pull Primary Data using power query against primary data table	Validation
Household ID	Household Name	Family	Province	Household ID	Household Name	Family	Province	Household ID	Household Name	Family	Province
A0349-A	John Doe	John Doe FAMILY	BC	A0349-A	John Doe	John Doe FAMILY	BC	TRUE	TRUE	TRUE	TRUE
A1940-B	Jane Doe	Jane Doe FAMILY	MB	A1940-B	Jane Doe	Jane Doe FAMILY	MB	TRUE	TRUE	TRUE	TRUE
A1940-A	Dan Winkler	Dan Winkler FAMILY	QC	A1940-A	Dan Winkler	Dan Winkler FAMILY	QC	TRUE	TRUE	TRUE	TRUE
A1965-A	Adam Santos	Adam Santos FAMILY	NL	A1965-A	Adam Santos	Adam Santos FAMILY	NL	TRUE	TRUE	TRUE	TRUE
A1483-A	David Shine	David Shine FAMILY	NB	A1483-A	David Shine	David Shine FAMILY	NB	TRUE	TRUE	TRUE	TRUE
A0146-A	William Turn	William Turn FAMILY	AB	A0146-A	William Turn	William Turn FAMILY	AB	TRUE	TRUE	TRUE	TRUE
A1356-A	Roy Gant	Roy Gant FAMILY	NB	A1356-A	Roy Gant	Roy Gant FAMILY	NB	TRUE	TRUE	TRUE	TRUE
A2028-A	Steve Rue	Steve Rue FAMILY	MB	A2028-A	Steve Rue	Steve Rue FAMILY	MB	TRUE	TRUE	TRUE	TRUE
A0723-A	Lila Wayne	Lila Wayne FAMILY	CB	A0723-A	Lila Wayne	Lila Wayne FAMILY	CB	TRUE	TRUE	TRUE	TRUE
A0066-A	Shane Waug	Shane Waug FAMILY	TB	A0066-A	Shane Waug	Shane Waug FAMILY	TB	TRUE	TRUE	TRUE	TRUE
A0088-A	Tina Rush	Tina Rush FAMILY	AB					FALSE	FALSE	FALSE	FALSE
A0095-A	Roy Sack	Roy Sack FAMILY	AB					FALSE	FALSE	FALSE	FALSE

leodisrandle · Answer

https://refer.crowdfireapp.com/bgchrvh2n3i5

Forum Discussion

Comparing two sets of tables to validate each column

1 Reply

Resources