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.

dgwilson's avatar
dgwilson
Resolver III
5 years ago

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

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

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.

  • dgwilson's avatar
    dgwilson
    Resolver III
    5 years ago

    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

    • PhilipTreacy's avatar
      PhilipTreacy
      Super User
      5 years ago

      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.

      • dgwilson's avatar
        dgwilson
        Resolver III
        5 years ago

        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