Forum Discussion

dgwilson's avatar
dgwilson
Icon for Resolver III rankResolver 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
Icon for Super User rankSuper User
5 years ago

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.

dgwilson's avatar
dgwilson
Icon for Resolver III rankResolver 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
    Icon for Super User rankSuper 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
      Icon for Resolver III rankResolver 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
        Icon for Super User rankSuper 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.