Dynamic Images in SharePoint Document Sets

One of the cool features of Document Sets is that you can customize the "home page" of the Document Set by adding or editing its Content Type's home page web parts. The default page includes an image web part that can be customized with your choice of image. The only problem here is that the image is then used for all Document Sets created from this Content Type. Here's the default "home page" with the default image:

   

If you would like to change this image for each new Document Set added to a library, then upload a picture somewhere in SharePoint and then while viewing the new Document Set, click the Page ribbon, Edit Page and then edit the image web part. No fun if you have to do this for each new Document Set folder added to the library.
   

Make it Dynamic

What if you wanted the picture to be more automatic, or updateable by the users of the Document Set who might not have the permissions to edit the page?

Here's the plan:

• Let the users of the Document Set upload a picture to the Document Set. We will pick a required named like "TeamLogo.png".

• Modify the original Content Type in Site Settings, or modify the copy of that Content Type that is already associated with the library. (Content Types are copied to a library.)
• Add a Content Editor Web Part just below the existing Image Web Part.
• Add JavaScript to change the URL of the Image Web Part's HTML to point to our picture.

The steps below assume you have already created your custom Document Set Content Type.

1. Go to Site Settings and Site Content Types and click the Content Type, or
   Go to the library, visit Library Setting and click the Content Type.
2. Click Document Set Settings.
3. Scroll down and click Customize the Welcome Page.
4. In the ribbon click PAGE and Edit Page.
5. Click below the Image web part (but still in Zone 1).
6. Click the INSERT ribbon and click Web Part.
7. In the Media and Content section click Content Editor and the click Add.
8. If the web part did not go below the Image web part:
1. In the Content Editor Web Part click the dropdown arrow and click Edit Web Part.
2. Expand Layout, change the Zone Index from 0 to 1 and click Apply.
9.  In the Content Editor Web Part click "Click here to add new content".
10. Click the FORMAT TEXT ribbon and click Edit Source.
11. Add the JavaScript from below.
12. Replace "\\myimage.png" filename with something better like "\\teamlogo.pgn" or \\projectlogo.prg.
13. Click OK and then APPLY. (no image will display while in the page editor)
14. Click the PAGE ribbon and click Stop Editing.
15. If you just created a new Content Type, go create a new library and add this Content Type to the library.
16. In your library that is using this Content Type:
1. Click the New button (above the list of files or in the ribbon) and create a new Content Type folder.
      
   
2. Create a logo file and upload it to the Content Type folder. The image should be displayed in the Content Type home page area.

                        

The JavaScript code.

<script>
  // get the page's URL
  var url = window.location.href;
  // make sure we are not in page edit mode
  if(url.indexOf("RootFolder")>0)
  {
    // get the "RootFolder" query string parameter
    var rootpath = url.split("RootFolder=")[1].split("&")[0];
    
    // build a URL to our image file. Replace "myimage.png" with something
    // better like "\\teamlogo.pgn" or "\\projectlogo.prn"
    var imgpath = "..\\..\\.." + rootpath.replace(/%2F/g, "\\") + "\\myimage.png"; 
    // update the image source
    // Note that the ID is for the default doc set image web part. Yours
    // may be different!
    document.getElementById("MSOImageWebPart_WebPartWPQ2").src=imgpath;
  }
</script>

 ...

SharePoint.Tables – Power BI – Power Query M Functions

The Microsoft DOCS article for SharePoint.Tables says "Returns a table containing a row for each List item found at the specified SharePoint list url." While this sounds like it returns list "Items", it actually returns a list of Lists. It also reads like the URL is supposed to point to a list. Instead, it should point to the site or subsite.
If this was a typical DOCS article, I'd click the Edit button and offer an update to the content. For some reason, there is no Edit button. So here is my proposed revision:

Syntax

SharePoint.Tables(**url** as text, optional **options** as nullable record) as table

About

Returns a table containing a row for each List ^item found at the specified SharePoint ^list site url. Each row contains properties of ^the a List. options may be specified to control the following options:

• ApiVersion : A number (14 or 15) or the text "Auto" that specifies the SharePoint API version to use for this site. When not specified, API version 14 is used. When Auto is specified, the server version will be automatically discovered if possible, otherwise version defaults to 14. Non-English SharePoint sites require at least version 15.

Examples

Get a list of lists in a web:

let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15])
in
    SharePointSiteLists

Get details about a list:

let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    // Get a list named "Toys"...
    // (Could also use list GUID - [Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"] )
    ToyList = SharePointSiteLists{[Title="Toys"]}
in
    ToyList

Get a list of items in a list:

let
    // Get a list of tables at the site URL... 
    SharePointSiteLists = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    // Get a list named "Toys"...
    // (Could also use list GUID - [Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"] )
    ToyList = SharePointSiteLists{[Title="Toys"]},
    // Get the items from the list...
    Toys = ToyList[Items]
in
    Toys

 
Here's the output of the three examples above…

The list of lists:


 Data for a list:


List Items: (SharePoint tends to return a lot of extra columns you don't need.)

.

Three Ways to Load SharePoint Data into Power BI and Excel

Power BI includes a connector/wizard for SharePoint lists that makes is easy to load list content. It works great, but has a shortcoming or two. It first retrieves a list of all of the lists, and then it retrieves all of the list content… even if you only need a small subset of the data.

In this little article I'll show these approaches:

• Using the Wizards
• Writing your own "M" formulas
• Using the SharePoint ODATA RESTful web services, for lists and a lot more!

While they work for Power BI, they also work for Excel Power Query.

Using the Wizards

The wizards will write "M" formulas for you to connect to the SharePoint list. Although this will let you transform the data any way you like, all of the list content will be downloaded into Power Bi and then filtered.

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. If the full list of connectors is not displayed, click More.
4. Scroll, or use the search box, to find and click the “SharePoint Online List” or "SharePoint List" connector.
5. Enter the Site URL. (Just the URL to the site, not to a page or a list.)
   Example: https://yourDomain.sharepoint.com/sites/yourSite or https://yourDomain.sharepoint.com/sites/yourSite/yourSubSite
6. Click OK.
7. Find and checkmark your SharePoint list.
8. Review your data and click Edit. (SharePoint adds a number of hidden/internal columns that you will probably want to exclude.)
9. Optional: In the Query Settings area, click in the Name box and enter a new name for the imported data.
10. Select all of the columns that you do not need and click Remove Columns. Or, select the columns you want to keep, click the dropdown under Remove Columns, and click Remove Other Columns.
11. Click Close & Apply.
12. Create your visuals!

Writing your own "M" formulas

The wizards just write formulas for you. With a little practice, you can write better ones! Although this will let you transform the data any way you like, all of the list content will still be downloaded into Power Bi and then filtered. (i.e. keep reading to see how to use the REST web services to download only what you need.)

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. Click Blank Query. This will open the Query Editor.
4. Click the Advanced Editor button.
5. Write your formula, save your changes.
6. You can continue customizing the data in the Query Editor.
7. Click Close & Apply.
8. Create your visuals!

In the blank query you will see something like this:

let
    Source = ""
in
    Source

When you run the wizard in the previous example, it writes a formula similar to this:

let
    Source = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    #"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5" = Source{[Id="a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"a1c236c1-7d6d-4141-9d25-bf6f8e25d8a5",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

You can write a better formula than the wizard!

The "let" selects and restructures the data while the "in" is the final result where we get the data for the model. The "let" contains a series of functions that drill down, layer by layer, to get to the data you need.

The "Source" is a variable and can have any name. This first line selects the data source, SQL, SharePoint, etc. While the documentation for SharePoint.Tables says it uses a URL to the list, it is actually the URL to the server. (Kind of like how a SQL connection string names a database, but not a table.)

Here's an example of a hand written "let" statement that uses friendly names instead of GUIDs for tables and obvious names for the variables:

let
    SharePointSite = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]),
    ToyList = SharePointSite{[Title="Toys"]},
    Toys = ToyList[Items]
in
    Toys

These functions can be chained, so if you prefer a one line version:

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items]
in
    Toys

And if you want to pick just the columns you need without a lot of clicking…

let
    Toys = SharePoint.Tables("https://yourDomain.sharepoint.com/sites/yourSite", [ApiVersion = 15]){[Title="Toys"]}[Items],
    ToysData = Table.SelectColumns(Toys,{"Id", "Title", "Price", "Modified"})
 in
    ToysData

More on M:
    https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

Ok, that's good to know… but we still bring back all of the rows of data, even if we just want a few.

Using the SharePoint ODATA RESTful Web Services

The SharePoint List connector lets you bring in data from SharePoint lists and libraries. Using the SharePoint REST API web services you can bring in just about any kind of data found in SharePoint, including list data, lists of lists, lists of users, lists of sites and much more. Even better, you can write filters and select columns so only the needed data is sent from SharePoint to Power BI. (And with other REST APIs you can bring in just about any data from Office 365!)

1. Launch Power BI Desktop
2. Click Get Data from the ribbon
3. Click OData Feed.
4. Enter a URL to a SharePoint web service. This one retrieves all Touring bikes.
   This example assumes a list named “Bikes” where we only want three columns and only the rows for "Touring" bikes.
   Example:
   https://yourServer/sites/yourSite/_api/Lists/GetByTitle('Bikes')/Items?$select=price,color,style&$filter=category eq ' Touring'
5. Click OK.
6. Enter credentials, if needed.
7. Click OK.  (You have already selected your columns and rows from the REST query, so no additional Power Query edits needed.)
8. Create your visuals!

Note: You could start with a Blank Query and enter something like this:

let
    Source = OData.Feed("https://yourDomain.sharepoint.com/sites/yourSite/_api/web/lists/getbytitle('Bikes')/Items?$select=Title,Bike_x0020_Type,Size,Retail", null, [Implementation="2.0"])
in
    Source

There are REST Web Services for Everything!

A few other ideas: (and even more ideas here: https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service)

• Get a list of all of the lists in a site:
  https://yourServer/sites/yourSite/_api/web/lists
• Get a list of all items in a site’s recycle bin:
  https://yourServer/sites/yourSite/_api/web/Recyclebin
• Get a list of all subsites in the current Site Collection:
  https://yourServer/sites/yourSite/_api/web/webs
• Get a list of all site users:
  https://yourServer/sites/yourSite/_api/web/siteusers?$filter=startswith(LoginName,'i:0%23.f')

You can load multiple REST queries, and then define relationships to build reports for all kinds of SharePoint activity!

 

Learning the SharePoint REST API

To experiment with SharePoint REST services you can add my SharePoint REST Tester to your SharePoint site. See details here:

   https://techtrainingnotes.blogspot.com/2017/04/a-sharepoint-rest-api-tester-with-ajax.html

and download from here:

   https://github.com/microsmith/SharePointRESTtester

SharePoint Validation Formula Tip – Don't Use IF!

I often see questions about SharePoint validation formulas in the online forums that include IF statements to return True or False. Something like this:

    =IF(  someCondition , True, False  )
    =IF( Amount > 100, True, False )
    =IF( AND( State = "OH", Amount>100 ), True, False )

The IF is simply not needed!

Simply replace this:
    =IF( Amount > 100, True, False )
With this:
    =Amount > 100

It's either greater than, or it is not. The greater than test returns a True or False all by itself. For that matter, the expression in the first parameter of an IF statement must return True or False!

Here's what it looks like in both Classic and Modern UI.

What if you need to reverse the True and the False result?

Use the NOT function to reverse the True/False value.
    =NOT( Amount > 100 )

ANDs and ORs Return True or False

Simply replace this:
    =IF( AND( State = "OH", Amount>100 ), True, False )
With this:
    =AND( State = "OH", Amount>100 )

Two more examples…

If the state must be within the tri-state area you could write:
    =IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) )
Or you could just write:
    =OR( State="OH", State="KY", State="IN" )

While there are examples that require IF, you can solve most validations with a simple comparison, or the with the use of AND, OR and NOT.

    =IF( AND( Amount>100, IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) ) ), True, False)

Yup… I have seen those. And, it can be replaced with:
    =AND( Amount>100, OR( State="OH", State="KY", State="IN" ) )


And… book plug here… I have a lot more on validation formulas in my book!