I am not sure if this is a DAX or Power Query matter.
I have a price file and a product file in an ERP system.
The price file has at least 4 columns.
Category / Sub-Category / Product Group / Product
For each combination there are several types of price e.g. Price1 , Price 2 etc.
The problem I have is that any of these 4 columns can be wild carded, so I don’t have to specify a price for every product in the product file.combination.
- Category1 / SubCategory1 / Product Group1 / Product £10.99
- Category1 / SubCategory1 / * / * £15.99
So any product in the product file that matches line 1 will be charged at £10.99 and any products that match line 2 will be £15.99.
My problem is the product file will always use the 4 columns. So when I am looking up a price, how do I determine the lookup key to use?
From what I can see I will have to try multiple combinations to find a match.
Is there an elegant way to do this?
I should have posted this to make my problem clearer.
Sorry.
Thank you.
