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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KhrystinaM
Helper I
Helper I

Lookup Field

Hello, 

 

I have a SharePoint lookup field that is not giving me the value in Power BI. Can you not use a lookup field in PowerBI?

 

KhrystinaM_0-1712941786335.pngKhrystinaM_1-1712941896461.png

KhrystinaM_2-1712942097024.png

 

 

1 ACCEPTED SOLUTION

Yes, you can do this with CONCATENATEX() in DAX. I have a video tutorial describing various techniques for this sort of thing here if it helps - this is using a file source, not a SP list, but shows how to create the dimension table, relate them, then concatenate values with a comma between. 

 

 

View solution in original post

8 REPLIES 8
christinepayton
Super User
Super User

You can use lookup columns. Are there values for that particular field in your data? If it's blank, it might present like you're seeing. If it's multivalue, you may want to put it into a separate table and expand, then relate the two tables on their SP item ID, otherwise you'll get duplicate rows.

There's a Department ID and Department Table, but when I try to expand the table, no columns are found. 

 

KhrystinaM_0-1713221498898.png KhrystinaM_1-1713221524449.png KhrystinaM_3-1713221642131.png

 

This is the SharePoint data in that column. The circled cell shows multiple departments. 

KhrystinaM_4-1713221747183.png

 

 

 

It looks like you expanded it into comma-separated values - I think that is the issue. I believe you probably need to expand to new rows to have it successfully expand the related values. You may need to duplicate the query as a dimension table and relate the two on ID if you don't want to expand it in your main table - this is a commonly-done thing with multivalue fields generally, not just with SharePoint sources. 

After expanding the department ID and getting the department name, can I group them back together? For example John Doe is in department Administration and Com'l Service. I want it grouped where John Doe is only listed once with Administation, Com's Service in the same department name field. 

Yes, you can do this with CONCATENATEX() in DAX. I have a video tutorial describing various techniques for this sort of thing here if it helps - this is using a file source, not a SP list, but shows how to create the dimension table, relate them, then concatenate values with a comma between. 

 

 

Your video was extremely helpful, thank you so much!!

KhrystinaM
Helper I
Helper I

Sorry about that, I updated with screenshots. 

danextian
Super User
Super User

Hi @KhrystinaM ,

 

Not enough information. Please elaborate. Give sample data/sample result. Please refer to this post.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/144... 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.