Append distinct selected columns in PQ

[JemmaD]

Hi there,

I have four tables, all of which hold two common columns called [Mailbox] and [Business Type]

I want to append the four tables with distinct values of these two columns

I tried bringing just these two columns in and doing a table.distinct but it's not lining the mailbox up with the right business type - it seems to have brought them in independently of one another, so I ended up appending the tables completely and then keeping the two columns but this makes the table very large to load, so I want to try again doing a distinct select of the two columns but keeping them lined up with one another.

Help!

[BIstvan]

@JemmaD  Instead of PQ you can try to do it with DAX.

Try the below example.

APPENDED TABLES

VAR Table1 =DISTINCT(
SELECTCOLUMNS(
Table1,
'Table1'[Mailbox],
'Table1'[Business Type])

VAR Table2 =DISTINCT(
SELECTCOLUMNS(
Table2,
'Table2'[Mailbox],
'Table2'[Business Type])

VAR Table3 =DISTINCT(
SELECTCOLUMNS(
Table3,
'Table3'[Mailbox],
'Table3'[Business Type])

VAR Table4 =DISTINCT(
SELECTCOLUMNS(
Table4,
'Table4'[Mailbox],
'Table4'[Business Type])

RETURN
DISTINCT(UNION(Table1,Table2,Table3,Table4))

[JemmaD]

I need to do it in Power Query as dax will create circular dependency 

[BIstvan · Accepted solution]

I suppose you need it for a dimension table. have you tried to duplicate the 4 tables. Disable the load on the duplicates. Leave just the 2 common columns. Then select Both columns and remove duplicates. Do it for all 4 duplicates same steps. Once done append them toghether. And on the appended table do again a duplicate removal on both columns.

[JemmaD]

Ah yes good shout!

[BIstvan]

@JemmaD  let a response if it worked 🙂