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?

We don't use the UnitPrice to calculate costs, we use the EffectiveUnitPrice. The UnitPrice is just the list price, while the EffectiveUnitPrice considers several factors:

  • Discounts like promotions or tiered pricing, and adjustments like partner earned credit.
  • The number of days you're billed for, which is counted from when a billing event occurs until the end of the billing cycle.
  • The exchange rate.

The UnitPrice for a subscription doesn't change during the billing term. If it does change, the new price might show in the reconciliation data, but it doesn't affect the cost calculation.

To confirm the cost, check the EffectiveUnitPrice and use it to invoice your customers.

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

If you see a recurring charge on your invoice reconciliation data but no daily usage charges, it's okay. This happens with products like Azure savings plans or reservations that have a fixed price. You pay this price monthly, annually, or one-time, depending on your billing plan, for a certain number of units used in the charge cycle. These products don't have daily charges based on usage.

Here's what you need to know:

  • Even if your daily charges are zero, you still need to pay the fixed price that you agreed to when you bought the product. This is the recurring charge on your invoice reconciliation data.
  • Your daily charges will show as zero until you use more than the agreed number of units.
  • If you use more than the agreed unit or quantity, you'll be charged extra at a pay-as-you-go rate for the overages.

In short, for fixed-priced products like Azure savings plans or reservations, recurring charges will show on your invoice reconciliation data even if your daily charges are zero. Once your usage exceeds the agreed units or quantities, any extra usage will be billed 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 are only applicable to first-party Azure products, with some exceptions. To determine a customer's Azure credit amount, review the invoice reconciliation and daily rated usage data using the following steps.

Invoice reconciliation

  • Credit line items display a negative value for Subtotal and Total.
  • The ChargeType for credit line items is "customerCredit", while the CreditReasonCode describes the credit type, like "Azure Credit Offer".
  • Invoice reconciliation data shows usage and credit received. Organize transactions by ProductID, SKUID, AvailabilityID, OrderDate, and either CustomerName or CustomerID. To find the credit amount, sum up the Total.
  • The BillableQuantity for charge and credit line items should be the same.

Daily rated usage

  • Filter by customer tenant ID, type of credit in CreditType, and 100 inCrditPercentage.
  • To find the credit amount, sum 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 monitor your pay-as-you-go charges for telco products, review your daily usage and monthly invoice reconciliation data. Here's the process:

  • 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 call minutes and costs for each service. Check BillableQuantity for call minutes and Total for monthly costs.
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, once you use up your free talk time, any extra call minutes will be billed as pay-as-you-go charges. To reconcile the charges and call minutes between your daily usage and monthly invoice, use the ProductId and SKUId.

Your charges and call minutes are broken down by call type (domestic, international, and toll-free). So, you can see exactly what you're billed for. The sum of BillingPreTaxTotal and the Subtotal (before tax) are the same 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