Lookup Field

[KhrystinaM]

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?

[christinepayton]

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.

[KhrystinaM]

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

  

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

[christinepayton]

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. 

[KhrystinaM]

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. 

[christinepayton · 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. 

[KhrystinaM]

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

[KhrystinaM]

Sorry about that, I updated with screenshots. 

[danextian]

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