Custom columns in a SharePoint Page Library

[MichaelHutchens]

Hi folks, I'm hoping someone might be able to assist. I'm attempting to connect to a SharePoint Page Library using Online Services. The Page Library table (lets call that table 'Pages' has a custom column called 'Author Byline' that contains a string called 'Title' that has the name of the page owner. I want to bring that Title back into the main 'Pages' table in the same row as the other fields. But the column is currently a List type and I'm missing the option to expand it:

When I click down into List > then click on Record I get the below with the field I need ("Title"), but I can't see a way of bringing that back to the main 'Pages' table, or even a unique identifier for each Title record that I could use to merge with the 'Pages' table:

Any help would be really appreciated 🙂

[AlexisOlson]

If you remove those blank rows, does it give you the option to expand?

[MichaelHutchens]

Hi @AlexisOlson , it does, but there is data I need in those blank columns so I can't remove them. E.g. a blank space means that a page doesn't have an owner, which is also important for me to know so I can organize one.

For additional context, I'm working with 12 different page libraries, of which this is just one example. I've tried splitting out the table into two different ones (one filtered by whether the column has a [List] entry, and one filtered to only show blank rows), drilling down into the former, then merging the two separate tables back into one. That works fine for a single table, but my report slows down to a crawl now that I've added 6 of the 12 page libraries and I don't think it's scaleable. So I'm looking for a more elegant solution 🙂

[MichaelHutchens]

This seems to be what I need, but I don't know how to analyse the code to customize it to my scenario:

https://community.powerbi.com/t5/Power-Query/SharePoint-List-and-null-values/td-p/1761940 

[MichaelHutchens]

Here's my current code, if it helps:

let
    Source = SharePoint.Tables("https:URL_GOES_HERE", [Implementation="2.0", ViewMode="All"]),
    #"793529eb-48c3-4fad-a005-7a4aa0a716ad" = Source{[Id="793529eb-48c3-4fad-a005-7a4aa0a716ad"]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(#"793529eb-48c3-4fad-a005-7a4aa0a716ad",{"Name", "Author Byline"})
in
    #"Removed Other Columns"

[AlexisOlson]

Try this:

let
    Source = SharePoint.Tables("https:URL_GOES_HERE", [Implementation="2.0", ViewMode="All"]),
    #"793529eb-48c3-4fad-a005-7a4aa0a716ad" = Source{[Id="793529eb-48c3-4fad-a005-7a4aa0a716ad"]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(#"793529eb-48c3-4fad-a005-7a4aa0a716ad",{"Name", "Author Byline"}),
	#"Fix Non List Values" = Table.TransformColumns( #"Removed Other Columns", {{"Author Byline", each if Value.Is(_, type list) then _ else null}} )
in
    #"Fix Non List Values"

[MichaelHutchens]

Thank you so much @AlexisOlson 🙂

One final question, I actually have two columns of [List] data that both contain spaces. I've tried to add a second entry in the Applied Steps and just update the column name field, but it results in the 'null' entries 'jumping' from one column to the other:

I suspect it's because the hex long string in the applied steps isn't unique, do you know how I might fix that so I can have null entries in both columns, rather than just one or the other?

Apologies for 'teasing out' the requirements here, I really appreciate your time.

[AlexisOlson · Accepted solution]

Either do them both in the same step or else change the reference to the prior step. Your new step is referencing the step before the first remove nulls step.

Here's both in the same step:

let
    Source = SharePoint.Tables("https:URL_GOES_HERE", [Implementation="2.0", ViewMode="All"]),
    #"793529eb-48c3-4fad-a005-7a4aa0a716ad" = Source{[Id="793529eb-48c3-4fad-a005-7a4aa0a716ad"]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(#"793529eb-48c3-4fad-a005-7a4aa0a716ad",{"Name", "Author Byline"}),
	#"Fix Non List Values" = Table.TransformColumns( #"Removed Other Columns", {{"Author Byline", each if Value.Is(_, type list) then _ else null}, {"Checked Out To", each if Value.Is(_, type list) then _ else null}} )
in
    #"Fix Non List Values"

[MichaelHutchens]

Fabulous @AlexisOlson , thank you so much 🙂 That's exactly what I need.