Forum Discussion
Extracting Title from SharePoint Online list
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.
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.
See below how only some of the rows have a "type" list. Within that list is a record ... and that's what I want to get to. That record is an Office 365 Person (I may have expressed that wrong) but is the same type of record as the "Modified By"... so I want "title".
I'm here currently and everything has returned "not assigned" for every row.
= Table.TransformColumns(#"Expanded Modified By1", {"Outcomes Manager", each try Table.ExpandListColumn(#"Expanded Modified By1", "Outcomes Manager"{0}) otherwise [title = "not assigned"]})
- David
Hi dgwilson
The double headed arrow to expand the lists is not shown because one of the values in that column is a space, empty string (blank) or some other whitespace character.
Click on the single drop down arrow at the top of the column to filter those out and then you can expand the lists.
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.That's the trick... I won't want the list filtered... I need those blanks.
I'm currently trying to test for "list" type. It isn't working... hopefully I'm on the right path.
= Table.TransformColumns(#"Expanded Modified By1", "Outcomes Manager", each try if Value.Is("Modified By", type list) = true then Table.ExpandListColumn(#"Expanded Modified By1", "Outcomes Manager"{0})else [title = "not assigned"] otherwise [title = "try failure"])- David
Hi dgwilson
Try this line instead. It'll give you a string of comma separated values from within each list.
= Table.TransformColumns(#"Expanded Modified By1", {"Outcomes Manager", each try Text.Combine(List.Transform(_, Text.From), ",") otherwise "", type text})Here's a sample PBIX file where I've included 2 examples of extracting lists from columns with empty rows.
You can use try .. otherwise as above, or you can leave out the otherwise and then replace errors.
Using try..otherwise is probably better but never hurts to know other ways to do things.
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.