Forum Discussion
Merge in PowerQuery using filters and nearest values.
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!
3 Replies
=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]?)
This seems to throw a cyclic reference error?
it works well on my PC
