Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
Error Message:
Desired Output:
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.
Solved! Go to 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.
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.
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.
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.
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:
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.
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:
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |