cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Janaa
Helper I
Helper I

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

 

1 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

View solution in original post

11 REPLIES 11
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Janaa_2-1627624707707.png

 

Function used:

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

 

Error screenshot from 2nd row till end:

Janaa_0-1627623427473.png

Column in PowerQuery:

Janaa_1-1627623651067.png

 

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

 

 

 

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

View solution in original post

Janaa
Helper I
Helper I

@hello Everyone,

 

Any help on this thread would be highly appreciated.

Greg_Deckler
Super User IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler 

 

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

Janaa_0-1627492151974.png

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.

 

Janaa_1-1627492435672.png

 

Please throw some insights to move further.

 

Thanks for your efforts to get this solved.

 

 

@Janaa Maybe try Table.FromList and then expand. 

 

Table functions - PowerQuery M | Microsoft Docs

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Kudoed Authors