SharePoint Lists, Lookup Fields and Power Query

[jamesclark458]

Hi folks,

Apologies if this has been covered elsewhere but I am getting into territory with Power Query that is a little beyond me at the moment and I haven't yet found a thread that answers the question..

I have a SharePoint site that has multiple lists;

List A

List B

List C

These lists all have the same columns, but are segregated for business reasons. Within each list there are a number of columns that are Type:Lookup

e.g.

List	Column	Type
A	A	Unique ID
A	B	Single Test
A	C	Lookup - Allow Multiple Selections
A	D	Lookup - Allow Multiple Selections

The Lookup columns Lookup data in other lists on the SharePoint (Columns D & E). 

When I import Lists A, B & C into PowerBI Columns C & D display as List Type. 

However the only way I have been able to display these values is by Expanding to new rows, the issue here is that Column A (the Unique ID) is duplicated and now prevents the data from refreshing properly. 

Is there a way I can display the data in Columns D&E without having to  Expand to New Rows? If I try to extract I get the error;

Expression.Error: We cannot convert a value of type Record to type Text.

Details:

Value=

lookupId=46

lookupValue=Supply Management

isSecretFieldValue=FALSE

Type=[Type]

Any help would be fantastic,

Thanks

Jamie

[mahoneypat]

You can get multiple values with a more complex expression. Can you provide sample data?  Or at least screen grabs of examples with one and two values?

Pat

[jamesclark458]

Thanks Pat, really appreciate this help 🙂

Below is the data I am currently working this. This is how it has exported from SharePoint into Excel (.iqy format) 

ID	Workstream	Descriptor
3	Supply Management;#46	Operational & Strategic;#1
4	IS&T (Systems);#43	Operational & Strategic;#1
5	IS&T (Systems);#43	People;#2
6	Catering/Food;#41	Operational & Strategic;#1
7	Supply Management;#46	Operational & Strategic;#1
8	Catering/Food;#41;#Supply Management;#46	Financial;#3;#Operational & Strategic;#1
9	Brand & Communications;#62	Operational & Strategic;#1;#Compliance;#5

The below screen grab is how the data appears after I apply the custom column formula originally mentioned - tweaked with a second column to allow the second values [Workstream]{1}[lookupValue]

[lookupValue] is used in place of [Name] as that is the name of the column being pulled through in Drill Down

Many thanks,

Jamie

[mahoneypat · Accepted solution]

You can use try ... otherwise to avoid errors.

= try [Workstream]{0}[Name] otherwise null

Pat

[jamesclark458]

This has worked a treat.

Thank you so much for your support and guidance with this Pat 🙂

[mahoneypat]

The error suggests it is a List containing one or more records.  Click to the right of the List hyperlink in the cell to preview the contents to confirm, and to see what the Record field names are. You should be able to add a custom column (or do a transform step) to extract the desired field(s). For example, a custom column with 

= [Workstream]{0}[Name]

should extract the Name field of the first/only Record in the List.

Pat

[jamesclark458]

Morning @mahoneypat 

Thank you for your help with this - using the custom column has worked. One thing I am wondering is whether there is a way to modify that formula to allow it to extract multiple values? For example one of the records has two list items on the SharePoint site.

I tried setting up a 2nd custom column to extract that second value, however it returned errors for the rows that don't have the 2nd value which then created issues itself.

If I need to just use the 1st value that is absolutely fine though. 

Thank you 🙂