topic Re: Lookup Field in Desktop

Lookup Field

KhrystinaM — Fri, 12 Apr 2024 17:15:26 GMT

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?

Re: Lookup Field

danextian — Fri, 12 Apr 2024 15:49:39 GMT

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/1447523/jump-to/first-unread-message

Re: Lookup Field

KhrystinaM — Fri, 12 Apr 2024 17:16:28 GMT

Sorry about that, I updated with screenshots.

Re: Lookup Field

christinepayton — Mon, 15 Apr 2024 16:43:08 GMT

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.

Re: Lookup Field

KhrystinaM — Mon, 15 Apr 2024 22:56:41 GMT

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.

Re: Lookup Field

christinepayton — Tue, 16 Apr 2024 16:25:55 GMT

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.

Re: Lookup Field

KhrystinaM — Tue, 16 Apr 2024 17:29:03 GMT

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.

Re: Lookup Field

christinepayton — Tue, 16 Apr 2024 19:33:17 GMT

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.

Re: Lookup Field

KhrystinaM — Wed, 17 Apr 2024 18:03:55 GMT

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