📊Power BI: SharePoint List Item Version History
HTML-код
- Опубликовано: 27 май 2024
- In this video, we will see how we can bring the Version History of our SharePoint List Items from SharePoint into Power BI.
To achieve that, first, we will connect to our SharePoint List. Then we will eliminate all the columns we do not need and keep only the ones we need. After having prepared our table we are going to use a custom query to invoke the Version History data from SharePoint into each and one of our item rows in the main table.
The code for the Query Invoke can be found here since I cannot paste the code in the description due to character limits on RUclips video descriptions: community.fabric.microsoft.co...
After that, we are going to invoke a custom function in our main table and use the query that we just created to call the SharePoint API and pass the information from each row into the API call to retrieve the version history for each row.
Enjoy!
0:00 Introduction to the topic of the video
0:50 Introduction to the data source of the demo and the problem
1:40 Showcasing how the result looks like
2:13 Connecting to the data source
3:15 Cleaning, formatting, and transforming the data
6:35 Creating the new custom query
7:00 Explaining the custom query M code
8:37 Invoke a Custom Function
9:36 Transforming the data from the custom function
13:55 Comparing the result with the one on SharePoint
15:12 Talking about working with complex/table-type columns
16:40 Conclusion and closing
If you want to learn more about Power BI, you are welcome to follow the channel and subscribe to the Power BI playlist here:
• Power Automate & Share...
Feel Free to follow me on other social media platforms as well:
linktr.ee/EneaLicaj
Buy me a pizza 🍕- bmc.link/enealicaj 
Наука
Getting the error: DataSource.Error: Web.Contents failed to get contents from ...... (404): Not Found.
Could you please help?
Hi, This solution results in a dynamic data source which cannot be refreshed by the Power BI service as a scheduled refresh. Is there a way around this?
Your timing is perfect! Thank you for posting this! Great helpful content.
absolute genius! Thanks for the video. Some things did look different as I was following through (not sure if we worked on different version of Power Query) but the process is the same!
Thanks, happy it helped! Feel free to subscribe to the channel, it will help me as well :)
This works fantastically on Power BI Desktop, but I'm having issues with credentials on a published version of the report on Power BI Service. Seems like the Web data source (from the Custom Function) isn't liking my organizational level credentials, even though the same settings are used on Desktop. Any ideas how to fix this? Breaks any scheduled restarts I've got set.
Did you find a solution? I'm runing into the exact same issue ..
Yes! On the published version, turn off Test Connection, and it works perfectly. I think custom functions can't be tested by Power BI service.@@teun9698
Great vid. Thanks for sharing.
Great Tutorial! Thanks a lot!
Thanks! Subscribe for more if you like :)
This solution does not allow to refresh the report in PowerBi Services i guess this is due to the dynamic url. Any work around?
"You can't schedule refresh for this semantic model because the following data sources currently don't support refresh:
Data source for Query1"
Thank you for this video! I was planning on designing a changelog flow to record each change over time, but this should help avoid the additional build in power automate. As a question, when I try to run the custom function, my Version History table comes back as Error. Trying to invoke the function manually for testing, I get a response that the remote name couldn't be resolved. Do you have any suggestion as to how this can be fixed?
Hi! Maybe you wrote something wrong? Looks like its not finding anything
This helped me, thank you. Do you know where to get the modified by column? I can't seem to find it.
Happy I could help! The modified by column is called "Editor"
@@Enea Great thank you. Last question, do you know how I can replicate the view in sharepoint to only show what column or data changed. In your example you can compare all versions and see what has changed but is it possible to have a summary view or a column that identified what changed between the versions? Same as the view in SP under version history.
@@Jacowduplessis you want to have that view in SharePoint or in Power BI?
@@Enea I want to show in Power BI what column has been changed. In Sharepoint it is easy cause it shows you the version and what changed from the old version to the new one. I want to show the same thing in Power BI. I want to show all the changes that were made on a specific day. In order to do that I assume I need to compare the previous version with the current version and highlight the changes - unless there is data in the query that can already show me what columns where modified?
I don't have ParentWebURL...?
Oh weird, I had ParentList, I have to expand that, and then I had it in there... The way you show it in the video, it looks like it's in the items drop down, but you expand it and then expand again...
do you show somewhere how do you built the function you copy and paste ? I don't see those steps.
Read the video description
Hi thank you for this video, it has been very useful and enabled me to extract version history to feed a Power BI report. I have hit a limitation however; the code you shared seems to return a maximum of 51 history records. Is there a way to override this and return either all history or all history to a specified date? Thank you so much.
Check your List Settings within SharePoint, within a List top right cog and click List Settings. There's a setting for amount of versions to keep (per row I think).
I dont have parent web url as mentioned in 4:06 . Is that step needed
Hi I too didn't see that option did you find the solution
Expand ParentList first