Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 ext...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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