Forum Discussion

JemmaD's avatar
JemmaD
Helper V
2 years ago
Solved

Append distinct selected columns in PQ

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!

Need Help
  • BIstvan's avatar
    BIstvan
    2 years ago

    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.

5 Replies

  • BIstvan's avatar
    BIstvan
    Resolver I
    2 years ago

    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's avatar
      JemmaD
      Helper V
      2 years ago

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

      • BIstvan's avatar
        BIstvan
        Resolver I
        2 years ago

        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.