Learn how to read the line items in your Partner Center reconciliation files

Appropriate roles: Billing admin | Global admin

You can download your reconciliation files from Partner Center for a detailed, line-item view of each charge in a billing cycle.

Line-item details include charges for each customer's subscriptions, and detailed events (such as a mid-term addition of licenses to a subscription).

For information about how to read your invoice, see Read your bill.

Understand reconciliation file fields

Here are the reconciliation files and what they show:

Reconciliation file field Shows
Legacy license-based Costs and quantities of legacy Office and Dynamics products for a billing period
Legacy usage-based Costs and usage of legacy Azure products for a billing period
New commerce daily rated usage Daily usage and charges for new commerce (Azure plan) usage-based products for a billing period
New commerce reconciliation Aggregated costs, quantities, and usage for all new commerce products for a billing periods

Understand charge types in reconciliation files

To learn about the different types of charges in reconciliation files (displayed in the ChargeType column), refer to Reconciliation file charge types.

Fix formatting issues

Occasionally, a reconciliation file might have formatting issues. For example, this issue might occur if the en-us locale isn't used.

To fix any formatting issues in your reconciliation files:

  1. Open the reconciliation file (in .csv format) in Microsoft Excel.
  2. Select the first column in the file.
  3. Open the Convert Text to Columns Wizard.
  4. On the ribbon, select Data, then select Text to Columns.
  5. In the wizard, select Delimited file type, and then select Next.
  6. In the Delimiters field, select Comma. (If Tab is already selected, you can leave this option selected.) Then, select Next.
  7. In the Column data format field, select Date:MDY, and then select Next.
  8. In the Column data format field, select Text for all amount columns, and then select Finish.

Download reconciliation files programmatically

Reconciliation files can be large and are sometimes difficult to download from the portal. If you need to download them programmatically, refer to Get invoice line items.

Where do I download the file asynchronously?

To enhance the download experience and improve efficiency, new commerce reconciliation files are downloaded asynchronously from Partner Center. To download a file:

  1. Sign in to Partner Center and select Billing workspace.

  2. Go to the Billing history and select Recurring and one-time purchases (billing currency) tab, or go to the Billing overview (NCE).

  3. Choose the reconciliation file you want to download.

  4. A notification will appear at the top of the screen, informing you that the file is currently being prepared and will soon be available on the Reports (NCE) page. This process can take some time, depending on the amount of data that needs to be processed.

  5. To check the progress of your request, look at the status. If it shows "Progressing" or "Checking," it means that our system is still collecting and generating your reprot. To avoid redundancy, search from the generated reports before making a new request.

Statuses and their meanings:

  • Queued: Request submitted
  • Processing: The system is collecting data for the report
  • Checking: Verifying if the data is ready
  • Completed: Report is available for download
  • Failed: An error occurred while generating the report
  • No data found: The system couldn't any data for the report

If your file exceeds the row limit in Excel

If you can download a reconciliation file but can't open it in Microsoft Excel, that probably means the file contains more rows than Excel allows. If that happens, you can:

Open a reconciliation file in Excel Power Query

  1. Download the reconciliation file from Partner Center.

  2. Open a new blank workbook in Microsoft Excel.

  3. Navigate to Data tab, then select Get Data > From File > From Text/CSV, and import the CSV (Comma Separated Value) reconciliation file.

  4. After a while, you will see a window with the file preview.

  5. In that window, select the Load dropdown menu > Load To.

    Screenshot of the File preview window, with the dropdown next to Load open, and the selection Load To inside the drop-down selected.

  6. In the Import Data dialog box, select the options as indicated in the following screenshot.

    Screenshot of the Import Data window, with two items highlighted: Only Create Connections and Add this data to the Data Model.

  7. Select OK, and wait for the loading process to complete.

  8. On the right side, you will find the name of the file and the number of rows. Double-click this area to open the Power Query Editor.

    Screenshot of the Queries & Connections window, with a sample of a report called Test Recon Data highlighted.

  9. Now, when you scroll down, you'll notice that the new rows are added dynamically.

  10. To search for specific transactions, use the filter option as shown in the following screenshot. You can also aggregate transactions or do mathematical calculations using the "Group By" or "Statistics" features.

    Screenshot of Excel Power Query Editor, with two highlighted items, Group By and Statistics.

  11. For instance, to find the number of billable days in the charge cycle, you can use the Custom Column feature to calculate the duration between the charge start and end dates. Additionally, Power Query offers many features that allow various computations.

    Screenshot of Excel Power Query Editor. In the menu bar, Add Column is highlighted.

  12. After applying the required filters in the Power Query, you can transfer the limited transaction data to Excel by following these steps.

    Screenshot of Excel Power Query Editor. In the spreadsheet, the CreditReasonCode drop-down is open. The menu item: Copy Entire Table is highlighted.

Open a reconciliation file in Power BI

To open a reconciliation file in Power BI, use the following steps:

  1. Download the reconciliation file as you normally would.
  2. Download, install, and open an instance of Microsoft Power BI.
  3. On the Power BI Home tab, select Get data.
  4. In the list of Common data sources, select Text/CSV.
  5. When prompted, open your reconciliation file.

Negative amount displayed

If you see a negative amount for some transactions in your reconciliation file, it's likely because:

  • You recently made changes to the subscription, such as canceling, upgrading, adjusting licenses, or other modifications.
  • You received credit for a service level agreement (SLA) violation, Azure consumption, or price adjustments.

To learn more, refer to the ChargeType attribute in the reconciliation data.

How is the total cost calculated for new commerce license-based purchases?

Cost is calculated using EffectiveUnitPrice, not UnitPrice. The UnitPrice only shows the list price from the price sheet, but the EffectiveUnitPrice is determined based on several things:

  • Discounts (promotions or tiered pricing) and adjustments (partner earned credit) to the unit price.
  • The number of billable days, which are counted from the time an event happens until the end of the charge cycle.
  • Exchange rate.

The UnitPrice of a subscription stays the same for the entire billing term. If it changes during the term, the reconciliation data can show the new price, but it won't be used to calculate the costs.

To verify the cost, check the EffectiveUnitPrice and use it to create invoices for your customers.

Why does my invoice reconciliation data show a recurring charge for a product for which there are no daily charges?

If you notice a recurring charge on your invoice reconciliation data but daily rated usage charges for the product are zero, don't worry. This is because some products, such as Azure savings plans or reservations, have a fixed price that you pay either monthly, annually, or one-time, depending on billing plan, for a certain number of units or quantities used in the charge cycle. As a result, charges for these products aren't calculated daily based on usage.

Here's what you need to know:

  • Even if your daily charges are zero, you're still required to pay the fixed price that you agreed to at the time of purchase. This is the recurring charge that appears on your invoice reconciliation data.
  • Until you exceed that unit or quantity, your daily charges will show as zero against your consumed unit or quantity.
  • However, if you go over the agreed unit or quantity, you'll be charged extra at a pay-as-you-go rate for the overages.

Simply put, for fixed-priced products like Azure savings plans, reservations, or specific subscriptions, recurring charges will appear on your invoice reconciliation data even if your daily charges are zero until your usage goes over the agreed unit or quantity. After that, any additional usage will be charged as pay-as-you-go charges.

To check the costs, see below how an Azure reservation or savings plan linked to an Azure subscription.

How do you find the Azure subscription ID for a product?

To track your Azure subscription ID for each Azure resource, follow these steps:

Tip

To keep track of products purchased multiple times under the same or different Azure subscriptions, use meaningful resource groups or tags. This helps identify which subscription was used for each product, even if they have the same name.

Pay-as-you-go product

  • To match your Azure usage with your Azure subscription, link the SubscriptionID from your invoice reconciliation with the daily rated usage data and find the corresponding EntitlementID, which is your Azure subscription ID.

Azure reservation

  • For Azure reservations, link the ReservationOrderId from your invoice reconciliation to the same ID in the AdditionalInfo attribute of the daily rated usage data.

Azure savings plan

  • For Azure savings plans, link the ReservationOrderId to the BenefitOrderId attribute in the daily rated usage data.

How do you find Azure credits in the invoice reconciliation and daily rated usage files?

Azure credits can only be used for first-party Azure products, although there might be exceptions. Find out how much Azure credit a customer has been given by looking at invoice reconciliation and daily rated usage data.

Invoice reconciliation

  • Credit line items display a negative value for Subtotal and Total.
  • The ChargeType for credit line items is set to "customerCredit", while the CreditReasonCode includes text describing the type of credit, such as "Azure Credit Offer".
  • Invoice reconciliation data shows usage and credit received. Transactions should be organized by ProductID, SKUID, AvailabilityID, OrderDate, and either CustomerName or CustomerID. To know the credit amount, add up the Total.
  • The BillableQuantity for charge and credit line items should match.

Daily rated usage

  • Filter by customer tenant ID, type of credit in CreditType, and 100 inCrditPercentage.
  • To know the credit amount, add up the BillingPreTaxTotal.

Note

Some Azure credits aren't included in the daily rated usage data, as they're only calculated at the end of the billing period. However, you can always find the credit information in the invoice reconciliation data.

How do you reconcile telco pay-as-you-go charges?

To keep track of your pay-as-you-go (PAYG) charges for telco products, check both your daily usage and monthly invoice reconciliation data. Here's how:

  • Daily rated usage reconciliation: This shows your daily call minutes and charges for each service you use. Look for BillingPreTaxTotal for daily charges and "NumMin" property under AdditionalInfo for call minutes.

Note

Quantity in the daily rated usage data doesn't show exact call minutes.

CustomerName ProductId SkuId ProductName UsageDate ChargeType UnitPrice Quantity UnitType BillingPreTaxTotal BillingCurrency AdditionalInfo EffectiveUnitPrice
Constoso CFQ7TTC0JL2N 1 Microsoft 365 International Calling Plan - Outbound US 9/17/2021 New 1 0.1524 Per Call 0.15 USD { "NumMin": 0.2} 0.984251969
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/18/2021 New 1 6.6275 Per Call 6.623381835 USD { "NumMin": 24.1} 0.999378625
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/21/2021 New 1 2.97 Per Call 2.968154515 USD { "NumMin": 10.8} 0.999378625
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/22/2021 New 1 0.0585 Per Call 0.05846365 USD { "NumMin": 0.3} 0.999378625
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/23/2021 New 1 0.4485 Per Call 0.447924684 USD { "NumMin": 2.3} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/24/2021 New 1 0.0975 Per Call 0.097374931 USD { "NumMin": 0.5} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/29/2021 New 1 0.156 Per Call 0.15579989 USD { "NumMin": 0.8} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 International Calling Plan - Outbound US 9/29/2021 New 1 0.2114 Per Call 0.211128825 USD { "NumMin": 1.4} 0.998717244
Constoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US 9/30/2021 New 1 0.178 Per Call 0.177771669 USD { "NumMin": 8.9} 0.998717244
Total 10.89 49.3
  • Monthly invoice reconciliation: This shows your total monthly costs and call minutes for each service. Check Total for monthly costs and BillableQuantity for call minutes.
CustomerName ProductId SkuId ProductName ChargeType UnitPrice Quantity Subtotal TaxTotal Total Currency ChargeStartDate ChargeEndDate EffectiveUnitPrice BillableQuantity
Contoso CFQ7TTC0JL2N 1 Microsoft 365 International Calling Plan - Outbound US new 0.381 1 0.36 0 0.36 USD 9/1/2021 9/30/2021 0.381 1.6
Contoso CFQ7TTC0JL2S 1 Microsoft 365 Audio Conferencing - Outbound US new 0.352836167 1 10.53 0 10.53 USD 9/1/2021 9/30/2021 0.352836167 47.7
Total 10.89 49.3

Remember, the PAYG charges start once your free talk time is fully used up. You can match the charges and call minutes between daily usage and monthly invoice using ProductId and SKUId.

Your charges and call minutes are itemized by call type (domestic, international, and toll-free). So, you can see exactly what you're paying for. The total of BillingPreTaxTotal and the Subtotal (before tax) have the same value and the BillableQuantity equals the sum of "NumMin."

What do different prefixes for Reservation Order ID mean?

The prefix shows where you bought your Azure reservations.

  1. PCTest: Test purchases in Partner Center.
  2. RIOderId: Regular purchases in Partner Center.
  3. No prefix is used for purchases made through Microsoft Cost Management.

Next steps