Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bg
New Member

Power Query Multiple Criteria with Dates

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

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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. 

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
bg
New Member

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
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

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
Community Champion
Community Champion

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. 

Specializing in Power Query Formula Language (M)
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.