Forum Discussion
looking up values where two ranges apply
Another option is to start with the Orders table, merge in the Customer table (matching on the Customer column), expand the date and unique reference columns, and then filter out the rows that don't satisfy the time overlap.
Here's some example code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbK0MDcFUib6hvpGBkZGQKYZjBmrA1ViaWgOpMz1TWBKTPVNoUpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Order = _t, #"Start Date" = _t, #"Order End" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Order", Int64.Type}, {"Start Date", type date}, {"Order End", type date}}, "en-IN"), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Customer"}, Customers, {"Customer"}, "Customers", JoinKind.LeftOuter), #"Expanded Customers" = Table.ExpandTableColumn(#"Merged Queries", "Customers", {"Start Date", "End Date", "Unique Reference"}, {"Customers.Start Date", "Customers.End Date", "Customers.Unique Reference"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Customers", each ([Customers.Start Date] <= [Order End] and [Customers.End Date] >= [Start Date])) in #"Filtered Rows"
Hi Anonymous
You can do this in Power Query my modifying the tables. You might create tables that aren't loaded but are just used in a merge for this purpose. My file is here - I did this in Excel for simplicity's sake.
What I did:
In each table I created a DateRange field that is a list of the dates from the StartDate to EndDate. It is actually numeric, but you convert to dates in a second:
You then hit the double-arrows in the upper right of that new column, Expand into Rows, then change the type to date. You'll get this:
Do that with both tables, then from the order table, merge it with the customer table as shown, using 2 columns:
Do not expand the merged "Customers" column. Instead, add a custom column with this formula:
if Table.RowCount([Customers]) > 0 then [Customers][Status]{0} else null
If the merged Customers column has more than 1 record, this will get the Status value of the first record, which is Green in this case.
At this point, keep only the columns you want (get rid of the Customers and DateRange for example), then use Table.Distinct (remove duplicates on the whole table) and you get this:
The order table can be brought in as is. The Customer table would need a copy of the original before all of this work. You could duplicate it and remove all of the steps I added, or create a reference to it to create this expanded table, etc. Many ways to approach it.
Hi,
thanks so much for your reply. I am trying to replicate this, but adding the first column I get
Expression.Error: The name 'Changed Type' wasn't recognized. Make sure it's spelled correctly
any idea why this is?
#"Changed Type" refers to the previous step. Change it to whatever the name of your previous step is.