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
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.
Thank you for this Phil.
I can see what you've done in the PBIX... for me the Otherwise condition is being triggered.
Now this is likely because inside the list is a record. That's what we need to get out.
Good news is that you've used Table. TransformColumns - I was beginning to wonder if it was the right thing.
How do we modify your sample so that the list includes a record (first and only entry).
An image of the record structure is below. It's straight forward and I'm only after the "title".
I modified in your PBIX this line on "Table Try Otherwise"
Table.AddColumn(#"Changed Type", "Custom", each if [Data] =1 then {} else if [Data] = 2 then {[title="David"]} else if [Data] = 3 then "" else if [Data] = 4 then {[title="Phil"]} else {"x","y","z"})
Some progress... it's not right yet...
= Table.TransformColumns(#"Added Custom", {"Custom", each try if Value.Is(_, type list) then Table.ExpandListColumn(#"Added Custom", "Custom") else [] otherwise [name="otherwise"], type record})
- David
Hi David,
OK let's change tack, try this one line instead which willgive you another column with the title in it.
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Modified By", each try Record.Field([Outcomes Manager]{0}, "title") otherwise "")and here's the PBIX file showing what I did with sample data.
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.Hi Phil...
I'm pleased to report success! Nice work.
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Modified By", each try Record.Field([Outcomes Manager]{0}, "title") otherwise "")Given this... it should be possible to extract the record from the list? Either to the same or new column.
- David
c/- OwenAuger
Here it is.
= Table.TransformColumns(#"Added Custom1", {{"Custom", each if Value.Is(_,type list) then _{0} else [title = "not assigned"], type record}} )_{0}
Thank you both PhilipTreacy and OwenAuger - Awesome.
- David