Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nick
Frequent Visitor

Getting SharePoint List items with full history version?

Hi,

 

We do have a SharePoint list to manage Fleet inventory of asset: mainly their attribution (who own it) and their status (active, lost, broken, etc.).

We did activated the SharePoint Version Control and I would like to use this information to run histroical report overtime around such assets status and ownership.

Right now, the only solution I found is to eitheir take a snapshot of the full list on a regular basis into another datasource.

Is there anyway to get such history/version data directly avoiding wruting such separate datasource?

 

Thansk in advance,

Nick

44 REPLIES 44
jdr4mcse
New Member

Is there a way to limit the amount of version returned by adding a date range into the api code? If so, do you know what code to add to include a date range? 

MD1
New Member

Hello all,

I am trying to run this query but I am getting below error. Do you know what can be the reason of it?

Capture.PNG

mohandar
New Member

how can we get full version history of all items? I am trying to create report based on the no of days an item stayed in a particular status, any help will be appreciated.

JensG
Advocate II
Advocate II

Hi

 

I like to share a function/query which will help to get the Version details via SharePoint API for a list item.

 

Function/Query:
let
    Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let
        Source = Xml.Tables(Web.Contents(Text.Combine({
    VersionsRelevantSharePointLocation,
    "/_api/web/Lists/getbytitle('",
    VersionsRelevantSharePointListName ,
    "')/items(",
    Text.From(VersionsRelevantItemID),
    ")/versions"}
    ))),
        entry = Source{0}[entry],
        #"Removed Other Columns2" = Table.SelectColumns(entry,{"content"}),
        #"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns2", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),
        #"Expanded content1" = Table.ExpandTableColumn(#"Expanded content", "content", {"properties"}, {"properties"}),
        #"Expanded properties" = Table.ExpandTableColumn(#"Expanded content1", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})
    in
        #"Expanded properties"
in
    Source

3 parameters are required for the function:

VersionsRelevantSharePointLocation => https://<yourAddress>.sharepoint.com/sites/<yourSite>

VersionsRelevantSharePointListName => Your SharePoint List Name

VersionsRelevantItemID => SharePoint list item ID

when you invoke this Function to an SharePoint list you get the table with all related versions for that item.
This can be further expanded to get to the full set of columns from that list.

 

Hope this helps. 

 

Have Fun!

Hi @JensG,

 

The query is to get the version history of a single List Item (ID). How do we get the version history of all the list items.

Anonymous
Not applicable

please share how do we apply schedule refresh on this dynamic data source, 

Hi @Anonymous,

 

I suggest you to read these articles:

 

To set a scheduled refresh using dynamic data source, I've edit the function of @JensG in this way:

 

 

let
    Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let
        Source = Xml.Tables(Web.Contents(
    "https://YourAddress.sharepoint.com/sites/",
    [RelativePath = VersionsRelevantSharePointLocation & "/_api/web/Lists/getbytitle('" & VersionsRelevantSharePointListName & "')/items(" & Text.From(VersionsRelevantItemID) & ")/versions"]
    )),
        entry = Source{0}[entry],
        #"Removed Other Columns2" = Table.SelectColumns(entry,{"content"}),
        #"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns2", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),
        #"Expanded content1" = Table.ExpandTableColumn(#"Expanded content", "content", {"properties"}, {"properties"}),
        #"Expanded properties" = Table.ExpandTableColumn(#"Expanded content1", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})
    in
        #"Expanded properties"
in
    Source

 

 

 

3 parameters are required for the function:

  1. VersionsRelevantSharePointLocation => The subdirectory of your SharePoint site (is what come after ...sites/)
  2. VersionsRelevantSharePointListName => Your SharePoint List Name
  3. VersionsRelevantItemID => SharePoint list item ID

 

 

Then, once you have published your report into Power BI service, I raccomend to read these articles:

 

 

Hope this may help you.

 

Bye!

Thanks to @mzzwtr and @JensG for sharing the function, managed to get this working for my use case. Much appreciated folks

It worked fine here, thank you very much.

Hello @mzzwtr ,

Thanks for this answer. I'm facing an issue with the solution you provided in Power BI Service as below.

harsh_bi_dev_0-1662672077653.png

Please know that it is working fine in the Power BI Desktop but not on Power BI Service.

Please help me out with this.

Hi @harsh_bi_dev ,

 

could you show me how you connected the SharePoint Online List data source to your Power BI dashboard?

 

I followed this guide:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list 

 

Another question, could you show me what steps you took to publish your Power BI dashboard into your Power BI Service workspace?

 

I followed this guide:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-upload-desktop-files


Hope this may help you.

 

Bye!

Hello @mzzwtr,

 

I'm getting the SharePoint List Item version history using the method mentioned in your initial reply as below:

harsh_bi_dev_0-1662699646552.png

It is creating another datasource as Web and when I'm trying to connect to it, it is showing me invalid credentials in Power BI Service. 

 

Please know that I'm adding new column by invoking custom function as below:

harsh_bi_dev_1-1662699787746.png

And if I go to datasource settings it will show me 3 datasources as below:

harsh_bi_dev_2-1662700012468.png

Please know that I'm using Gateway to connect to these datasources and it is showing the error of invalid credentials in Power BI Service but working fine in Power BI Desktop.

Hope this information will help you in understanding the issue I'm facing.

 

Let me know if you need anything else as well.

 

Thank you for the quick turn around.

Hi @harsh_bi_dev ,

 

I think the problem is related to the fact that you used a Gateway to connect your data source; since the data is in the Cloud, a Gateway is not required, as reported in the Microsoft documentation:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem 

 

In order to connect my Sharepoint Online List, I followed this documentation:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list 

 

After that, I've published my Power BI into my workspace wollofing this documentation:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-upload-desktop-files

 

Once you've published your Power BI in your workspace, you need to follow these simple steps:

 

1) open the settings of the Dataset (data source) published in the workspace

 

mzzwtr_0-1662721315131.png

 

 

 

2) From the "Data Source Credentials" section, click "Edit Credential" related to the "Web" data source

 

mzzwtr_1-1662721379941.png

 

 

3) Set the "Web" access settings as shown in the image 

 

mzzwtr_2-1662721433217.png

 

 

Hope this may help you.

 

Bye!

Hello @mzzwtr,

 

If I'm configuring without gateway then also it is giving me same error. Please see the below screenshot.

harsh_bi_dev_0-1662741191778.png

 

Could you please help me out with that?

 

Thank you.

 

Hi @harsh_bi_dev ,

 

have you tried to check the "AuthDialog_Checkbox_SkipTestConnection" option?

 

Let me know.

 

Bye!

Hello @mzzwtr,

 

Yes, I tried that option and if I check it the it is giving me an error at the time of refresh as below:

 

harsh_bi_dev_0-1662754852811.png

 

Correct... Sharepoint feeds no longer need Gateways. So don't set it up and it should work else you will get an error and the data wont refresh.

 

I however did run into an issue still with getting errors pulling the data. I worked around the issue by creating a DataFlow to get the version info and the updating the PowerBI model to pull data from that DataFlow instead. Then defining "Allow Combining data from multiple sources." in the Project options of the DataFlow. I haven't had any issues since and I make this my standard approach for any version info in PowerBI from SharePoint Online.

 

NickTT_1-1662731823502.png

 

Hello @NickTT,

I can't create a DataFlows as I'm working on MyWorkspace.

 

But thank you for the suggestions.

Anonymous
Not applicable

THANKS A LOT, ❤️ YOU SAVED MY DAY 🙂 YOUR ABOVE CODE WORS FINE

This is awesome! I just got a request if this was possible!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.