Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Manas1415
Frequent Visitor

Using LOOKUPVALUE in IFERROR producing an error

Hi all,

I am new to Power BI and I'm stuck in a particular scenario where I want to derive a Column Value from a MappingTable using Column_1 or Column_2 as Column references and display the corresponding values of the ResultColumn against a SearchColumn from my main table.

 

I am trying to populate my MainTable with a lookup value from a MappingTable, but I am trying to use two different reference columns from the MappingTable. I tried using the same approach as MS-Excel by using two lookup functions inside an IFERROR function.

 

Formula in Excel:

=IFERROR(VLOOKUP([@Task Name],'MappingTable'!A:G,7,0),VLOOKUP([@Task Name],'MappingTable'!B:G,6,0))

which works absolutely fine in MS-Excel.

 

Formula I'm trying to use in Power BI:

NewColumn(LookUp Function) =

                  IFERROR(
                        LOOKUPVALUE('MappingTable'[ResultColumn],'Task Categories'[Column_1],'MainTable'[Task Name]),
                        LOOKUPVALUE('MappingTable'[ResultColumn],'Task Categories'[Column_2],'MainTable'[Task Name])
                  )

This Expression gives the following Error Message: <A Table of Multiple values was supplied where a single value was expected.>

 

 

Note: For all the null values in MappingTable[Column_1], there exists a value in MappingTable[Column_2] which I want to use as the reference column if the Column_1 is NULL to display the Output from the ResultColumn in both the cases.

 

Sample Structure of my Tables:

 

My MappingTable looks like this:

Image1.jpg

 

So basically, I'm performing a lookup to fetch the value from the ResultColumn using Column_1 as reference, where if there is a NULL value, I want to use the Column_2 as reference and fetch the corresponding value from the ResultColumn.

 

MainTable:

Image3.jpg

 

Error Message:

Image2.jpg

 

Desired Output:

Image4.jpg

 

P.S: Please refrain from suggesting to merge the Column_1 and Column_2 in the first place because I just can't, I need to find out if there's anyway possible to achieve the desired result?

 

Any kind of input is appreciated. 

 

 

 

1 ACCEPTED SOLUTION

Hi,

 

This is the calculted column formula i used in Table2

 

=IF(ISBLANK(LOOKUPVALUE(mappingtable[Result],mappingtable[Column_1],[Lookup_value])),LOOKUPVALUE(mappingtable[Result],mappingtable[Column_2],[Lookup_value]),LOOKUPVALUE(mappingtable[Result],mappingtable[Column_1],[Lookup_value]))

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
Manohar30
Employee
Employee

Hi Team,

I am new to Power BI and I'm stuck in a particular scenario where I want to convert this Vlookup in excel formula to Power Bi.

Excel vlookup formula: =IFERROR(VLOOKUP([@Team],NTEbyTeam3[[Org]:[FY20NTE]],2,FALSE)/IF([@[Approved Ind]]="N",0,IF([@[Budget]]=-1,0,SUMIFS([TotalPo],[Team],[@Team],[Budget],0))),0)

 

In power Bi i need to convert this expression. Any help is appriciated. 

 

Thank you,

Manohar.

Hi,

Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

I cannot identify any mistake in your formula.  It looks like for there are duplicate values in Column_1 and/or Column_2 for the same task_name.  Please confirm if that is the case.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur,

 

Thanks for reaching out.

 

The values in both the Column_1 and Column_2 are unique.

 

Hi,

 

Have you checked my most recent post.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-huizhn-msft
Employee
Employee

Hi @Manas1415,

>> which I want to use as the reference column if the Column_1 is NULL to display the Output from the ResultColumn in both the cases.

What's the meaning of both cases? From your formula, ResultColumn only exist 'MappingTable' table. 

In addition, it's hard to reproduce your scenario without sample table, please share your sample table for further analysis.

Best Regards,
Angelia

Hi @v-huizhn-msft,

 

First of all, thanks for reaching out.

 

My MappingTable looks like this:

New Bitmap Image.jpg

 

So basically, I'm performing a lookup to fetch the value from the ResultColumn using Column_1 as reference, where if there is a NULL value, I want to use the Column_2 as reference and fetch the corresponding value from the ResultColumn.

 

Hope this clears what I'm trying to ask. 🙂

 

Hi,

 

This is the calculted column formula i used in Table2

 

=IF(ISBLANK(LOOKUPVALUE(mappingtable[Result],mappingtable[Column_1],[Lookup_value])),LOOKUPVALUE(mappingtable[Result],mappingtable[Column_2],[Lookup_value]),LOOKUPVALUE(mappingtable[Result],mappingtable[Column_1],[Lookup_value]))

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur,

 

I understand the logic that you used while building the expression but sadly, your expression too gives the same error.

I'm attaching a screenshot of the same:

 

1111.jpg

 

Where,

Task Categories -> MappingTable

Data Edit -> MainTable

Category -> ResultColumn

TaskName -> LookUp Value Column (Search Value)

 

I don't understand what's going wrong here. 

Thanks for looking into this.

Hi,

 

You are not sharing the complete information.  You only shared one table initially whereas you have more than 1 table.  Share the link from where i can download the file and state the requirements clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

I have TWO tables:

1. MainTable

2. MappingTable

 

I want to fetch a value from the MappingTable and display it on the MainTable using a conditional lookup.

I've also edited the main post along with sample table structures.

 

Please see if it helps.

Hi,

 

My post about an hour ago should solve the problem.  If it does not, then share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.