Get versions from SharePoint

[Anonymous]

Hi guys,

There are other threads that contain this question, but I did not resolve the issue. So I'm here asking it again.

I need to get the versioning of my SharePoint list into Power BI. But the columns Versions has an error.

This is my current M code

let
    Source = SharePoint.Tables("https://<company>.sharepoint.com/sites/ca/<list>/", [ApiVersion = 15]),
    #"d121b816-bf85-4693-967a-2fe2f95407bd" = Source{[Id="d121b816-bf85-4693-967a-2fe2f95407bd"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"d121b816-bf85-4693-967a-2fe2f95407bd",{{"ID", "ID.1"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "Title", "ComplianceAssetId", "PredecessorsId", "PercentComplete", "AssignedToId", "AssignedToStringId", "TaskGroupId", "TaskGroupStringId", "DueDate", "RelatedItems", "WorkflowLink", "OffsiteParticipant", "OffsiteParticipantReason", "WorkflowOutcome", "WorkflowName", "Action Date", "Adverse Action Notic", "Request Date", "SBA #", "Disbursement Ins"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"StartDate", type datetime}, {"Created", type datetime}, {"Modified", type datetime}})
in
    #"Changed Type"

[Joerg]

I don't know where I did find this solution, but for me it works fine since years.

Query your list and then you can add a new column:
Xml.Tables(Web.Contents("https://=>your.sharepoint.com/sites=<",[RelativePath="/=>your subsite<=/_api/web/Lists/getbytitle(=>yourList<=)/items("&Text.From([Id])&")/versions"]))

[v-yingjl · Accepted solution]

Hi @Anonymous ,

I think it's not currently possible without some custom or third party integration.

You can try these two partial workarounds:

1. Export to excel

• Use the Export to Excel option in the List section of the ribbon (make sure the list view you export includes a modified column - thanks T6J2E5).
• Save the owssvr.iqy file and open with notepad
• Copy just the URL from the file and paste it back into your browser, adding "&IncludeVersions=TRUE"
• Save the XML file and open in Excel (or your favorite XML viewer), selecting the "As an XML table" open option.
• You'll have to delete the first few columns and rows as they contain the schema data but other than that you should have all the version history (I suggest you add the Version column to the view). You can sort by the Modified column to get a chronological change log of the entire list.

Refer: sharepoint-list-version 

2. Export to csv using PowerShell

# ******* Variables Section ******************
#Define these variables
$WebURL="http://sharepoint.crescent.com/sites/Sales/"
$ListName ="Invoice"
$ReportFile = "D:\Invoice_VersionHistory.csv"
# *********************************************

#delete file if exists
If (Test-Path $ReportFile)
 {
 Remove-Item $ReportFile
 }

#Get the Web and List
$Web = Get-SPWeb $WebURL
$List = $web.Lists.TryGetList($ListName)

 #Check if list exists
 if($List -ne $null)
 {
  #Get all list items
  $ItemsColl = $List.Items

  #Write Report Header
  Add-Content -Path $ReportFile -Value "Item ID, Version Lable, Created by, Created at, Title"

  #Loop through each item
  foreach ($item in $ItemsColl)
  {
   #Iterate each version
      foreach($version in $item.Versions)
       {
    #Get the version content
    $VersionData = "$($item.id), $($version.VersionLabel), $($version.CreatedBy.User.DisplayName), $($version.Created), $($version['Title'])"
    #Write to report
    Add-Content -Path $ReportFile -Value $VersionData
   }
  }
 }
Write-Host "Version history has been exported successfully!"​

Refer: How to export version history of SharePoint list items using powershell

After exporting to excel / csv file, you can get this file in power bi desktop.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[anon0326]

• Save the owssvr.iqy file and open with notepad - after doing this it's all in different language.  how can i change to english? 

[pranit828]

Hi @Anonymous 

Plaese check and fix the data type of the returned resultset or before loading it as a source.

This issue comes when the data type mismatches.

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit

[Anonymous]

@pranit828

How do I do that in my sharepoint list?