Min/Max Date from an other table, based on multiple conditions

[Anonymous]

Hi

I'm trying to enrich a customer table based on their orders.

I have a table with the customers and I would like to add their first orders date for different stores

Table 1 : Orders

Order	Contact	Store	Date
A1	1	A	01/01/2022
B2	1	B	02/01/2022
B3	1	B	10/01/2022
A2	2	A	04/01/2022
B1	3	B	05/01/2022
B4	3	B	20/01/2022

Table 2 : Customers (with the info I'd like to get from orders)

Contact	Min_Date_A	Min_Date_B
1	01/01/2022	02/01/2022
2	04/01/2022
3		05/01/2022

I don't know how to start created my formula, and don't even know if I should do it on PowerQuery or in the Data View tab.

Anyone could help?

Thanks

[Anonymous]

Thanks

It works perfectly

[Ahmedx · Accepted solution]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczRCQAhDAPQXfIt0kZvgLpGcf81tHBCC8nXI3GHKRqidivabygkdnMs/rbCWG0kUylmseP7nHUXm/E+v2ozGdPnPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Contact = _t, Store = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Contact", Int64.Type}, {"Store", type text}, {"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Order"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Store]), "Store", "Date", List.Min)
in
    #"Pivoted Column"