Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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
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.
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"]))
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
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.
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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |