Connection between customer table and orders table

[guy2233]

Hi there,
I am trying to figure it out how to associate between customer order date to customer address
this is the customer table:

Each customer has a number of records following an address update, distinguished by an update date.
this is the order table:

I would like to combine combine order table with the customer table to get the appropriate address for that order date.

the output sholud look like this:

Thanks in advanced.

[MFelix]

Hi @guy2233 ,

Do you want to have the information in a table visualization so that you can have a list of adresses or do you want to have the information in a table so you can use it in your dataset in a different way?

[guy2233]

Hi @MFelix ,

I would like to the the information in the Orders Table so I can use it on the data set.

[MFelix]

Hi @guy2233 ,

For this you need to create a custom function something similar to this:

(OrderDate as date, customerid as text) =>

let
    Source = Customers,
    #"Filtered Rows" = Table.SelectRows(Source, each [Dateupdated] <= OrderDate and [Customer ID] = customerid),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Dateupdated", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1)
in
    #"Kept First Rows"

This function gets the information from your customers table and then filter out the latest result based on the order date.

Then you just need to use it has a new column on your order table and expan the result for your needs:

In my example I only have city but you can expand all the columns.