Forum Discussion

dgwilson's avatar
dgwilson
Resolver III
5 years ago
Solved

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 connecto...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    dgwilson 

    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.

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

dgwilson 

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.

dgwilson's avatar
dgwilson
Resolver III
5 years ago

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

  • dgwilson's avatar
    dgwilson
    Resolver III
    5 years ago

    FYI care of Owen Auger we have this:

     

    = Table.TransformColumns(#"Expanded Modified By", {{"Outcomes Manager", each if http://Value.Is(_,type list) then List.Transform(_,each [title]) else {"not assigned"}, type list}} )

    I've also asked Owen about extracting the "record".

     

    OwenAuger 

     

    - David

  • dgwilson's avatar
    dgwilson
    Resolver III
    5 years ago

    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