Change Over time data model to capture Bucket change and completed items

[rixmcx59]

Hello all, I am working with planner data, I have a weekly scheduled flow that moves planner data to a SharePoint list, before the new data is added to the list, the existing data is moved to a history list (copied) then deleted from the main list.

What I will end up with is current data report date (the item "Created" date) and History list report date (the original "Created" date) basically the data is being stamped with the flow run date. For example, in 2 months I wll have 8 export dates for the records in the history list

What I need to capture is "Bucket name change" and how long the task spent in the bucket, and "Checklist item completed change". 

What I am thinking is, to capture the reporting week that the change occurred, since I don't have a daily export.

I have looked at several change over time solutions and it appears I will need a calendar/date table. 

Any feedback would be greatly appreciated

[Anonymous]

Hi @rixmcx59,

I'd like to suggest you merge two lists and add a custom column to remark the source, then you can simply use DAX expressions to analyze these two types of records based on the 'source table' field group.

Regards,

Xiaoxin Sheng

[rixmcx59]

Hello all, Maybe I should reword the post, what would be the ideal data model to capture change over time?

1. A single SharePoint list with all data and changes will be appended (UID to match on) 1 table in Power query, Plus a Date table.

2. Two SharePoint lists, Primary list and history list, then Merge or Append in Power Query plus a Date table?

What I have now is #2, which is not going to work, the history list will grow to quickley because it's a copy of the Primary list and most records have no change on a weekly basis.

My Plan is to modify the workflow to retain only records that have changed and append them to the primary list or, back to my original question, add them to the history list.

Thanks