Forum Discussion

FabMann's avatar
FabMann
Frequent Visitor
3 years ago

Power Query: Create a table from two different tables

Hello, I have two different tables (Table A, and Table B) with these fields

Table A

DateUser Id

 

Table B

Dateemail

 

I would like to create a third table (Table C) on Power Query with these fields

DateCount idCount email

 

I'm not really good with power Query so I can't get it how to do it.

I am using Table.SelectColumns() but in this way I can only select one table and some columns from that table. I should need a sort of a join between the two tables and then a distinct count.

Can anyone help me?

Thank you very much

Need Help

1 Reply

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    Try this steps:

    Import Table A (mySample)

     

    Import Table B (mySample)

     

    New Source > Blank Query

    Create a new table with combination of Dates from Table A and Table B

    = Table.Combine({Table.SelectColumns(#"Table A",{"Date"}),Table.SelectColumns(#"Table B",{"Date"})})

     

    = Table.Distinct(Source)

    We want only unique values, so use Distinct.

     

    Merge Table A and Table B on Date column.

     

    Expand Table A ussing Aggregate "Count (Not Blank) of User ID"

     

    Expand Table B using Aggregate "Count of Email"

     

    FINAL RESULT:

     

    PBIX FILE: https://we.tl/t-ysyNlMBzuu