Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
=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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
9 | |
8 | |
7 |
User | Count |
---|---|
17 | |
10 | |
7 | |
6 | |
6 |