Getting SharePoint List items with full history version?

[Nick]

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

[AP20202020]

I keep getting the below error

DataSource.Error: Web.Contents failed to get contents from 'https://aemocloud.sharepoint.com/sites/EnterprisePortfolioOfficeHub/EnterprisePortfolioOfficeHub/_ap...' (404): Not Found

[jdr4mcse]

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]

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?

[mohandar]

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]

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!

[bibinthomas]

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]

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

[mzzwtr]

Hi @Anonymous,

I suggest you to read these articles:

• Setting a scheduled refresh on a Dynamic Data Source in Power BI 
• Web.Contents(), M Functions And Dataset Refresh Errors In Power BI 
• Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

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:

• [Unable to combine data] accessing data sources that have privacy levels
• Information is needed in order to combine data

Hope this may help you.

Bye!

[nwr122]

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

[GabrielMacedo]

It worked fine here, thank you very much.

[harsh_bi_dev]

Hello @mzzwtr ,

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

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

Please help me out with this.

[mzzwtr]

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!

[harsh_bi_dev]

Hello @mzzwtr,

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

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:

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

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.

[mzzwtr]

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

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

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

Hope this may help you.

Bye!

[harsh_bi_dev]

Hello @mzzwtr,

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

Could you please help me out with that?

Thank you.

[mzzwtr]

Hi @harsh_bi_dev ,

have you tried to check the "AuthDialog_Checkbox_SkipTestConnection" option?

Let me know.

Bye!

[harsh_bi_dev]

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:

[NickTT]

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.

[harsh_bi_dev]

Hello @NickTT,

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

But thank you for the suggestions.

[Anonymous]

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