Power BI version history from a SharePoint Document Library using Power Query

2 comments

This was to be a nice simple post, but after a couple of days tinkering with the help of some colleagues (special thanks to Maria Papadaki), we’ve created a convoluted, but working solution for my first question in the search for good and proper version management for my organizations PowerBI dashboards.

So please, pretty, pretty please, tell me when you have a clean alternative.

Power BI Version Management – Part 1

The is the first step in creating a Power BI version management setup. My end-goal of this series of blog posts is, to develop and describe a controlled way of working with Power BI dashboards in an enterprise environment. To encompass the entire dashboard lifecycle.

Power BI details and version history page

I want a page inside my PowerBI that contains 2 elements:

  • What are the people I can connect when I have questions about the report? (owner and stewards)
  • What is the version history and what are the changes for each version?
Power BI Versioning output
Power BI about page

Simple question; I’ve made a working MVP, with 2 tables inside the PowerBI that are maintained manually. This look great for the end-users, but is a hassle to maintain.

So for the next step in the maturity ladder, I want to leverage the capabilities of SharePoint. I want to use the version history of a SharePoint list and custom fields to maintain the additional properties.

The next chapters will work towards 2 functions with four parameters: tenant, site, document library name and filename:

Parameters

SharePoint API v1 for document versions

This SharePoint API is easy to use, but it is not returning information about who made the latest change.

Parameter is the list id, not the item name. To get at the item id from the name we first need to get the list of items.

let
  Source = (
    #”SharePoint tenant” as text,
    #”Site name” as text,
    #”List name” as text,
    #”File name” as text
  ) =>
    let
      Source = Xml.Tables (
        Web.Contents (
          Text.Combine (
            {
              “https://”,
              #”SharePoint tenant”,
              “/sites/”,
              #”Site name”,
              “/_api/Web/GetFileByServerRelativePath(decodedurl=’/sites/”,
              #”Site name”,
              “/”,
              #”List name”,
              “/”,
              #”File name”,
              “‘)/Versions”
            }
          )
        )
      ),
      entry = Source{0}[entry],
      #”Removed Other Columns” = Table.SelectColumns ( entry, { “content” } ),
      #”Expanded content” = Table.ExpandTableColumn (
        #”Removed Other Columns”,
        “content”,
        { “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” },
        { “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” }
      ),
      #”Expanded http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”
        = Table.ExpandTableColumn (
        #”Expanded content”,
        “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”,
        { “properties” },
        { “properties” }
      ),
      #”Expanded properties” = Table.ExpandTableColumn (
        #”Expanded http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”,
        “properties”,
        { “http://schemas.microsoft.com/ado/2007/08/dataservices” },
        { “http://schemas.microsoft.com/ado/2007/08/dataservices” }
      ),
      #”Expanded http://schemas.microsoft.com/ado/2007/08/dataservices” = Table.ExpandTableColumn (
        #”Expanded properties”,
        “http://schemas.microsoft.com/ado/2007/08/dataservices”,
        {
          “CheckInComment”,
          “Created”,
          “CreatedBy”,
          “ID”,
          “IsCurrentVersion”,
          “Length”,
          “Size”,
          “Url”,
          “VersionLabel”
        },
        {
          “CheckInComment”,
          “Created”,
          “CreatedBy”,
          “ID.1”,
          “IsCurrentVersion”,
          “Length”,
          “Size”,
          “Url”,
          “VersionLabel”
        }
      ),
      #”Expanded Created” = Table.ExpandTableColumn (
        #”Expanded http://schemas.microsoft.com/ado/2007/08/dataservices”,
        “Created”,
        { “Element:Text” },
        { “Created Date” }
      ),
      #”Expanded IsCurrentVersion” = Table.ExpandTableColumn (
        #”Expanded Created”,
        “IsCurrentVersion”,
        { “Element:Text” },
        { “IsCurrentVersion” }
      ),
      #”Expanded Size” = Table.ExpandTableColumn (
        #”Expanded IsCurrentVersion”,
        “Size”,
        { “Element:Text” },
        { “Size in Bytes” }
      ),
      #”Inserted Text After Delimiter” = Table.AddColumn (
        #”Expanded Size”,
        “Filename”,
        each Text.AfterDelimiter ( [Url], “/”, 2 ),
        type text
      ),
      #”Removed Columns” = Table.RemoveColumns (
        #”Inserted Text After Delimiter”,
        { “Length”, “CreatedBy”, “ID.1” }
      ),
      #”Renamed Columns” = Table.RenameColumns (
        #”Removed Columns”,
        {
          { “VersionLabel”, “Version” },
          { “Size in Bytes”, “FileSizeBytes” },
          { “Created Date”, “Modified” }
        }
      ),
      #”Changed Type” = Table.TransformColumnTypes (
        #”Renamed Columns”,
        {
          { “Version”, type text },
          { “Url”, type text },
          { “FileSizeBytes”, Int64.Type },
          { “Modified”, type datetime },
          { “IsCurrentVersion”, type logical },
          { “CheckInComment”, type text }
        }
      ),
      #”Replaced Errors” = Table.ReplaceErrorValues (
        #”Changed Type”,
        { { “CheckInComment”, “” } }
      ),
      #”Reordered Columns” = Table.ReorderColumns (
        #”Replaced Errors”,
        {
          “Filename”,
          “Version”,
          “CheckInComment”,
          “Modified”,
          “IsCurrentVersion”,
          “FileSizeBytes”,
          “Url”
        }
      )
    in
      #”Reordered Columns”
in
  Source
Power Query Formatter

SharePoint API v2 for document versions

The second version of the SharePoint API is actually the support by the graph API. It looks like Microsoft is pushing this way for the future. The Graph API is a little harder to use, but in the end it gave me a nice result.

But,…. no CheckinComments.

let
  Source = (
    #”SharePoint tenant” as text,
    #”Site name” as text,
    #”List name” as text,
    #”Item ID” as number
  ) =>
    let
      Source = Json.Document (
        Web.Contents (
          Text.Combine (
            {
              “https://”,
              #”SharePoint tenant”,
              “/_api/v2.0/sites/”,
              #”SharePoint tenant”,
              “:/sites/”,
              #”Site name”,
              “:/lists/”,
              #”List name”,
              “/items/”,
              Number.ToText ( #”Item ID” ),
              “/versions?expand=fields”
            }
          )
        )
      ),
      value = Source[value],
      #”Converted to Table” = Table.FromList (
        value,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
      ),
      #”Expanded Column1″ = Table.ExpandRecordColumn (
        #”Converted to Table”,
        “Column1”,
        { “id”, “lastModifiedBy”, “lastModifiedDateTime”, “fields@odata.navigationLink”, “fields” },
        { “id”, “lastModifiedBy”, “lastModifiedDateTime”, “fields@odata.navigationLink”, “fields” }
      ),
      #”Expanded fields” = Table.ExpandRecordColumn (
        #”Expanded Column1″,
        “fields”,
        {
          “FileLeafRef”,
          “Title”,
          “ID”,
          “Created”,
          “Modified”,
          “_CheckinComment”,
          “DocIcon”,
          “ItemChildCount”,
          “FolderChildCount”,
          “_UIVersionString”
        },
        {
          “FileLeafRef”,
          “Title”,
          “ID.1”,
          “Created”,
          “Modified”,
          “_CheckinComment”,
          “DocIcon”,
          “ItemChildCount”,
          “FolderChildCount”,
          “_UIVersionString”
        }
      ),
      #”Expanded lastModifiedBy” = Table.ExpandRecordColumn (
        #”Expanded fields”,
        “lastModifiedBy”,
        { “user” },
        { “lastModifiedBy.user” }
      ),
      #”Expanded lastModifiedBy.user” = Table.ExpandRecordColumn (
        #”Expanded lastModifiedBy”,
        “lastModifiedBy.user”,
        { “email”, “displayName” },
        { “ModifiedByEmail”, “ModifiedBy” }
      ),
      #”Removed Columns” = Table.RemoveColumns (
        #”Expanded lastModifiedBy.user”,
        { “fields@odata.navigationLink”, “Created”, “_UIVersionString”, “lastModifiedDateTime” }
      ),
      #”Renamed Columns” = Table.RenameColumns (
        #”Removed Columns”,
        {
          { “id”, “Version” },
          { “_CheckinComment”, “CheckinComment” },
          { “ID.1”, “ListItemID” },
          { “FileLeafRef”, “Filename” },
          { “Modified”, “Modified” }
        }
      ),
      #”Changed Type” = Table.TransformColumnTypes (
        #”Renamed Columns”,
        {
          { “Version”, type text },
          { “Modified”, type datetime },
          { “ModifiedByEmail”, type text },
          { “ModifiedBy”, type text },
          { “Filename”, type text },
          { “Title”, type text },
          { “ListItemID”, Int64.Type },
          { “CheckinComment”, type text },
          { “DocIcon”, type text },
          { “ItemChildCount”, Int64.Type },
          { “FolderChildCount”, Int64.Type }
        }
      ),
      #”Reordered Columns” = Table.ReorderColumns (
        #”Changed Type”,
        {
          “ListItemID”,
          “Filename”,
          “Title”,
          “Version”,
          “CheckinComment”,
          “Modified”,
          “ModifiedBy”,
          “ModifiedByEmail”,
          “DocIcon”,
          “ItemChildCount”,
          “FolderChildCount”
        }
      )
    in
      #”Reordered Columns”
in
  Source
Power Query Formatter

Joining the version queries

To join the queries to one function, with 4 parameters.

We need to do a little work, because the v1 version is based on the filename, while the v2 version uses the ListItemID. So a little lookup is necessary.

let
  Source = ( #”SharePoint tenant” as text, #”Site name” as text, #”List name” as text ) =>
    let
      Source = Json.Document (
        Web.Contents (
          Text.Combine (
            {
              “https://”,
              #”SharePoint tenant”,
              “/_api/v2.0/sites/”,
              #”SharePoint tenant”,
              “:/sites/”,
              #”Site name”,
              “:/lists/”,
              #”List name”,
              “/items?expand=fields”
            }
          )
        )
      ),
      value = Source[value],
      #”Converted to Table” = Table.FromList (
        value,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
      ),
      #”Expanded Column1″ = Table.ExpandRecordColumn (
        #”Converted to Table”,
        “Column1”,
        {
          “@odata.etag”,
          “createdBy”,
          “createdDateTime”,
          “eTag”,
          “id”,
          “ModifiedBy”,
          “lastModifiedDateTime”,
          “parentReference”,
          “webUrl”,
          “contentType”,
          “fields@odata.navigationLink”,
          “fields”
        },
        {
          “@odata.etag”,
          “createdBy”,
          “Created”,
          “eTag”,
          “id”,
          “ModifiedBy”,
          “lastModifiedDateTime”,
          “parentReference”,
          “WebUrl”,
          “contentType”,
          “fields@odata.navigationLink”,
          “fields”
        }
      ),
      #”Removed Columns” = Table.RemoveColumns (
        #”Expanded Column1″,
        { “@odata.etag”, “eTag”, “contentType”, “fields@odata.navigationLink”, “parentReference” }
      ),
      #”Expanded fields” = Table.ExpandRecordColumn (
        #”Removed Columns”,
        “fields”,
        {
          “FileLeafRef”,
          “Title”,
          “ContentType”,
          “_CheckinComment”,
          “DocIcon”,
          “FileSizeDisplay”,
          “ItemChildCount”,
          “FolderChildCount”,
          “_UIVersionString”
        },
        {
          “FileLeafRef”,
          “Title”,
          “ContentType”,
          “_CheckinComment”,
          “DocIcon”,
          “FileSizeDisplay”,
          “ItemChildCount”,
          “FolderChildCount”,
          “_UIVersionString”
        }
      ),
      #”Expanded createdBy” = Table.ExpandRecordColumn (
        #”Expanded fields”,
        “createdBy”,
        { “user” },
        { “createdBy.user” }
      ),
      #”Expanded createdBy.user” = Table.ExpandRecordColumn (
        #”Expanded createdBy”,
        “createdBy.user”,
        { “email”, “displayName” },
        { “CreatedByEmail”, “CreatedBy” }
      ),
      #”Expanded lastModifiedBy” = Table.ExpandRecordColumn (
        #”Expanded createdBy.user”,
        “ModifiedBy”,
        { “user” },
        { “lastModifiedBy.user” }
      ),
      #”Expanded lastModifiedBy.user” = Table.ExpandRecordColumn (
        #”Expanded lastModifiedBy”,
        “lastModifiedBy.user”,
        { “email”, “displayName” },
        { “ModifiedByEmail”, “ModifiedBy” }
      ),
      #”Renamed Columns” = Table.RenameColumns (
        #”Expanded lastModifiedBy.user”,
        {
          { “lastModifiedDateTime”, “Modified” },
          { “FileLeafRef”, “Filename” },
          { “_CheckinComment”, “CheckinComment” },
          { “FileSizeDisplay”, “FileSizeBytes” },
          { “_UIVersionString”, “Version” },
          { “id”, “ListItemID” }
        }
      ),
      #”Reordered Columns” = Table.ReorderColumns (
        #”Renamed Columns”,
        {
          “ListItemID”,
          “Filename”,
          “Title”,
          “Version”,
          “CheckinComment”,
          “Modified”,
          “ModifiedBy”,
          “ModifiedByEmail”,
          “Created”,
          “CreatedBy”,
          “CreatedByEmail”,
          “WebUrl”,
          “ContentType”,
          “DocIcon”,
          “FileSizeBytes”,
          “ItemChildCount”,
          “FolderChildCount”
        }
      ),
      #”Changed Type” = Table.TransformColumnTypes (
        #”Reordered Columns”,
        {
          { “Modified”, type datetime },
          { “ListItemID”, Int64.Type },
          { “Filename”, type text },
          { “Title”, type text },
          { “Version”, type text },
          { “CheckinComment”, type text },
          { “CreatedBy”, type text },
          { “CreatedByEmail”, type text },
          { “Created”, type datetime },
          { “ModifiedBy”, type text },
          { “ModifiedByEmail”, type text },
          { “WebUrl”, type text },
          { “ContentType”, type text },
          { “DocIcon”, type text },
          { “FileSizeBytes”, Int64.Type },
          { “ItemChildCount”, Int64.Type },
          { “FolderChildCount”, Int64.Type }
        }
      )
    in
      #”Changed Type”
in
  Source
Power Query Formatter

Then join the 3 queries to a useable result:

let
  Source = (
    #”SharePoint tenant” as text,
    #”Site name” as text,
    #”List name” as text,
    #”File name” as text
  ) =>
    let

      // retrieve list of all items
      #”Item” = #”GetSharePointList v2″ ( #”SharePoint tenant”, #”Site name”, #”List name” ),
      #”Item.Filtered” = Table.SelectRows ( #”Item”, each ( [Filename] = #”File name” ) ),
      #”Items.Remove Columns” = Table.RemoveColumns (
        Item.Filtered,
        {
          “Version”,
          “CheckinComment”,
          “Modified”,
          “ModifiedBy”,
          “ModifiedByEmail”,
          “ContentType”,
          “DocIcon”,
          “ItemChildCount”,
          “FolderChildCount”
        }
      ),
      #”Items.Renamed Columns” = Table.RenameColumns (
        #”Items.Remove Columns”,
        { { “FileSizeBytes”, “LastVersionFileSizeBytes” } }
      ),
      // retrieve details with API v1
      #”Versions1″ = #”GetSharePointListItemVersionsByName v1″ (
        #”SharePoint tenant”,
        #”Site name”,
        #”List name”,
        #”File name”
      ),
      // retrieve details with API v2
      #”Versions2″ = #”GetSharePointListItemVersionsByItemID v2″ (
        #”SharePoint tenant”,
        #”Site name”,
        #”List name”,
        List.First ( #”Items.Renamed Columns”[#”ListItemID”] )
      ),
      #”Join1.Item with Version 2″ = Table.NestedJoin (
        Versions2,
        { “Filename” },
        #”Items.Renamed Columns”,
        { “Filename” },
        “Version 2”,
        JoinKind.LeftOuter
      ),
      #”Join1.Expanded Version 2″ = Table.ExpandTableColumn (
        #”Join1.Item with Version 2″,
        “Version 2”,
        { “Created”, “CreatedBy”, “CreatedByEmail”, “WebUrl”, “LastVersionFileSizeBytes” },
        { “Created”, “CreatedBy”, “CreatedByEmail”, “LastWebUrl”, “LastVersionFileSizeBytes” }
      ),
      #”Join2.Version 1 with Version 2″ = Table.NestedJoin (
        #”Join1.Expanded Version 2″,
        { “Filename”, “Version” },
        Versions1,
        { “Filename”, “Version” },
        “Invoked Function GetSharePointListItemVersionsByItemID v2”,
        JoinKind.LeftOuter
      ),
      #”Join2.Expanded GetSharePointListItemVersionsByItemID v1″ = Table.ExpandTableColumn (
        #”Join2.Version 1 with Version 2″,
        “Invoked Function GetSharePointListItemVersionsByItemID v2”,
        { “CheckInComment”, “IsCurrentVersion”, “FileSizeBytes”, “Url” },
        { “2.CheckInComment”, “IsCurrentVersion”, “2.FileSizeBytes”, “VersionFileUrl” }
      ),
      #”Join2.Renamed Columns” = Table.RenameColumns (
        #”Join2.Expanded Invoked Function GetSharePointListItemVersionsByItemID v1″,
        { { “CheckinComment”, “1.CheckinComment” } }
      ),
      #”Join2.Merge CheckInComments” = Table.AddColumn (
        #”Join2.Renamed Columns”,
        “CheckinComment”,
        each if [#”2.CheckInComment”] = null then [#”1.CheckinComment”] else [#”2.CheckInComment”]
      ),
      #”Join2.Merge FileSizes” = Table.AddColumn (
        #”Join2.Merge CheckInComments”,
        “FileSizeBytes”,
        each
          if [#”2.FileSizeBytes”] = null then
            [LastVersionFileSizeBytes]
          else
            [#”2.FileSizeBytes”]
      ),
      #”Join2.Changed Type” = Table.TransformColumnTypes (
        #”Join2.Merge FileSizes”,
        { { “FileSizeBytes”, Int64.Type }, { “CheckinComment”, type text } }
      ),
      #”Join2.Reordered Columns” = Table.ReorderColumns (
        #”Join2.Changed Type”,
        {
          “ListItemID”,
          “Filename”,
          “Title”,
          “Version”,
          “FileSizeBytes”,
          “CheckinComment”,
          “1.CheckinComment”,
          “Modified”,
          “ModifiedBy”,
          “ModifiedByEmail”,
          “LastWebUrl”,
          “LastVersionFileSizeBytes”,
          “2.CheckInComment”,
          “IsCurrentVersion”,
          “2.FileSizeBytes”,
          “VersionFileUrl”
        }
      ),
      #”Join2.Add base url” = Table.AddColumn (
        #”Join2.Reordered Columns”,
        “FileUrl”,
        each
          if [VersionFileUrl] = null then
            [LastWebUrl]
          else
            Text.Combine (
              { “https://”, #”SharePoint tenant”, “/sites/”, #”Site name”, “/”, [VersionFileUrl] }
            )
      ),
      #”Join2.Removed Columns” = Table.RemoveColumns (
        #”Join2.Add base url”,
        {
          “1.CheckinComment”,
          “2.CheckInComment”,
          “LastVersionFileSizeBytes”,
          “2.FileSizeBytes”,
          “ListItemID”,
          “LastWebUrl”,
          “VersionFileUrl”,
          “Created”,
          “CreatedBy”,
          “CreatedByEmail”,
          “DocIcon”,
          “ItemChildCount”,
          “FolderChildCount”
        }
      ),
      #”Added Major Version” = Table.AddColumn (
        #”Join2.Removed Columns”,
        “MajorVersion”,
        each Text.BeforeDelimiter ( [Version], “.” )
      ),
      #”Added Minor Version” = Table.AddColumn (
        #”Added Major Version”,
        “MinorVersion”,
        each Text.AfterDelimiter ( [Version], “.” )
      ),
      #”Changed Type” = Table.TransformColumnTypes (
        #”Added Minor Version”,
        { { “MinorVersion”, Int64.Type }, { “MajorVersion”, Int64.Type } }
      ),
      #”Added VersionSortColumn” = Table.AddColumn (
        #”Changed Type”,
        “VersionSortColumn”,
        each 1000 * [MajorVersion] + [MinorVersion]
      ),
      #”Sorted Rows” = Table.Sort (
        #”Added VersionSortColumn”,
        { { “VersionSortColumn”, Order.Descending } }
      )
    in
      #”Sorted Rows”
in
  Source
Power Query Formatter

And this is the result:

Version history

Getting the item details and the custom fields

What rest now is getting the list item fields, including the custom fields. Remind that each custom field needs a little tweaking of this function. Here it’s demonstrated with 2 custom fields with persons.

When using the person type, I needed to have it a multiple value select, otherwise this function didn’t return any value. Don’t know yet why, but I’ll find out sometime and hope to update this post.

let
  Source = (
    #”SharePoint tenant” as text,
    #”Site name” as text,
    #”List name” as text,
    #”File name” as text
  ) =>
    let
      Source = Json.Document (
        Web.Contents (
          Text.Combine (
            {
              “https://”,
              #”SharePoint tenant”,
              “/_api/v2.0/sites/”,
              #”SharePoint tenant”,
              “:/sites/”,
              #”Site name”,
              “:/lists/”,
              #”List name”,
              “?expand=columns,items(expand=fields)”
            }
          )
        )
      ),
      items = Source[items],
      #”Converted to Table” = Table.FromList (
        items,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
      ),
      #”Expanded Column1″ = Table.ExpandRecordColumn (
        #”Converted to Table”,
        “Column1”,
        {
          “createdBy”,
          “createdDateTime”,
          “lastModifiedBy”,
          “lastModifiedDateTime”,
          “webUrl”,
          “fields”
        },
        { “createdBy”, “createdDateTime”, “ModifiedBy”, “Modified”, “webUrl”, “fields” }
      ),
      // make sure the custom columns are part of the expansion
      #”Expanded fields” = Table.ExpandRecordColumn (
        #”Expanded Column1″,
        “fields”,
        {
          “FileLeafRef”,
          “Title”,
          “ContentType”,
          “_CheckinComment”,
          “DocIcon”,
          “FileSizeDisplay”,
          “ItemChildCount”,
          “FolderChildCount”,
          “_UIVersionString”,
          “Owner”,
          “Stewards”
        },
        {
          “Filename”,
          “Title”,
          “ContentType”,
          “CheckinComment”,
          “DocIcon”,
          “FileSizeBytes”,
          “ItemChildCount”,
          “FolderChildCount”,
          “Version”,
          “Owner”,
          “Stewards”
        }
      ),
      // filter for the parameter document
      #”Filtered Rows” = Table.SelectRows ( #”Expanded fields”, each ( [Filename] = #”File name” ) ),
      // work standard columns
      #”Expanded createdBy” = Table.ExpandRecordColumn (
        #”Filtered Rows”,
        “createdBy”,
        { “user” },
        { “createdBy.user” }
      ),
      #”Expanded createdBy.user” = Table.ExpandRecordColumn (
        #”Expanded createdBy”,
        “createdBy.user”,
        { “email”, “displayName” },
        { “CreatedByEmail”, “CreatedBy” }
      ),
      #”Expanded lastModifiedBy” = Table.ExpandRecordColumn (
        #”Expanded createdBy.user”,
        “ModifiedBy”,
        { “user” },
        { “lastModifiedBy.user” }
      ),
      #”Expanded lastModifiedBy.user” = Table.ExpandRecordColumn (
        #”Expanded lastModifiedBy”,
        “lastModifiedBy.user”,
        { “email”, “displayName” },
        { “ModifiedByEmail”, “ModifiedBy” }
      ),
      // work the custom properties
      #”Extracted Owners” = Table.TransformColumns (
        #”Expanded lastModifiedBy.user”,
        {
          “Owner”,
          each Text.Combine ( List.Transform ( _, each Record.Field ( _, “LookupValue” ) ), “,” ),
          type text
        }
      ),
      #”Replaced Empty Owners” = Table.ReplaceErrorValues (
        #”Extracted Owners”,
        { { “Owner”, “” } }
      ),
      #”Extracted Stewards” = Table.TransformColumns (
        #”Replaced Empty Owners”,
        {
          “Stewards”,
          each Text.Combine ( List.Transform ( _, each Record.Field ( _, “LookupValue” ) ), “,” ),
          type text
        }
      ),
      #”Replaced Empty Stewards” = Table.ReplaceErrorValues (
        #”Extracted Stewards”,
        { { “Stewards”, “” } }
      ),
      #”Changed Type” = Table.TransformColumnTypes (
        #”Replaced Empty Stewards”,
        {
          { “CreatedByEmail”, type text },
          { “CreatedBy”, type text },
          { “ModifiedByEmail”, type text },
          { “ModifiedBy”, type text },
          { “webUrl”, type text },
          { “Modified”, type datetime },
          { “createdDateTime”, type datetime },
          { “Filename”, type text },
          { “Title”, type text },
          { “ContentType”, type text },
          { “CheckinComment”, type text },
          { “FileSizeBytes”, Int64.Type },
          { “ItemChildCount”, Int64.Type },
          { “FolderChildCount”, Int64.Type },
          { “DocIcon”, type text },
          { “Version”, type text }
        }
      ),
      #”Reordered Columns” = Table.ReorderColumns (
        #”Changed Type”,
        {
          “Filename”,
          “Title”,
          “Version”,
          “CheckinComment”,
          “Modified”,
          “ModifiedBy”,
          “ModifiedByEmail”,
          “CreatedByEmail”,
          “CreatedBy”,
          “createdDateTime”,
          “webUrl”,
          “ContentType”,
          “DocIcon”,
          “FileSizeBytes”,
          “ItemChildCount”,
          “FolderChildCount”
        }
      ),
      // unpivot for displaying
      #”Unpivoted Columns” = Table.UnpivotOtherColumns (
        #”Reordered Columns”,
        {},
        “Attribute”,
        “Value”
      ),
      #”Added Index” = Table.AddIndexColumn ( #”Unpivoted Columns”, “Index”, 1, 1, Int64.Type )
    in
      #”Added Index”
in
  Source
Power Query Formatter
Dashboard details

Conclusion

The solution described in this post does the trick. I have a nice page for reuse in all my Power BI dashboards, showing the version history and the document details for my end-users.

All in all it has been a nice experiment into the field of SharePoint API’s and PowerBI functions. I still hope for some hints that it can be much simpler.

2 thoughts on “Power BI version history from a SharePoint Document Library using Power Query”

  1. Hey, great article! I am trying to repeat your steps to get the version history from sharepoint displaying in power bi, but struggling a bit! Do you perhaps have a pbix file you could share with me if it’s not too much to ask?

    thank you,

    Scott

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *