Request: How to make LOOKUPVALUE formula retrieve from multiple result columns

Question

Hello,&nbsp;I have a data table containing information on the sale of beauty products. Each row is an individual transaction record, but does not include the price paid by the customer. It does include the loyalty tier of the customer.&nbsp;&nbsp;In a separate lookup table, I have a list of prices based product type&nbsp;and loyalty tier (different loyalty tiers pay different amounts per product).&nbsp;How can I easily map this price information into my data table? I have tried different combinations of LOOKUPVALUE and RELATED but can't get it to work given my lookup table has values across multiple columns:ProductPlatinumGoldSilverBronzeBlush$35$40$45$50Mascara$30$35$38$40Foundation$70$80$90$100Fake eyelashes$14$16$18$20Lipstick$35$40$45$50&nbsp;Transaction IDSale DateProductQuantityLoyalty TierCost11-JanBlush1Platinum??21-JanBlush4Bronze??31-JanMascara1Bronze??41-JanLipstick2Gold??&nbsp;Any help would be much appreciated!&nbsp;Thank you&nbsp;I am trying (unsucessfully) to use LOOKUPVALUE to

xifeng_l · Accepted Answer

Hi&nbsp;PBI12345&nbsp;,&nbsp;First, you need to convert the price lookup table into a one-dimensional table format in PowerQuery.&nbsp;&nbsp;Then, we can use the LOOKUPVALUE function to match the price in Sales table.&nbsp;&nbsp;&nbsp;Demo - How to make LOOKUPVALUE formula retrieve from multiple result columns.pbix&nbsp;&nbsp;Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !&nbsp;Thank you~&nbsp;

Forum Discussion

Request: How to make LOOKUPVALUE formula retrieve from multiple result columns

2 Replies

Related content

lookupvalue

Retrieving data via HTML

lookupvalue PBI

LookupValue with Filter

Cannot retrieve Contacts from API

Product	Platinum	Gold	Silver	Bronze
Blush	$35	$40	$45	$50
Mascara	$30	$35	$38	$40
Foundation	$70	$80	$90	$100
Fake eyelashes	$14	$16	$18	$20
Lipstick	$35	$40	$45	$50

Transaction ID	Sale Date	Product	Quantity	Loyalty Tier	Cost
1	1-Jan	Blush	1	Platinum	??
2	1-Jan	Blush	4	Bronze	??
3	1-Jan	Mascara	1	Bronze	??
4	1-Jan	Lipstick	2	Gold	??