Accessing list items (with history) in SharePoint online

[Mark Teviotdale]

Hi Team,

I have a SharePoint list that is a small sales pipeline. I would like to either export the list items with version metadata so I can hook it up to Power BI and analyse it. There is a drop down that controls the item through the sales pipeline so exporting the history would give me metrics on how many items made it all the way through the sales pipeline. Is this possible to do? I have tried exporting via excel or connecting directly to the list in Power BI but can't view the version controls change records.

Another approach would be to setup workflow that every time a list item was created/edited it dropped the current item into a duplicate historical list. Thoughts on this approach would be apricated if above is not possible. 

Thanks

Mark

[#2]

Hi Mark,

I might not be understanding all the requirements but maybe rather than creating all those duplicate records perhaps you could do a few of the following:

1) Maybe have an extra column that would track last status before close? That way you know how far it got in the pipeline before it was closed?

2) If for some reason you wanted that data outside of the pipeline list, why not have a workflow create an item in that history list for key events versus on every *touch* (edit).

3) For those type of business process solutions I find myself leveraging calculated columns for certain activities which help out reporting. Not sure if this would be applicable in your scenario? Maybe tracking time between open and close - leverage "Created" for initial and then set the close column value when that action happens. Then perhaps a calculated column that does the math to figure out the difference between created & the custom closed column.

Maybe one of these will give a little inspiration? Happy SharePointing!

-Jared

[#3]

Hi Jared 

Thanks very much for your reply and your suggestions, I'm pretty lose regarding requirements on this one! I think your idea about adding another column in the list would work well with regrads to storing the previous pipeline status. I'm assuming I need to setup a workflow for the value to be copied ? 

Yes I have been looking into using the created and modified columns to look at how long items have been in the pipeline. But the data is noisey on the created value due to migration from on-prem to SharePoint online so I will have to apply some filerting.

Thanks for you help,

Cheers

Mark

[#4]

Hi Mark,

Yes that's exactly right - you'd have a workflow that runs when the Item is edited and it can check the dropdown value. It would then write a value to that new custom column you'll create.

Sorry to hear that you're dealing with some bad data. Most of the tools today should've been able to maintained the dates, but it sounds like that ship has sailed.

Definitely add to this thread if you hit anything else while building.

-Jared

[#5]

Hi Jared,

I know this is an old thread but I'm hoping you can help me.  I'm fairly new to SharePoint. 

I have created a workflow in my document folder and my team is asking for a workflow history or some sort of view that will show who is next to approve a file.  I read yours and Mark's thread and adding the column may seem like it can help. 

I know how to add a column but what I'm not sure is what i need to select to create the column and show who is next on the approving workflow.

I hope this makes sense.

Thank you

Paola