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
Anonymous
Not applicable

vlookup in power BI

Capture.PNG

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

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

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.1-1.PNG

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-eachen-msft
Community Support
Community Support

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.1-1.PNG

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Capture.PNG

 

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.Capture.PNG

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.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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.