Excel Power Query: Merge Join on Date Range

user_3889954 0
Viewed 137

I am trying to do something very similar to your article here, but I cannot get it to work.

I have 2 tables: "XYZ Charges - PQ" and "Land Orders".

"XYZ Charges - PQ" includes 3 columns: "Land Account No.", "STDATE", and "STDATE - 7". "Land Orders" includes 2 columns: "Acct No." and "Order Date".

I need to merge/join "XYZ Charges - PQ" and "Land Orders" together. "Land Account No." need to match "Acct No." exactly, but "Order Date" needs to be between "STDATE" and "STDATE - 7".

In the Power Query Editor, I tried to add a custom column with the following: = Table.AddColumn(#"XYZ Charges - PQ", "Table.SelectRows()", (X)=> Table.SelectRows(#"Land Orders - Grouped", (L)=> L[#"Account No."]=X[#"Harland Account No."] and X[PSTDATE] >= L[#"Invoice Date"] and X[PSTDATE] <= L[#"Invoice Date"]))

But I either get null results or received this error: Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

What am I missing?

Thank you, Cin

powerquery excel
1 Answers

The formula you want is

= Table.SelectRows(#"XYZ Charges - PQ",(x) => [Order Date] >= x[#"STDATE - 7"] and [Order Date] <=x[#"STDATE"] and [#"Acct No."]=x[#"Land Account No."])

or in another format

#"Added Custom" = Table.AddColumn(#"ACH Charges - PQ", "Custom", each Table.SelectRows(#"XYZ Charges - PQ",(x) => [Order Date] >= x[#"STDATE - 7"] and [Order Date] <=x[#"STDATE"] and [#"Acct No."]=x[#"Land Account No."]))

full sample code for Land Orders

let  Source = Excel.CurrentWorkbook(){[Name="SourceTableLandOrders"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Acct No.", type text}, {"Order Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"XYZ Charges - PQ",(x) => [Order Date] >= x[#"STDATE - 7"] and [Order Date] <=x[#"STDATE"] and [#"Acct No."]=x[#"Land Account No."]))
in  #"Added Custom"

to attempt to speed this up, try using a Table.Buffer() around the #"XYZ Charges - PQ" step taking place before this step.

#"XYZ Charges - PQ" = Table.Buffer(..existing code...) ,
user_92642300
Related