Get list of distinct values from multiple tables?

[ericOnline]

Hello,

I have 4 tables of data, each with an "EMAIL" column. I'd like to create a new table of the unique Email addresses found across all tables. Then use this EMAIL_TABLE as a JOIN table. 

How is this done in PowerQuery?

Example:

Table 1:

EMAIL1	TBL1_VAL0	TBL1_VAL1	TBL1_VAL2
bob@bob.com	A	B	C

Table 2:

EMAIL2	TBL2_VAL0	TBL2_VAL1	TBL2_VAL2
sally@sally.com	X	Y	Z

Table 3:

EMAIL3	TBL1_VAL0	TBL1_VAL1	TBL1_VAL2
bill@bill.com	1	2	3

Table 4:

EMAIL4	TBL1_VAL0	TBL1_VAL1	TBL1_VAL2
bob@bob.com	Z	X	Y

EMAIL_TABLE:

EMAIL_DISTINCT

bob@bob.com

sally@sally.com

bill@bill.com

[ericOnline]

Thank you for the details. Yes, I want to do this in Power Query. What would that look like?

[MikeJohnsonZA · Accepted solution]

In PowerQuery, you could do something like this.

let
Source = List.Union({#"Table1"[Email1],#"Table2"[Email2],#"Table3"[Email3],#"Table4"[Email4]}),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[MikeJohnsonZA]

You would be able to use something like this.

Table =
DISTINCT (
UNION (
SELECTCOLUMNS (
'Table1',
"Email", [Email1]
),
SELECTCOLUMNS (
'Table2',
"Email", [Email2]
),
SELECTCOLUMNS (
'Table3',
"Email", [Email3]
),
SELECTCOLUMNS (
'Table4',
"Email", [Email4]
)
)
)

However, this sort of thing is better done in Power Query.