cancel
Showing results for 
Search instead for 
Did you mean: 
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

40 REPLIES 40
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 I
Advocate I

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!

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

Hi @Ra20241295,

 

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!

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.

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

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

Anonymous
Not applicable

Awsome! You made my day. Thanks a lot. 🙂 

Anonymous
Not applicable

@JensG cool that you posted a function/query. Unfortunately, it does not work for me. Any ideal how I can fix that without too much effort. The error says Expression.Error: The access to the ressourc is prohibites (translated from my version: "Der Zugriff auf die Ressource ist untersagt."). I checked that I am properly logged in and check the list that there are no permission restrictions. At least I cannot see any. I am assuming that the account I am logged into PowerBI must also have access the SharePoint list or is there anything else I need to consider?

Are you sure that the links in your function are still valid?

http://schemas.microsoft.com/ado/2007/08/dataservices/metadata
http://schemas.microsoft.com/ado/2007/08/dataservices

 Appreciate, if someone has a clue what is wrong with it?

 

Thank you so much

Hi @Anonymous  

I had to add my SharePoint URL to this list of trusted sites in my Internet Options to overcome that error-message.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi udobausc, 

 

sorry I don´t have seen this error on my end yet.

I´m logged in with my O365 Users in Power BI, same user who has access to the SP stuff.

Anyhow sometimes Power BI is promting to enter credentials where I can select differrent methods. But I always use the same user.

 

regarding the old links, it is when I expand some columns this is returned as field name and I have just kept it as it comes from the SP API 🙂


it might be worth to check if the underlying link is working in your browser when you replace the values in <>:

<VersionsRelevantSharePointLocation>/_api/web/Lists/getbytitle('<VersionsRelevantSharePointListName>')/items(<VersionsRelevantItemID>)/versions

 

have fun 

 

 

 

 

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors