Forum Discussion
Extracting Title from SharePoint Online list
I'm trying to extract the title from the Record in the Modified By column of a SharePoint list.
I'm accessing the SharePoint List using Power BI Desktop October release and the new Beta 2.0 connector.
The Modified By column returns a type List in each row of the data.
Below is my sad attempt to extract this to a type Record. After which I can expand and get to the Title field.
let
Source = SharePoint.Tables("https://xxxxxx.sharepoint.com/sites/CustomerHub/", [Implementation="2.0", ViewMode="All"]),
#"fdc6dc7f-80f9-45e0-b350-e93e1f714ccc" = Source{[Id="fdc6dc7f-80f9-45e0-b350-e93e1f714ccc"]}[Items],
#"Removed Other Columns" = Table.SelectColumns(#"fdc6dc7f-80f9-45e0-b350-e93e1f714ccc",{"Customer Name", "Outcomes Manager", "Modified By"}),
#"xForm Outcomes Manager" = Table.TransformColumns(#"Removed Other Columns", {"Modified By", each try if Value.Is("Modified By", type list) then Table.ExpandListColumn(#"Removed Other Columns", "Modified By") else "xb" otherwise "xxx"})
in
#"xForm Outcomes Manager"
Once This is working I can expand it to other fields that I also need the details from.
Currently every row is returning "xb" - to me this suggests that my Table.ExpandListColumn is failing?
Table.ExpandRecordColumn(#"Renamed Columns1", "Modified By", "title")
- David
- David
Hi David,
To summarise, if you want to extract the title directly from the List use this code
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Modified By", each try Record.Field([Outcomes Manager]{0}, "title") otherwise "")To extract the Record from the List use this code
Transform = Table.TransformColumns(#"Added Custom", {{"Outcomes Manager", each if Value.Is(_,type list) then try _{0} otherwise "" else [title = "not assigned"], type record}} )NOTE: I've put a try..otherwise in there to allow for an empty list.
Examples of both of these are in this PBIX file.
Cheers
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
16 Replies
Hi David,
To summarise, if you want to extract the title directly from the List use this code
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Modified By", each try Record.Field([Outcomes Manager]{0}, "title") otherwise "")To extract the Record from the List use this code
Transform = Table.TransformColumns(#"Added Custom", {{"Outcomes Manager", each if Value.Is(_,type list) then try _{0} otherwise "" else [title = "not assigned"], type record}} )NOTE: I've put a try..otherwise in there to allow for an empty list.
Examples of both of these are in this PBIX file.
Cheers
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.Nice Summary, thank you Phil.
- David
Hi dgwilson
In the Modified By column click on the double headed arrow and then Expand to New Rows
That should give you a column of Records. Again,click on the double headed arrow on the Modified By column then click on OK to extract the columns including the Title you are after.
Regards.
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.Thanks Phil...
I managed to get to that point about the same time you posted the message! 🙂
Next trick is to do the same thing but on a column where some of the fields are blank.
i.e. not every row has a "List".
- David
Hi dgwilson
Not sure what you mean, can you give an example?
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Glad to help
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.Which post would you like me to accept as the solution?
If you summarise in a post I'm happy to accept that one.
