cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft

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 III
Super User III

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
Microsoft

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/

View solution in original post

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors