Merge in PowerQuery using filters and nearest values.

[TStanifiorth]

Hello I have an issue, which to abstract looks a bit like this:

Table 1: Inventory

ID	Item	Value
1	TV	£30
2	TV	£40
3	TV	£90
4	Radio	£5
5	Radio	£10

Table2: InsuranceProduct

Name	ProdType	MaxValue
TVOne	TV	£50
TVTwo	TV	£100
RadioOne	Radio	£8
RadioTwo	Radio	£16

I want to merge these two queries so I can add a column to the Inventory table with the relevant insurance product name, i.e.:

ID	Item	Value	Insurance.Name
1	TV	£30	TVOne
2	TV	£40	TVOne
3	TV	£90	TVTwo
4	Radio	£5	RadioOne
5	Radio	£10	RadioTwo

I.e. in other words I want to look up a value in one table ([InsuranceProduct]"Name"), by matching on the value of one column ([Inventory]"Item" and [InsuranceProduct]"ProdType"), and finding the smallest value in [insuranceProduct]"MaxValue" that is greater than [Inventory]"Value"

I hope that makes sense!

[wdx223_Daniel]

=let a=Table.Group(InsuranceProduct,"ProdType",{"n",each Table.Sort(_,"MaxValue")}) in Table.AddColumn(Inventory,"InsuranceName",each let b=a{[ProdType=[Item]]}?[n]? in if b=null then null else Table.Skip(b,(x)=>x[MaxValue]<[Value]){0}?[Name]?)

[TStanifiorth]

This seems to throw a cyclic reference error?

[wdx223_Daniel]

it works well on my PC