Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
We have a requirement where we have to do a lookup .It was easy to do in excel but I am unable to figure it out in power BI(we are doing a data connection to bring the data from excel and these column are custom columns) .
We need to mark the column B as the value of column A,where A column data is based on some condition,
for ex: if the Id is 102 the do a vlookup on comboid and if it matches copy the value of A1 into B2
Any help will be much appreiciated.
Thank You,
Mam
Solved! Go to Solution.
Hi @Anonymous ,
You need to create a new calculated column “B”.
B = CALCULATE ( FIRSTNONBLANK ( 'Table'[A], 1 ), FILTER ( 'Table', 'Table'[COMBOID] = EARLIER ( 'Table'[COMBOID] ) && 'Table'[ID] = EARLIER ( 'Table'[ID] ) - 1 ) )
Here is the result.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share the link from where i can download your PBI file.
r your revert Aashish.I am new to this community ,i was unable to find an option to upload the excel sample file.
let me know how exactly i can provide u the info.
Thanks,
Mam
Hi,
Upload the file to OneDrive/Google Drive and share the download link here.
Hi @Anonymous ,
You need to create a new calculated column “B”.
B = CALCULATE ( FIRSTNONBLANK ( 'Table'[A], 1 ), FILTER ( 'Table', 'Table'[COMBOID] = EARLIER ( 'Table'[COMBOID] ) && 'Table'[ID] = EARLIER ( 'Table'[ID] ) - 1 ) )
Here is the result.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your prompt response.
I was really occupied with some GO -live activity so was not able to test the code.
I ahve a question, what exactly is the use of Table in the filter and what table name i have to use here.
I tried it and it is throwing thi s:Expression error: The name 'CALCULATE' wasn't recognized.I tried looking for this error but could not find much.
I tried putting CALCULATE in lower case also but still the error is popping up.
FYI:I am currently working on excel 2013 .
Hi @Anonymous ,
"Table" is the table name where the data you use is located. And you need to use my codes in DAX instead of power query.
About sharing files, you can upload to OneDrive and generate sharing links.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |