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.

dgwilson's avatar
dgwilson
Icon for Resolver III rankResolver III
5 years ago

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

 

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