Neo.Tax Help Center

Export Azure DevOps tickets with Power BI

This guide provides an alternative to our manual workflow for exporting tickets from ADO. Instead, you can export your data using a a Power BI query that pulls work items across all projects in your Azure DevOps organization in a single shot, filtered to the date window Neo.Tax expects, and produces a CSV with column names that match Neo.Tax’s expected format.

Once set up, the annual refresh is a one-line change (the tax year) followed by Refresh → Export to CSV.

How it works

  • We hit the Azure DevOps Analytics OData endpoint at the organization level (not project level), so a single query returns work items across every project you have access to. No per-project operations required.
  • The query filters by ChangedDate to match Neo.Tax’s window: 6 months before the tax year start through 3 months after tax year end.
  • Power Query expands the OData record fields (AssignedTo, CreatedBy, Project) into flat columns, adds a synthesized Work Item URL column, and renames everything to match the column headers Neo.Tax expects from the native ADO CSV export.
  • It also adds the organization’s stable host ID (a GUID, fetched from the connectionData API) as the Organization Host ID column. Neo.Tax uses this — not the renameable URL slug — to identify the workstream, so your tickets stay linked even if the organization is later renamed.
  • Final step: export the table to CSV and upload it to Neo.Tax.

Note on “Tree of work items”: the original instructions use that query type to preserve parent → child hierarchy. In OData, every work item carries its ParentWorkItemId, which gives Neo.Tax the same hierarchy information as a column. No tree query needed.

Prerequisites

  1. Power BI Desktop installed (free download from Microsoft).
  2. Azure DevOps account with read access to all projects you want included. The query will only return data for projects the authenticated user can see.
  3. Analytics extension enabled in your ADO organization. It’s enabled by default in modern ADO; if your org disabled it, an org admin needs to re-enable it under Organization Settings → Extensions.
  4. Your organization name—the slug from your ADO URL: https://dev.azure.com/{Organization}.

Step 1: Create a blank query

  1. Open Power BI Desktop.
  2. Home → Get Data → Blank Query.
  3. The Power Query Editor opens. In the left panel, right-click Query1 → Rename → call it WorkItems.
  4. Home → Advanced Editor.

Step 2: Paste the M code

Replace whatever is in the Advanced Editor with the code below. Update the two parameters at the top (Organization and TaxYear), then click Done.

let
    // ===== UPDATE THESE TWO VALUES =====
    Organization = "YOUR_ORG_NAME",   // from https://dev.azure.com/{Organization}
    TaxYear      = 2025,              // calendar tax year you're filing for
    // ===================================

    // Window = 6 months before tax year start → 3 months after tax year end
    StartDate     = #date(TaxYear - 1, 7, 1),
    EndDate       = #date(TaxYear + 1, 3, 31),
    StartDateText = Date.ToText(StartDate, "yyyy-MM-dd") & "T00:00:00Z",
    EndDateText   = Date.ToText(EndDate,   "yyyy-MM-dd") & "T23:59:59Z",

    // Org-level Analytics OData endpoint = work items across ALL projects
    BaseUrl  = "https://analytics.dev.azure.com/" & Organization & "/_odata/v4.0-preview/WorkItems",
    Filter   = "$filter=ChangedDate ge " & StartDateText & " and ChangedDate le " & EndDateText,
    QueryUrl = BaseUrl & "?" & Filter,

    Source = OData.Feed(QueryUrl, null, [Implementation = "2.0"]),

    // Organization host id (GUID) — the stable identifier Neo.Tax binds tickets to. It does NOT
    // change when the org is renamed (the URL slug does). connectionData is org-scoped, so it
    // reuses the same Azure DevOps sign-in as the feed above.
    HostId = Json.Document(Web.Contents("https://dev.azure.com/" & Organization & "/_apis/connectionData", [Query = [#"api-version" = "7.1-preview.1"]]))[instanceId],

    // Expand record-typed columns
    ExpAssigned = Table.ExpandRecordColumn(Source,      "AssignedTo", {"UserName", "UserEmail"}, {"AssignedToName", "AssignedToEmail"}),
    ExpCreated  = Table.ExpandRecordColumn(ExpAssigned, "CreatedBy",  {"UserName", "UserEmail"}, {"CreatedByName",  "CreatedByEmail"}),
    ExpProject  = Table.ExpandRecordColumn(ExpCreated,  "Project",    {"ProjectName"},           {"TeamProject"}),

    // Synthesize a friendly "Name <email>" assignee/creator string and the work item URL
    AddAssignee = Table.AddColumn(ExpProject,  "Assigned To",   each if [AssignedToName] = null then null else [AssignedToName] & " <" & [AssignedToEmail] & ">", type text),
    AddCreator  = Table.AddColumn(AddAssignee, "Created By",    each if [CreatedByName]  = null then null else [CreatedByName]  & " <" & [CreatedByEmail]  & ">", type text),
    AddUrl      = Table.AddColumn(AddCreator,  "Work Item URL", each "https://dev.azure.com/" & Organization & "/" & [TeamProject] & "/_workitems/edit/" & Text.From([WorkItemId]), type text),

    // Stamp the org host id (a per-organization constant) onto every row.
    // The column name matches Neo.Tax's upload template so it maps automatically.
    AddHostId   = Table.AddColumn(AddUrl, "Organization Host ID", each HostId, type text),

    // Rename OData field names → ADO CSV-export header names that Neo.Tax expects
    Renamed = Table.RenameColumns(AddHostId, {
        {"WorkItemId",        "ID"},
        {"WorkItemType",      "Work Item Type"},
        {"ParentWorkItemId",  "Parent"},
        {"RelatedLinkCount",  "Related Link Count"},
        {"AreaPath",          "Area Path"},
        {"IterationPath",     "Iteration Path"},
        {"CreatedDate",       "Created Date"},
        {"ClosedDate",        "Closed Date"},
        {"CompletedDate",     "Finish Date"},
        {"TeamProject",       "Team Project"},
        {"StartDate",         "Start Date"},
        {"TagNames",          "Tags"},
        {"ChangedDate",       "Changed Date"},
        {"OriginalEstimate",  "Original Estimate"},
        {"ResolvedReason",    "Resolved Reason"},
        {"TargetDate",        "Target Date"},
        {"DueDate",           "Due Date"}
    }, MissingField.Ignore),

    // Final columns in Neo.Tax's expected order
    Final = Table.SelectColumns(Renamed, {
        "ID", "Work Item Type", "Title", "Description", "Assigned To", "State",
        "Parent", "Priority", "Related Link Count", "Area Path", "Iteration Path",
        "Created Date", "Created By", "Closed Date", "Finish Date", "Effort",
        "Team Project", "Start Date", "Tags", "Changed Date", "Original Estimate",
        "Resolved Reason", "Target Date", "Due Date", "Work Item URL", "Organization Host ID"
    }, MissingField.UseNull)
in
    Final

We recommend saving this code to a .pbix file for future use!

Step 3: Authenticate

When you click Done, Power BI prompts for credentials.

  1. Choose Organizational account.
  2. Sign in with the same Microsoft account you use for ADO.
  3. Set the privacy level to Organizational if asked.

Power BI may prompt twice—once for analytics.dev.azure.com (the work-items feed) and once for dev.azure.com (the connectionData call that retrieves your organization’s host ID). Sign in with the same organizational account both times.

The query will run. For large orgs (tens of thousands of work items), the first load can take 5–15 minutes. OData paginates automatically—just let it finish.

Step 4: Verify the columns

Once loaded, scroll right through the result. You should see all 26 expected columns. A few things to check:

  • Organization Host ID is a GUID and must be identical on every row—it identifies your organization. If it’s blank, the connectionData call didn’t authenticate; see Troubleshooting below.
  • Description is HTML in ADO—the column will contain markup like <div>...</div>. Neo.Tax handles this; do not strip it.
  • Tags is a semicolon-separated string (tag1; tag2; tag3)—same as the native ADO CSV export.
  • Effort and Resolved Reason are populated only for work item types that use those fields (typically User Stories / Bugs). Empty values are normal for Tasks / Epics.
  • If a column is entirely missing (not just empty), it likely doesn’t exist in your org’s process template. The MissingField.Ignore / MissingField.UseNull flags keep the query from breaking. Neo.Tax accepts blank values in Related Link Count, Original Estimate, Resolved Reason, and Due Date.
  • Three columns must be populated on every row: ID, Work Item URL, and Organization Host ID. If any of the three is blank, Neo.Tax skips that row without raising an error — the upload appears to succeed but imports fewer tickets than expected, or none. Spot-check all three before exporting.
  • Work Item URL must be the browser link: https://dev.azure.com/{org}/{project}/_workitems/edit/{id}. If yours looks like https://dev.azure.com/{org}/{project-guid}/_apis/wit/workItems/{id} — the REST API form, usually with a GUID where the project name should be — the query has been modified. Restore the AddUrl line from Step 2. Neo.Tax cannot read the API form and will skip every row.

Step 5: Load and export to CSV

  1. Home → Close & Apply. Power BI loads the data into the model.
  2. Switch to the Table view (the grid icon on the left sidebar).
  3. Select the WorkItems table.
  4. Right-click the table in the Fields pane → Export data → choose CSV (UTF-8) → save.

Alternatively, for a more direct CSV export bypassing the model entirely:

  • In the Power Query Editor, click the table preview header → Copy Entire Table → paste into Excel → Save As CSV.

The first method is cleaner for large datasets.

No separate users file needed for Azure DevOps. Neo.Tax derives the person list automatically from the Assigned To and Created By columns in this export. Note that this captures everyone who created or was assigned a ticket in the window — not commenters, or people who modified tickets without owning them. For most R&D contexts that’s the right population, but if your team includes contributors who don’t own tickets, let Neo.Tax know they may need supplementary names.

Step 6: Upload the export

Follow steps 2–5 from the original Neo.Tax instructions: fill in the Elevator Pitch under Company Information, click Upload Ticket Provider Files on Data Sources, select AzureDevOps, and upload the CSV. You’ll be shown a column-mapping screen — the column names in this export are chosen to map automatically, so you should be able to confirm and continue. Then notify Neo.Tax that you’re ready for processing.

Annual update

Next year, the entire update is:

  1. Open the .pbix file.
  2. Home → Transform Data → Advanced Editor.
  3. Change TaxYear = 2025 to the new year.
  4. Done → Close & Apply → Refresh.
  5. Export the WorkItems CSV → upload to Neo.Tax.

If you want to make this even smoother, convert Organization and TaxYear to Power BI Parameters (Manage Parameters → New) so future years are a one-field UI edit instead of touching M code.

Troubleshooting

  • Organization Host ID is blank for all rows.

The connectionData call couldn’t authenticate or was blocked. Either re-run and sign in when prompted for dev.azure.com, or hardcode it: open https://dev.azure.com/{YourOrg}/_apis/connectionData in a browser while signed in, copy the instanceId GUID, and replace the HostId = Json.Document(...) line with HostId = "your-guid-here",.

  • “Access to the resource is forbidden.”

The signed-in user lacks Analytics view permission on at least one project. Either grant permissions or restrict the query to projects the user can see by adding and Project/ProjectName ne 'LockedProject' to the filter. (Or run with a service account that has org-wide read.)

  • “The query timed out / failed after a long load.”

Org has too many work items for a single OData call. Two fixes: (a) narrow the date window to the minimum Neo.Tax requires (calendar year + 6mo before + 3mo after—don’t widen further); or (b) split the query into two calls—one for ChangedDate in the first half of the window, one for the second half—and append them.

  • Description column is empty for all rows.

A small number of older ADO orgs disable Description in Analytics. Workaround: add a second query against the ADO REST API (_apis/wit/workitems?ids=...&fields=System.Description) keyed on ID, and merge it into the main table.

  • Custom fields needed.

If your team uses custom fields Neo.Tax wants included, add them to the OData call with $select and to the final Table.SelectColumns list. Custom fields appear in OData under their reference name with dots replaced — e.g., Custom.BusinessImpact becomes Custom_BusinessImpact.

  • Tree hierarchy concerns.

The native ADO “Tree of work items” CSV export tags rows with their position in the hierarchy. Our OData export instead provides the Parent column (the parent’s work item ID) for every row. This is the same information in a flat form—Neo.Tax’s importer reconstructs the tree from the Parent column.

Neo.Tax Help Center
Esc
↑ ↓ to move Enter to open