Finding date of column change in SharePoint 2010 versioned list data

[PamDeeVee]

Hi, I am a PowerBI newbie trying to create a PowerBI dashboard for a SharePoint 2010 task list, which has versioning turned on. I would like to include some data on:

1.  how long tasks are open (from Date Created until the date that Status column was changed to Completed)

2. how many tasks have been Completed in a month

Can someone outline the steps to expose the version data and how to report on the version/modified date that the status changed to Completed.

I think I need to use the Unpivot columns function but it seems to take an age to run this, and with every subsequent action.

The list has over 600 items, and about 25 columns. Versioning has only recently been turned on so there won't be much version data.

Thanks for assistance with this, or any useful links you've found to give me more direction.

[v-yuta-msft]

Hi PamDeeVee,

Are you trying to embed your power bi report on sharepoint? You said "create a PowerBI dashboard for a SharePoint 2010 task list, which has versioning turned on.", could you give more details about your data and expected result? In addtion, regarding how to create or embed a power bi report to sharepoint, please refer to this documentation: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/sharepoint-scenario-build-report.

Regards,

Jimmy Tao

[PamDeeVee · Accepted solution]

Thanks for your reply Jimmy. I was trying to get data about the dates that items changed status so I could present that in a dashboard. We used the link following to create a new data source from xml data created from the SharePoint table including all version data - https://community.powerbi.com/t5/Desktop/Getting-SharePoint-List-items-with-full-history-version/m-p... .  Just don't include multi value columns or the version history won't work. Then I grouped the new data using the status column and the mininum Modified Date to get the date the status changed. Then I merged this data source (and date column) back into the fulll task list datato create the dashboard.