Your Software Development Lifecycle Data Template

let
    Source = AzureDevOps.Views("[Your Organization Name]", "[Your Project Name]", [ApiVersion = 2]),
    View_Table = Source{[Id="[Your Analytics View ID]",Name="[Your Analytics View Name]"],Kind="View"}[Data],

    // Add a unique key for each daily snapshot
    AddSnapshotKey = Table.AddColumn(View_Table, "SnapshotKey", each Text.Combine({Text.From([Work Item Id], "en-US"), Text.From([Date], "en-US")}), type text),
    
    // Lag the state column to detect changes
    AddPreviousState = Table.Buffer(Table.Sort(AddSnapshotKey,{{"Work Item Id", Order.Ascending}, {"Date", Order.Ascending}})),
    ShiftedList = {null} & List.RemoveLastN(AddPreviousState[State],1),
    AddLagColumn = Table.ToColumns(AddPreviousState) & {ShiftedList},
    FinalTable = Table.FromColumns(AddLagColumn, Table.ColumnNames(AddPreviousState) & {"PreviousState"}),
    
    // Filter to rows where the state actually changed or it's the creation event
    FilterStateChanges = Table.SelectRows(FinalTable, each [State] <> [PreviousState] or [PreviousState] = null),

    // --- Event 1: Work Item Created ---
    WorkItemCreated = Table.SelectRows(FilterStateChanges, each [PreviousState] = null),
    AddActivity_Created = Table.AddColumn(WorkItemCreated, "ActivityName", each "Work Item Created"),
    SelectCols_Created = Table.SelectColumns(AddActivity_Created, {"Work Item Id", "ActivityName", "Created Date", "Work Item Type", "State", "Priority", "Assigned To", "Area Path"}),
    RenameCols_Created = Table.RenameColumns(SelectCols_Created,{{"Created Date", "EventTime"}, {"Area Path", "TeamName"}}),

    // --- Infer events from State Changes ---
    StateChangeEvents = Table.SelectRows(FilterStateChanges, each [PreviousState] <> null),
    AddActivity_StateChange = Table.AddColumn(StateChangeEvents, "ActivityName", each 
        if [State] = "Approved" then "Work Item Approved"
        else if [State] = "Committed" or [State] = "Active" or [State] = "In Progress" and [PreviousState] <> "In QA" then "Development Started"
        else if [State] = "Resolved" then "Development Completed"
        else if [State] = "In QA" or [State] = "Testing" then "QA Testing Started"
        else if [State] = "In Progress" and [PreviousState] = "In QA" then "QA Testing Failed"
        else if [State] = "QA Approved" then "QA Testing Completed"
        else if [State] = "In UAT" then "UAT Started"
        else if [State] = "UAT Approved" then "UAT Approved"
        else if [State] = "Deployed" then "Deployed to Production" // Assuming a state reflects deployment
        else if [State] = "Closed" then "Work Item Closed"
        else if [State] = "Done" then "Work Item Closed"
        else if [State] = "Removed" or [State] = "Cancelled" then "Work Item Cancelled"
        else null),
    FilterNullActivities = Table.SelectRows(AddActivity_StateChange, each ([ActivityName] <> null)),
    SelectCols_StateChange = Table.SelectColumns(FilterNullActivities, {"Work Item Id", "ActivityName", "Date", "Work Item Type", "State", "Priority", "Assigned To", "Area Path"}),
    RenameCols_StateChange = Table.RenameColumns(SelectCols_StateChange,{{"Date", "EventTime"}, {"Area Path", "TeamName"}}),

    // --- Combine all event tables ---
    CombinedEvents = Table.Combine({RenameCols_Created, RenameCols_StateChange}),
    
    // --- Placeholder Events Not Captured by State Changes ---
    // These activities require different data sources (OData/API) and are not reliably captured via Work Item history alone.
    // Placeholder logic is omitted as it would be speculative.

    // --- Final Formatting ---
    RenameCaseID = Table.RenameColumns(CombinedEvents,{{"Work Item Id", "DevelopmentItem"}}),
    AddSourceSystem = Table.AddColumn(RenameCaseID, "SourceSystem", each "Azure DevOps"),
    AddLastUpdate = Table.AddColumn(AddSourceSystem, "LastDataUpdate", each DateTime.LocalNow()),
    ChangeTypes = Table.TransformColumnTypes(AddLastUpdate,{{"DevelopmentItem", type text}, {"EventTime", type datetime}, {"LastDataUpdate", type datetime}}),

    // Final column selection and ordering
    SelectFinalColumns = Table.SelectColumns(ChangeTypes, {"DevelopmentItem", "ActivityName", "EventTime", "SourceSystem", "LastDataUpdate", "Assigned To", "Work Item Type", "State", "Priority", "TeamName"})

in
    SelectFinalColumns

# =============================================================================
# Configuration Variables
# =============================================================================
$organizationUrl = "https://dev.azure.com/[Your-Organization-Name]"
$projectName = "[Your-Project-Name]"
$personalAccessToken = "[Your-Personal-Access-Token]"
$startDate = "2023-01-01"
$endDate = "2023-06-30"
$outputCsvPath = "C:\temp\ado_process_mining_log.csv"

# =============================================================================
# Script Body
# =============================================================================

# --- Helper function to create a standardized event object ---
function New-EventLogEntry {
    param (
        $DevelopmentItem, $ActivityName, $EventTime, $SourceSystem, $LastDataUpdate,
        $EndTime, $AssignedTo, $WorkItemType, $State, $Priority, $TeamName, $DevelopmentCycleTime, $IsRework
    )
    return [PSCustomObject]@{ 
        DevelopmentItem = $DevelopmentItem; ActivityName = $ActivityName; EventTime = $EventTime; SourceSystem = $SourceSystem; 
        LastDataUpdate = $LastDataUpdate; EndTime = $EndTime; AssignedTo = $AssignedTo; WorkItemType = $WorkItemType; State = $State; 
        Priority = $Priority; TeamName = $TeamName; DevelopmentCycleTime = $DevelopmentCycleTime; IsRework = $IsRework 
    }
}

# --- Main script execution ---
try {
    $startTime = Get-Date
    Write-Host "Starting Azure DevOps data extraction at $startTime"

    # Base64 encode the PAT for the authorization header
    $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes((":{0}" -f $personalAccessToken)))
    $headers = @{Authorization = ("Basic {0}" -f $base64AuthInfo)}

    # Timestamp for the extraction run
    $lastDataUpdate = Get-Date -Format 'yyyy-MM-ddTHH:mm:ssZ'
    
    # Initialize list to hold all event log entries
    $eventLog = [System.Collections.Generic.List[object]]::new()

    # Step 1: Query for all work item IDs in the specified date range using WIQL
    Write-Host "Querying for work items between $startDate and $endDate..."
    $wiqlQuery = @{
        query = "SELECT [System.Id] FROM workitems WHERE [System.TeamProject] = '$projectName' AND [System.ChangedDate] >= '$startDate' AND [System.ChangedDate] <= '$endDate'"
    }
    $wiqlUrl = "$organizationUrl/$projectName/_apis/wit/wiql?api-version=6.0"
    $wiqlResponse = Invoke-RestMethod -Uri $wiqlUrl -Method Post -Body (ConvertTo-Json $wiqlQuery) -ContentType "application/json" -Headers $headers
    $workItemIds = $wiqlResponse.workItems.id
    Write-Host ("Found {0} work items to process." -f $workItemIds.Count)

    # Step 2: Iterate through each work item and get its full revision history
    $processedCount = 0
    foreach ($id in $workItemIds) {
        $processedCount++
        Write-Host ("Processing Work Item {0} ({1}/{2})..." -f $id, $processedCount, $workItemIds.Count)

        $revisionsUrl = "$organizationUrl/_apis/wit/workitems/$id/revisions?`$expand=relations&api-version=6.0"
        $revisionsResponse = Invoke-RestMethod -Uri $revisionsUrl -Method Get -Headers $headers
        
        $previousRevision = $null
        $stateHistory = [System.Collections.Generic.List[string]]::new()

        # Loop through each revision to generate events
        foreach ($revision in $revisionsResponse.value) {
            $currentFields = $revision.fields
            $previousFields = $previousRevision.fields
            $eventTime = [datetime]$currentFields.'System.ChangedDate'
            $currentState = $currentFields.'System.State'

            # Common attributes for this revision
            $commonAttribs = @{
                DevelopmentItem = $id
                EventTime = $eventTime.ToString('yyyy-MM-ddTHH:mm:ssZ')
                SourceSystem = 'Azure DevOps'
                LastDataUpdate = $lastDataUpdate
                AssignedTo = $currentFields.'System.AssignedTo'.displayName
                WorkItemType = $currentFields.'System.WorkItemType'
                State = $currentState
                Priority = $currentFields.'Microsoft.VSTS.Common.Priority'
                TeamName = $currentFields.'System.TeamProject'
            }
            
            # --- Activity Detection Logic ---

            # Work Item Created (always the first revision)
            if ($revision.rev -eq 1) {
                $isRework = $false
                $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName 'Work Item Created' -IsRework $isRework))
                $stateHistory.Add($currentState)
            } else {
                $previousState = $previousFields.'System.State'
                
                # Detect rework if we re-enter a state
                $isRework = $stateHistory.Contains($currentState)

                # State Change Activities
                if ($currentState -ne $previousState) {
                    $transition = "$previousState -> $currentState"
                    $activityName = switch ($transition) {
                        'New -> Approved' { 'Work Item Approved' }
                        'Approved -> Active' { 'Development Started' }
                        'Approved -> In Progress' { 'Development Started' }
                        'Active -> Resolved' { 'Development Completed' }
                        'In Progress -> Resolved' { 'Development Completed' }
                        'Resolved -> In QA' { 'QA Testing Started' }
                        'Resolved -> Testing' { 'QA Testing Started' }
                        'In QA -> Active' { 'QA Testing Failed' }
                        'Testing -> In Progress' { 'QA Testing Failed' }
                        'In QA -> Ready for UAT' { 'QA Testing Completed' }
                        'Testing -> UAT Approved' { 'QA Testing Completed' }
                        'Ready for UAT -> In UAT' { 'UAT Started' }
                        'In UAT -> UAT Approved' { 'UAT Approved' }
                        # Handle final states
                        default {
                           if ($currentState -eq 'Closed' -or $currentState -eq 'Done') { 'Work Item Closed' }
                           elseif ($currentState -eq 'Removed' -or $currentState -eq 'Cancelled') { 'Work Item Cancelled' }
                        }
                    }
                    if ($activityName) {
                        $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName $activityName -IsRework $isRework))
                    }
                    $stateHistory.Add($currentState)
                }
            }

            # Artifact Relation Activities (Pull Requests, Builds, Deployments)
            # This checks for newly added relations compared to the previous revision
            $currentRelations = $revision.relations
            $previousRelations = $previousRevision.relations
            if ($currentRelations) {
                $newRelations = Compare-Object -ReferenceObject $previousRelations -DifferenceObject $currentRelations -Property url -PassThru | Where-Object { $_.SideIndicator -eq '=>' }
                foreach ($rel in $newRelations) {
                    # Pull Request Created/Completed
                    if ($rel.rel -eq 'ArtifactLink' -and $rel.attributes.name -eq 'Pull Request') {
                        # Note: A more robust check would query the PR URL for its actual creation/completion date
                        $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName 'Pull Request Created' -IsRework $false))
                        # Placeholder for completion, real logic would track PR status over time
                        # For simplicity, we assume another event represents completion.
                    }
                    # Build Succeeded
                    if ($rel.rel -eq 'ArtifactLink' -and $rel.attributes.name -eq 'Build') {
                         $eventLog.Add((New-EventLogEntry @commonAttribs -ActivityName 'Build Succeeded' -IsRework $false))
                    }
                    # Deployed to Production
                     if ($rel.rel -eq 'ArtifactLink' -and $rel.attributes.name -eq 'Integrated in release environment') {
                         if ($rel.url -like '*Production*') { # Simple check for production deployment
                            $eventLog.Add((New-EventLogEntry @commonattribs -ActivityName 'Deployed to Production' -IsRework $false))
                         }
                    }
                }
            }

            $previousRevision = $revision
        }
    }

    # Step 3: Post-processing to sort events and calculate EndTime/CycleTime
    Write-Host "Sorting event log and calculating derived attributes..."
    $sortedEvents = $eventLog | Sort-Object DevelopmentItem, EventTime
    
    $finalLog = for ($i = 0; $i -lt $sortedEvents.Count; $i++) {
        $currentEvent = $sortedEvents[$i]
        $nextEvent = if ($i + 1 -lt $sortedEvents.Count) { $sortedEvents[$i + 1] } else { $null }
        
        # Calculate EndTime
        if ($nextEvent -and $currentEvent.DevelopmentItem -eq $nextEvent.DevelopmentItem) {
            $currentEvent.EndTime = $nextEvent.EventTime
        } else {
            $currentEvent.EndTime = $null
        }
        $currentEvent # Output to the final log
    }

    # Step 4: Export to CSV
    Write-Host ("Exporting {0} events to {1}..." -f $finalLog.Count, $outputCsvPath)
    $finalLog | Export-Csv -Path $outputCsvPath -NoTypeInformation -Encoding UTF8

    $endTime = Get-Date
    Write-Host "Extraction completed successfully at $endTime"
    Write-Host ("Total execution time: {0}" -f ($endTime - $startTime))
}
catch {
    Write-Error "An error occurred during extraction: $_"
    exit 1
}

import os
import pandas as pd
from datetime import datetime, timezone
from azure.devops.connection import Connection
from msrest.authentication import BasicAuthentication

class=class="hl-string">"hl-comment"># --- Configuration: Replace with your values ---
ORGANIZATION_URL = class="hl-string">'https://dev.azure.com/[Your Organization]'
PROJECT_NAME = class="hl-string">'[Your Project Name]'
PERSONAL_ACCESS_TOKEN = class="hl-string">'[Your Personal Access Token]'

class=class="hl-string">"hl-comment"># --- Extraction Parameters ---
class=class="hl-string">"hl-comment"># Date range for work items to fetch (based on creation date)
START_DATE = class="hl-string">'2023-01-01'
END_DATE = class="hl-string">'2023-12-31'

class=class="hl-string">"hl-comment"># Work item types to include in the extraction
WORK_ITEM_TYPES = [class="hl-string">'User Story', class="hl-string">'Bug', class="hl-string">'Feature']

class=class="hl-string">"hl-comment"># --- State Mapping: Customize this to match your project's workflow ---
class=class="hl-string">"hl-comment"># Maps a work itemclass="hl-string">'s 'State' field to a standardized Activity Name
STATE_MAP = {
    class="hl-string">'New': class="hl-string">'Work Item Created',
    class="hl-string">'Approved': class="hl-string">'Work Item Approved',
    class="hl-string">'Committed': class="hl-string">'Development Started',
    class="hl-string">'Active': class="hl-string">'Development Started',
    class="hl-string">'In Progress': class="hl-string">'Development Started',
    class="hl-string">'Resolved': class="hl-string">'Development Completed',
    class="hl-string">'Ready for Test': class="hl-string">'Development Completed',
    class="hl-string">'In QA': class="hl-string">'QA Testing Started',
    class="hl-string">'Testing': class="hl-string">'QA Testing Started',
    class="hl-string">'QA Approved': class="hl-string">'QA Testing Completed',
    class="hl-string">'Ready for UAT': class="hl-string">'QA Testing Completed',
    class="hl-string">'In UAT': class="hl-string">'UAT Started',
    class="hl-string">'UAT Approved': class="hl-string">'UAT Approved',
    class="hl-string">'Ready for Release': class="hl-string">'UAT Approved',
    class="hl-string">'Done': class="hl-string">'Work Item Closed',
    class="hl-string">'Closed': class="hl-string">'Work Item Closed',
    class="hl-string">'Removed': class="hl-string">'Work Item Cancelled',
    class="hl-string">'Cancelled': class="hl-string">'Work Item Cancelled'
}

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    print(class="hl-string">"Connecting to Azure DevOps...")
    credentials = BasicAuthentication(class="hl-string">'', PERSONAL_ACCESS_TOKEN)
    connection = Connection(base_url=ORGANIZATION_URL, creds=credentials)
    wit_client = connection.clients.get_work_item_tracking_client()
    git_client = connection.clients.get_git_client()
    build_client = connection.clients.get_build_client()

    print(fclass="hl-string">"Fetching work items from {START_DATE} to {END_DATE}...")
    wiql_query = fclass="hl-string">"""
        SELECT [System.Id]
        FROM workitems
        WHERE [System.TeamProject] = '{PROJECT_NAME}'
        AND [System.WorkItemType] IN ({', '.join([f"'{t}'" for t in WORK_ITEM_TYPES])})
        AND [System.CreatedDate] >= '{START_DATE}'
        AND [System.CreatedDate] <= '{END_DATE}'
        ORDER BY [System.Id]
    """

    wiql_results = wit_client.query_by_wiql({class="hl-string">'query': wiql_query}).work_items
    if not wiql_results:
        print(class="hl-string">"No work items found for the specified criteria.")
        return

    work_item_ids = [item.id for item in wiql_results]
    print(fclass="hl-string">"Found {len(work_item_ids)} work items. Fetching details and history...")

    all_events = []
    last_data_update = datetime.now(timezone.utc).isoformat()

    class=class="hl-string">"hl-comment"># Process work items in batches to avoid API limits
    batch_size = 200
    for i in range(0, len(work_item_ids), batch_size):
        batch_ids = work_item_ids[i:i + batch_size]
        work_items = wit_client.get_work_items(ids=batch_ids, expand=class="hl-string">'All')
        
        for item in work_items:
            events = process_work_item(item, wit_client, git_client, build_client, last_data_update)
            all_events.extend(events)
        print(fclass="hl-string">"Processed batch {i // batch_size + 1}...")

    if not all_events:
        print(class="hl-string">"No events were generated. Check your state mappings and data.")
        return

    print(class="hl-string">"Creating event log DataFrame...")
    df = pd.DataFrame(all_events)
    df = df.sort_values(by=[class="hl-string">'DevelopmentItem', class="hl-string">'EventTime'])
    
    class=class="hl-string">"hl-comment"># Calculate EndTime (start of next event for the same case)
    df[class="hl-string">'EndTime'] = df.groupby(class="hl-string">'DevelopmentItem')[class="hl-string">'EventTime'].shift(-1)
    
    class=class="hl-string">"hl-comment"># Final column selection and ordering
    df[class="hl-string">'SourceSystem'] = class="hl-string">'Azure DevOps'
    df = df[[class="hl-string">'DevelopmentItem', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'EndTime', class="hl-string">'AssignedTo', class="hl-string">'WorkItemType', 
             class="hl-string">'State', class="hl-string">'Priority', class="hl-string">'TeamName', class="hl-string">'IsRework', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate']]

    output_filename = class="hl-string">'azure_devops_event_log.csv'
    df.to_csv(output_filename, index=False)
    print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

def process_work_item(item, wit_client, git_client, build_client, last_data_update):
    events = []
    case_id = item.id
    work_item_type = item.fields.get(class="hl-string">'System.WorkItemType')
    team_name = item.fields.get(class="hl-string">'System.AreaPath')
    priority = item.fields.get(class="hl-string">'Microsoft.VSTS.Common.Priority')

    revisions = wit_client.get_revisions(work_item_id=case_id, expand=class="hl-string">'Fields')
    
    class=class="hl-string">"hl-comment"># Track states to detect rework
    seen_states = set()
    
    class=class="hl-string">"hl-comment"># Revision 0 / Initial Creation
    rev0 = revisions[0]
    initial_state = rev0.fields.get(class="hl-string">'System.State')
    events.append({
        class="hl-string">'DevelopmentItem': case_id,
        class="hl-string">'ActivityName': class="hl-string">'Work Item Created',
        class="hl-string">'EventTime': rev0.fields.get(class="hl-string">'System.CreatedDate').isoformat(),
        class="hl-string">'AssignedTo': rev0.fields.get(class="hl-string">'System.AssignedTo', {}).get(class="hl-string">'displayName'),
        class="hl-string">'WorkItemType': work_item_type,
        class="hl-string">'State': initial_state,
        class="hl-string">'Priority': priority,
        class="hl-string">'TeamName': team_name,
        class="hl-string">'IsRework': False,
        class="hl-string">'LastDataUpdate': last_data_update
    })
    seen_states.add(initial_state)
    
    class=class="hl-string">"hl-comment"># Process state changes and other updates
    for i in range(1, len(revisions)):
        prev_rev = revisions[i-1]
        curr_rev = revisions[i]

        prev_state = prev_rev.fields.get(class="hl-string">'System.State')
        curr_state = curr_rev.fields.get(class="hl-string">'System.State')
        event_time = curr_rev.fields.get(class="hl-string">'System.ChangedDate').isoformat()
        assigned_to = curr_rev.fields.get(class="hl-string">'System.AssignedTo', {}).get(class="hl-string">'displayName')

        class=class="hl-string">"hl-comment"># State Change Event
        if prev_state != curr_state and curr_state in STATE_MAP:
            activity = STATE_MAP[curr_state]
            is_rework = curr_state in seen_states
            
            class=class="hl-string">"hl-comment"># Specific logic for QA failure (rework)
            if (prev_state in [class="hl-string">'In QA', class="hl-string">'Testing', class="hl-string">'In UAT'] and 
                curr_state in [class="hl-string">'Active', class="hl-string">'In Progress', class="hl-string">'Committed']):
                activity = class="hl-string">'QA Testing Failed'
                is_rework = True
            
            events.append({
                class="hl-string">'DevelopmentItem': case_id,
                class="hl-string">'ActivityName': activity,
                class="hl-string">'EventTime': event_time,
                class="hl-string">'AssignedTo': assigned_to,
                class="hl-string">'WorkItemType': work_item_type,
                class="hl-string">'State': curr_state,
                class="hl-string">'Priority': priority,
                class="hl-string">'TeamName': team_name,
                class="hl-string">'IsRework': is_rework,
                class="hl-string">'LastDataUpdate': last_data_update
            })
            seen_states.add(curr_state)

    class=class="hl-string">"hl-comment"># Process linked artifacts like Pull Requests from the final item state
    if item.relations:
        for rel in item.relations:
            if rel.attributes.get(class="hl-string">'name') == class="hl-string">'Pull Request':
                try:
                    pr_id = int(rel.url.split(class="hl-string">'/')[-1])
                    pr = git_client.get_pull_request_by_id(pr_id, PROJECT_NAME)
                    events.append({
                        class="hl-string">'DevelopmentItem': case_id, class="hl-string">'ActivityName': class="hl-string">'Pull Request Created',
                        class="hl-string">'EventTime': pr.creation_date.isoformat(), class="hl-string">'AssignedTo': pr.created_by.display_name,
                        class="hl-string">'WorkItemType': work_item_type, class="hl-string">'State': item.fields.get(class="hl-string">'System.State'), class="hl-string">'Priority': priority, 
                        class="hl-string">'TeamName': team_name, class="hl-string">'IsRework': False, class="hl-string">'LastDataUpdate': last_data_update
                    })
                    if pr.status == class="hl-string">'completed' and pr.last_merge_commit is not None:
                        merge_commit = git_client.get_commit(pr.last_merge_commit.commit_id, PROJECT_NAME, pr.repository.id)
                        events.append({
                            class="hl-string">'DevelopmentItem': case_id, class="hl-string">'ActivityName': class="hl-string">'Pull Request Completed',
                            class="hl-string">'EventTime': merge_commit.committer.date.isoformat(), class="hl-string">'AssignedTo': merge_commit.committer.name,
                            class="hl-string">'WorkItemType': work_item_type, class="hl-string">'State': item.fields.get(class="hl-string">'System.State'), class="hl-string">'Priority': priority, 
                            class="hl-string">'TeamName': team_name, class="hl-string">'IsRework': False, class="hl-string">'LastDataUpdate': last_data_update
                        })
                except Exception as e:
                    print(fclass="hl-string">"Warning: Could not process PR link for WI {case_id}: {e}")

            if rel.attributes.get(class="hl-string">'name') == class="hl-string">'Build':
                try:
                    build_id = int(rel.url.split(class="hl-string">'/')[-1])
                    build = build_client.get_build(PROJECT_NAME, build_id)
                    if build.status == class="hl-string">'completed' and build.result == class="hl-string">'succeeded':
                         events.append({
                            class="hl-string">'DevelopmentItem': case_id, class="hl-string">'ActivityName': class="hl-string">'Build Succeeded',
                            class="hl-string">'EventTime': build.finish_time.isoformat(), class="hl-string">'AssignedTo': None,
                            class="hl-string">'WorkItemType': work_item_type, class="hl-string">'State': item.fields.get(class="hl-string">'System.State'), class="hl-string">'Priority': priority,
                            class="hl-string">'TeamName': team_name, class="hl-string">'IsRework': False, class="hl-string">'LastDataUpdate': last_data_update
                        })
                except Exception as e:
                    print(fclass="hl-string">"Warning: Could not process Build link for WI {case_id}: {e}")

    class=class="hl-string">"hl-comment"># Note: class="hl-string">'Deployed to Production' is highly specific to release pipeline setup.
    class=class="hl-string">"hl-comment"># This part requires custom logic to check release pipeline logs linked to the work item.
    class=class="hl-string">"hl-comment"># A placeholder is added here; you would need to implement get_deployment_events.
    class=class="hl-string">"hl-comment"># deployment_events = get_deployment_events(case_id, item)
    class=class="hl-string">"hl-comment"># events.extend(deployment_events)

    return events

if __name__ == class="hl-string">'__main__':
    main()

let
    Source = AzureDevOps.Views("[Your Organization Name]", "[Your Project Name]", [ApiVersion = 2]),
    View_Table = Source{[Id="[Your Analytics View ID]",Name="[Your Analytics View Name]"],Kind="View"}[Data],

    // Add a unique key for each daily snapshot
    AddSnapshotKey = Table.AddColumn(View_Table, "SnapshotKey", each Text.Combine({Text.From([Work Item Id], "en-US"), Text.From([Date], "en-US")}), type text),
    
    // Lag the state column to detect changes
    AddPreviousState = Table.Buffer(Table.Sort(AddSnapshotKey,{{"Work Item Id", Order.Ascending}, {"Date", Order.Ascending}})),
    ShiftedList = {null} & List.RemoveLastN(AddPreviousState[State],1),
    AddLagColumn = Table.ToColumns(AddPreviousState) & {ShiftedList},
    FinalTable = Table.FromColumns(AddLagColumn, Table.ColumnNames(AddPreviousState) & {"PreviousState"}),
    
    // Filter to rows where the state actually changed or it's the creation event
    FilterStateChanges = Table.SelectRows(FinalTable, each [State] <> [PreviousState] or [PreviousState] = null),

    // --- Event 1: Work Item Created ---
    WorkItemCreated = Table.SelectRows(FilterStateChanges, each [PreviousState] = null),
    AddActivity_Created = Table.AddColumn(WorkItemCreated, "ActivityName", each "Work Item Created"),
    SelectCols_Created = Table.SelectColumns(AddActivity_Created, {"Work Item Id", "ActivityName", "Created Date", "Work Item Type", "State", "Priority", "Assigned To", "Area Path"}),
    RenameCols_Created = Table.RenameColumns(SelectCols_Created,{{"Created Date", "EventTime"}, {"Area Path", "TeamName"}}),

    // --- Infer events from State Changes ---
    StateChangeEvents = Table.SelectRows(FilterStateChanges, each [PreviousState] <> null),
    AddActivity_StateChange = Table.AddColumn(StateChangeEvents, "ActivityName", each 
        if [State] = "Approved" then "Work Item Approved"
        else if [State] = "Committed" or [State] = "Active" or [State] = "In Progress" and [PreviousState] <> "In QA" then "Development Started"
        else if [State] = "Resolved" then "Development Completed"
        else if [State] = "In QA" or [State] = "Testing" then "QA Testing Started"
        else if [State] = "In Progress" and [PreviousState] = "In QA" then "QA Testing Failed"
        else if [State] = "QA Approved" then "QA Testing Completed"
        else if [State] = "In UAT" then "UAT Started"
        else if [State] = "UAT Approved" then "UAT Approved"
        else if [State] = "Deployed" then "Deployed to Production" // Assuming a state reflects deployment
        else if [State] = "Closed" then "Work Item Closed"
        else if [State] = "Done" then "Work Item Closed"
        else if [State] = "Removed" or [State] = "Cancelled" then "Work Item Cancelled"
        else null),
    FilterNullActivities = Table.SelectRows(AddActivity_StateChange, each ([ActivityName] <> null)),
    SelectCols_StateChange = Table.SelectColumns(FilterNullActivities, {"Work Item Id", "ActivityName", "Date", "Work Item Type", "State", "Priority", "Assigned To", "Area Path"}),
    RenameCols_StateChange = Table.RenameColumns(SelectCols_StateChange,{{"Date", "EventTime"}, {"Area Path", "TeamName"}}),

    // --- Combine all event tables ---
    CombinedEvents = Table.Combine({RenameCols_Created, RenameCols_StateChange}),
    
    // --- Placeholder Events Not Captured by State Changes ---
    // These activities require different data sources (OData/API) and are not reliably captured via Work Item history alone.
    // Placeholder logic is omitted as it would be speculative.

    // --- Final Formatting ---
    RenameCaseID = Table.RenameColumns(CombinedEvents,{{"Work Item Id", "DevelopmentItem"}}),
    AddSourceSystem = Table.AddColumn(RenameCaseID, "SourceSystem", each "Azure DevOps"),
    AddLastUpdate = Table.AddColumn(AddSourceSystem, "LastDataUpdate", each DateTime.LocalNow()),
    ChangeTypes = Table.TransformColumnTypes(AddLastUpdate,{{"DevelopmentItem", type text}, {"EventTime", type datetime}, {"LastDataUpdate", type datetime}}),

    // Final column selection and ordering
    SelectFinalColumns = Table.SelectColumns(ChangeTypes, {"DevelopmentItem", "ActivityName", "EventTime", "SourceSystem", "LastDataUpdate", "Assigned To", "Work Item Type", "State", "Priority", "TeamName"})

in
    SelectFinalColumns