How do I extract values from List/Rec to text inPowerQuery when extracting data from Sharepoint List

[Anonymous]

Hello Everyone,

When I tried to extract values from Sharepoint list, one particluar column is having values as list, when I clicked on list - Record. It has the below values wherein wanted to extract only "title" as Lastname, Firstname(Original value in sharepoint)

id

title - I want to show only this value in that column as Last name, First name.

email

and so on.

How do I acheive this in simple steps?

Could you please provide me with the easiest step to achieve this as rest other columns're fine.

Thanks in Advance for your prompt time & help.

[mahoneypat]

Please see this article/video on how to get data from SharePoint list fast.  It also provides example syntax on how to get just the fields you want and $expand next lists/records like this.

Get Data From SharePoint Lists … But Fast – Hoosier BI

Pat

[Anonymous]

@mahoneypat 

Thanks for your quick response, I have brought the data from sharepoint to Power BI comfortably with default view(Beta 2.0) features, definitely Rest API video would help me in other situations however I have only less than 500 records to analyse and out of 20 columns only 2 columns value populated as list(record with id, title, email, etc.) Just wanted to extract only "title" from the record. 

Thanks for your time & support. Your responses with other approches would be highly appreciated.

[mahoneypat]

You can use a formula like this either in an added custom column or if you modify a transform column step.  I'm assuming yours is a List with a single Record in it.  This gets the first item in the list ({0}) and the [title] field from the record.

= [ColumnWithListInIt]{0}[title]

Pat

[Anonymous]

@mahoneypat  Thanks for your time to look into the issue.

I have used the below function to extract only the "title". This particular column Manager is a List with 6 records from Sharepoint List like id, title, title, sip, email, picture, jobtitle and dept. Using the below function with created Index, I'm able to see only the value populating for first record wherein other remaining rows 're showing error.

Function used:

Record.Field([Manager]{[Index]}, "title")

Error screenshot from 2nd row till end:

Column in PowerQuery:

Only First record values extrcated from list as Last Name, First Name(as per Sharepoint List values). I have tried changing the Datatype to text evenafter getting the same error.

Please let me know how to apply the function in the above or we need to use some other function to get the expected results for all the records. 

Your help would be highly appreciated, Thanks in Advance.

Regards

Janaa

[mahoneypat]

Not sure what's going on.  A couple questions

1. Does your Index column start at 0.  If not, it should (or -1 from your index) to potentially avoid the not enough elements error.

2. Have you confirmed the List in the rows that error are not empty (click to the right of the work List to preview contents).  I'm guessing that's the case.  You could wrap your expression in try ... otherwise to avoid the error, but that won't fix the issue (of missing data potentially).

Pat

[Anonymous · Accepted solution]

Hi Pat,

Thanks for your response and time to look into the error. I have fixed this error this morning by just using the {0} in the existing function to extract the title alone from the list.

Record.Field([Manager]{0}, "title")

It working as expected with Lastname, Firstname in all the rows.

Thanks again for your time.

Regards

Janaa

[Anonymous]

@Anonymous Everyone,

Any help on this thread would be highly appreciated.

[Greg_Deckler]

@Anonymous So, if I understand correctly, in Power Query Editor, find the diverging arrows in the column header to the right of the column name. Click that icon. Deselect everything and then only select the field that you want, title.

[Anonymous]

@Greg_Deckler 

Please find my screenshot below. There is no diverging icon present in the column to select the required text here.

Also I have tried by creating a Customcolumn to populate the text value with an Index(created New column) which I'm looking for with the below function.

Record.Field([Column Name]{[Index]},"title")

which gives the expected result only for first record where still rest other records shows as an error as below.

Please throw some insights to move further.

Thanks for your efforts to get this solved.

[Greg_Deckler]

@Anonymous Maybe try Table.FromList and then expand. 

Table functions - PowerQuery M | Microsoft Docs

[Anonymous]

@Greg_Deckler : This solution doesn't work for my scenario. Thank you! for your time.