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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
Microsoft Employee
Microsoft 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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