Power Query Multiple Criteria with Dates

[bg]

Hi all, 

I am looking to merge two universes, "Data" and "Contracts" and return the "Contract ID" in a new column if:.

                            1.                                                               2.                                                               3. 

Unique ID Data = Unique ID Contracts & Recieved Date> = Contract Start Date & Recieved Date <= Contract End Date

I believe that this will have to be done using the Advanced Editor, but have never used M and have not been able to deciefer what to adjust when i look at other code online. 

Can anyone help walk me through how i would be able to return the "Contract ID" based on those conditions?

Thanks!

BG

[bg]

Hi @MarcelBeug

My apologies, i a mistake in the original post. I am looking to pull in the Contract ID into the Data if the unique ID's match and the Recieved Date is greater than or equal to the start date and less than or equal to the end date. I will adjust it in my original post. 

Unique ID Data = Unique ID Contracts & Recieved Date> = Contract Start Date & Recieved Date <= Contract End Date

[MarcelBeug]

In case of approximate matches, it is recommended to first buffer the lookup table (ic. the Contracts table).

I added "Value.Type(Contracts)" as type for the new column, so the expanded value will be correctly typed.

let
    Source = Data,
    ContractsBuffered = Table.Buffer(Contracts),
    #"Added Custom" = Table.AddColumn(Source, "Contracts", (Data) => Table.SelectRows(ContractsBuffered, each Data[ID] = [ID] and Data[Recieved Date] >= [Start Date] and Data[Recieved Date] <= [End Date]), Value.Type(Contracts)),
    #"Expanded Contracts" = Table.ExpandTableColumn(#"Added Custom", "Contracts", {"ID"}, {"Contracts.ID"})
in
    #"Expanded Contracts"

[bg]

Thank you - i think my only issue now would be how to implement that into power query, whick i dont fully understand yet. 

Would I merge the "Data" and "Contracts" queries together based on the Unique ID field, and then edit the code to look like the above in the advanced editor?

This is my first day ever using power query/M in any capacity, so i am still learning the ins and outs. Thanks for your help!

[MarcelBeug · Accepted solution]

No, the "merge" is already part of te code.

You can create a new blank query, go into the advanced editor and replace the default code by the code I provided in my previous post. Rename the query to the desired name of the resulting table.

As a prerequisite, you must have your Data and Contracts tables already loaded in Power Query.

Example query for loading the Excel Data table into Power Query (current workbook):

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Recieved Date", type date}})
in
    #"Changed Type"

This code was created by using "From Table" on the Data tab in Excel; the generated code was adjusted for the date type of "Recieved Date" (it was defaulted to datetime type). Output was loaded as "Connection Only". 

You may need to adjust any table/column names according to your actual table/column names. 

[MarcelBeug]

If I understand correctly, you want to add the Contracts ID to the "Data" table if the conditions are met (which includes that you will only get ID's if contract start date = contract end date, which looks strange to me).

Generated code (I added the last step after video recording):

let
    Source = Data,
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Recieved Date", "Recieved Date - Copy"),
    #"Merged Queries" = Table.NestedJoin(#"Duplicated Column",{"ID", "Recieved Date", "Recieved Date - Copy"},Contracts,{"ID", "Start Date", "End Date"},"Contracts",JoinKind.LeftOuter),
    #"Expanded Contracts" = Table.ExpandTableColumn(#"Merged Queries", "Contracts", {"ID"}, {"Contracts.ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Contracts",{"Recieved Date - Copy"})
in
    #"Removed Columns"