DEV Community

AdminPackStudio
AdminPackStudio

Posted on

Excel & CSV ops for IT admins: cleaning Intune device exports without breaking your UPNs (Power Query basics + 9 pitfalls)

#excel #sysadmin #intune #powerquery

Excel & CSV ops for IT admins: cleaning Intune device exports without breaking your UPNs

Someone asks: "How many active laptops do we actually have, and who's on them?" You export the device list from the Intune admin center, open it in Excel, and immediately run into problems: serial numbers in scientific notation, the same laptop listed three times, and a user who shows up as Jane.Doe@contoso.com in one sheet and jane.doe@contoso.com (trailing space) in the other.

None of this is hard. It just needs a repeatable routine. Here's the one I'd hand to any admin who lives in CSV exports.

Quick version (TL;DR):

  1. Don't double-click the CSV. Import it with Data → From Text/CSV.
  2. Normalize your join key: trim, clean, lowercase UPNs before you match anything.
  3. Dedupe on the right column (serial or device ID, not device name). Keep the latest check-in, not whichever row comes first.
  4. Put it in Power Query once, then press Refresh every week.
  5. Keep raw data, cleaned data, and reports on separate sheets.

1. Import, don't open

Double-clicking a .csv lets Excel guess every column's type. That's how you lose data:

  • Long numbers (IMEIs, some serials) turn into 3.52E+14, and the original digits are gone once you save.
  • Leading zeros get dropped (00123 → 123).
  • Dates get read in your PC's locale, so 03/04/2026 might become April 3rd or March 4th.

Do this instead: Data → From Text/CSV → pick the file → Transform Data. That opens Power Query, where you set column types on purpose.

Also worth checking: recent Microsoft 365 builds of Excel have File → Options → Data → Automatic data conversion settings. You can turn off "remove leading zeros" and "convert to scientific notation" there. Option names move around, so check your build.

Rule zero: work on a copy of the export. Keep the original file untouched in case you need it for an audit or ticket.

2. Normalize UPNs before you join anything

The UPN (user principal name) is usually your best join key between a device export and a user or license export. It's also where the sneakiest problems hide.

Pitfall What it looks like Fix
Trailing / leading spaces jane.doe@contoso.com doesn't match TRIM() / Text.Trim
Non-breaking spaces (char 160) Looks trimmed, still doesn't match SUBSTITUTE(A2,CHAR(160)," ") before TRIM
Case differences Power Query merges miss rows Lowercase both sides
UPN ≠ email address jdoe@contoso.onmicrosoft.com vs jane.doe@contoso.com Join on UPN to UPN, never UPN to mail
Renamed users Old UPN in last month's export For long-lived tracking, also keep the object ID if your export has it
Blank primary user Shared, kiosk, or userless devices Label them (no primary user). They usually aren't errors.

A worksheet helper column if you're not using Power Query yet:

=LOWER(TRIM(SUBSTITUTE([@[Primary user UPN]],CHAR(160)," ")))
Enter fullscreen mode Exit fullscreen mode

Why case matters: Excel's XLOOKUP and COUNTIF don't care about case. Power Query's Merge and Remove Duplicates do. So the same data can give you different answers depending on which tool you used. Lowercase the key, and the tools will agree.

3. Duplicates: dedupe on the right key, keep the right row

Duplicates in device exports are usually real records, not export bugs. A laptop that was reset and re-enrolled can show up more than once, and so can a device that got re-imaged and renamed.

Pick the key on purpose:

  • Device name. Bad key. Names get reused (LAPTOP-001 after a re-image) and changed.
  • Serial number. Good key for counting physical hardware. Watch for blanks and placeholder values that some VMs and white-box devices report. Filter those out and look at them separately.
  • Device ID. Good key for counting Intune records. It changes on re-enrollment, so it's the right key for "records", not for "laptops".

Keep the latest row, not the first one. Excel's grid Data → Remove Duplicates keeps whichever row comes first. Sort by last check-in, newest first, then remove duplicates. In Power Query there's one more step to remember (see the Table.Buffer note below).

Before you delete anything, count it:

=COUNTIFS(tblDevices[Serial number],[@[Serial number]])
Enter fullscreen mode Exit fullscreen mode

Filter where that's > 1. Those rows are your stale-record cleanup list, which is useful on its own and not just noise to get rid of.

4. Power Query basics: a refreshable device list

This is the habit that saves the most time. You build the cleanup once, and next week you drop in the new export and press Refresh.

In Power Query: Home → Advanced Editor, and adapt this. Column names are examples, so rename them to match your export headers.

let
    Source   = Csv.Document(
                 File.Contents("C:\Exports\Intune\devices.csv"),
                 [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Headers  = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),

    // Keep serials as TEXT so nothing turns into scientific notation
    Typed    = Table.TransformColumnTypes(Headers, {
                 {"Serial number", type text},
                 {"Primary user UPN", type text},
                 {"Last check-in", type datetime}}, "en-US"),

    // Normalize the join key: replace non-breaking spaces, trim, clean, lowercase
    CleanUpn = Table.TransformColumns(Typed, {{"Primary user UPN",
                 each if _ = null then null
                      else Text.Lower(Text.Clean(Text.Trim(
                           Text.Replace(_, Character.FromNumber(160), " ")))),
                 type text}}),

    // Newest check-in first, then BUFFER so the sort order sticks
    Sorted   = Table.Buffer(Table.Sort(CleanUpn, {{"Last check-in", Order.Descending}})),

    // One row per physical device (latest record wins)
    Deduped  = Table.Distinct(Sorted, {"Serial number"})
in
    Deduped
Enter fullscreen mode Exit fullscreen mode

Notes:

  • "en-US" on the type step tells Power Query how to read the date text. Set it to whatever locale your export actually uses. Timestamps in admin exports are often UTC, so label the column so nobody reads it as local time.
  • Table.Buffer after the sort is the classic gotcha. Without it, Power Query can reorder rows during optimization and Table.Distinct might keep an older row. Buffering pins the sorted order.
  • Point the path at a fixed filename (for example devices.csv) and overwrite it each week, or use From Folder to combine several exports.
  • Close & Load To… → Table on a sheet named clean_devices.

Then join licenses or users with Merge Queries on the lowercased UPN (Left Outer: keep all devices, bring in the matches).

5. Stale devices: one column that answers the real question

Add a custom column in Power Query (Add Column → Custom Column):

Duration.Days(DateTime.LocalNow() - [Last check-in])
Enter fullscreen mode Exit fullscreen mode

Name it DaysSinceCheckIn. Then use buckets (0–7, 8–30, 31–90, 90+) in a Pivot. That gives you "active vs stale" in one view. It's an easy number for a manager to read, and it's your cleanup queue.

Be careful about the conclusion: "hasn't checked in for 90 days" means investigate. It doesn't mean delete. That laptop might be on someone's parental leave shelf. Clean up through your normal change process, not straight from the spreadsheet.

6. Workbook layout that survives next month

  • raw_* sheets: the untouched import (or just keep it in Power Query)
  • clean_* sheets: Power Query output tables (tblDevices, tblLicenses)
  • viz_* sheets: Pivots and charts built from Tables, never from hard-coded ranges
  • A README sheet: where each export comes from, who ran it, and when

More pitfalls, quick fire:

  • Distinct counts in Pivots: check "Add this data to the Data Model" when creating the Pivot, then choose Distinct Count. A regular Count counts rows, not users.
  • Hidden header changes: if an admin portal renames a column, your query breaks on refresh. That's a good thing, because it fails loudly. Fix the column name in one step instead of hunting through formulas.
  • Personal data: device and user exports are personal data. Store them where your org says to, and don't email them around as attachments.

7. Want the full set of patterns?

This post covers the cleanup part. The Excel & Power Automate Ops Pack for IT Admins from Admin Pack Studio ($29) goes further: Excel ops patterns for messy admin CSVs (Tables, XLOOKUP, Pivots), an admin workbook starter layout (Licenses / Devices / Tickets sheets), Power Automate cloud flow starters for IT approvals and notifications, guardrails and run-after error handling, and two read-only PowerShell helpers that generate practice CSVs so you can learn without touching production data. It teaches you a layout to build in your own tenant. It doesn't ship a macro-laden .xlsx that breaks the first time you open it.

👉 https://cashflow4375.gumroad.com/l/vougj

Need the Intune exports and enrollment hygiene side? The Intune & M365 Admin Starter Pack ($19 launch price, normally $29) has enrollment/compliance checklists and read-only snapshot scripts whose output drops straight into this workbook: https://cashflow4375.gumroad.com/l/joonf

Admin Pack Studio. Not affiliated with Microsoft. Excel, Power Query, and Intune are Microsoft products; menu names and export columns change over time, so check current Microsoft documentation. Examples use placeholder data (contoso.com).

Top comments (0)