Create summarized table

[andersbq]

Hello all, 

I got two tables with order data (ecom/retail).  My goal is to create a summarized table (based on the email address) that looks like Table 3. Table 1 & 2 has a lot of columns, below are the matching ones. 

TABLE 1
Email	Name	Order ID	Product ID	Order date
john@doe.com	John Doe	1212	1010	2020-01-01
john@doe.com	John Doe	1212	1212	2020-01-01
Jane@doe.com	Jane Doe	1215	1202	2020-05-05
Jane@doe.com	Jane Doe	1215	1207	2020-05-05

TABLE 2
Email	Name	Order ID	Product ID	Order date
john@doe.com	John Doe	2020	1010	2020-04-01
john@doe.com	John Doe	2020	1212	2020-04-01
Jane@doe.com	Jane Doe	2040	1202	2020-06-05
Jane@doe.com	Jane Doe	2040	1203	2020-06-05

TABLE 3
Email	Name	First order	Latest order
john@doe.com	John Doe	2020-01-01	2020-04-01
Jane@doe.com	Jane Doe	2020-05-05	2020-06-05

Would really appreciate your help on this one!

Thanks!

[Anonymous · Accepted solution]

Hi @andersbq ,

You could do this in Query Editor with Merge, Append, Group by and Remove duplicates features.

Pbix as attached.

Best Regards,

Jay

[andersbq]

Awesome, works like a charm!

Thanks! @Anonymous 

[amitchandak]

@andersbq , refer if this can help

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Try like

summarize(
union (
selectcolumns(Table1,"Email",Table1[Email] ,"Name",Table1[Name],"Order ID",Table1[Order ID],"Product ID",Table1[Product ID],"Order date",Table1[Order date]),
selectcolumns(Table2,"Email	Name",Table2[Email] ,"Name",Table2[Name],"Order ID",Table2[Order ID],"Product ID",Table2[Product ID],"Order date",Table2[Order date])
), [Email],[Name],"First order" ,min([Order date]),"Latest order",max([Order date]))

[andersbq]

Thanks! @amitchandak - but I get this error message: Cannot identify the table that contains [Order date] column.