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

Appropriate roles: Billing admin | Global admin

Download reconciliation files from Partner Center to view charges for each billing period. Check charges for each customer's subscriptions and events affecting them, like adding licenses mid-term. For invoice details, see Read your bill.

Understand reconciliation file fields

Here are the reconciliation files and what they show:

Understand charge types in reconciliation data

Learn about the different types of charges in reconciliation data in the ChargeType attribute.

Fix formatting issues

Reconciliation line items might have formatting issues if the en-us locale isn't used.

To address the formatting issues, follow these steps:

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. Select Data on the ribbon, then select Text to Columns.
5. Choose Delimited file type, then select Next.
6. Check Comma as the delimiter (if Tab is already selected, you can leave this option selected) then select Next.
7. Choose Date:MDY for the column data format, then select Next.
8. Select Text for all amount columns, then select Finish.

Download reconciliation files programmatically

Reconciliation files can be large and are sometimes difficult to download from the portal. To download large reconciliation files programmatically, use Get invoice line items.

Where do I download the file asynchronously?

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

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

2. Go to the Billing history page and select Recurring and one-time purchases (billing currency) tab.

3. Or, go to the Billing overview (NCE) page instead.

4. Choose the reconciliation file you want to download. A message at the top of the page indicates that the file is being prepared and should be ready on the Reports (NCE) page soon. Processing time varies with the data volume.

5. Monitor your download's progress in the status column.

6. Save time and prevent duplication. Check already downloaded reports before submitting a new request.

What each status means:

• Queued: Request is submitted
• Processing: System is collecting data for the report
• Completed: Report is available for download
• Failed: An error occurred while generating the report
• No data found: System couldn't find any data for the report

If your file exceeds the row limit in Excel

If you can't open your .csv file in Microsoft Excel, the file likely contains more rows than Excel allows. To resolve this issue, you can:

• Open a reconciliation file in an Excel Power Query
• Open a reconciliation file in Power BI

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 Values) reconciliation file.

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

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

   

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

   

7. Select OK to start the loading process. Wait until it's complete.

8. See the file name and number of rows on the right side. Double-click this area to open the Power Query Editor.

   

9. As you scroll down, you see that new rows are added dynamically.

   Tip

   Fix data loading problems caused by line breaks:

   • Go to the "Advanced Editor" under "Home" tab.
   • Find the Csv.Document function.
   • Update the "QuoteStyle" value to "QuoteStyle.csv."

10. To search for specific line item, use the filter option as shown in the screenshot. Additionally, aggregate line items or perform calculations with the "Group By" or "Statistics" features.

    

    In the spreadsheet, the PartnerId box drop-down is open, and the Text Filters drop-down is highlighted.

11. For instance, to find the number of billable days in the charge cycle, use the Custom Column feature to calculate the duration between the charge start and end dates. Power Query also provides a range of features for different computations.

    

    In the ribbon, Custom column is highlighted. In the spreadsheet, the BillableDays column is highlighted. On the side, the Query Settings tab shows options for Properties and Applied Steps.":::

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

    

Open a reconciliation file in Power BI

To open a reconciliation file in Power BI:

1. Download the reconciliation file as you normally would.
2. Download, install, and open an instance of Microsoft Power BI.
3. Select Get data on the Power BI Home tab.
4. Choose Text/CSV from the list of Common data sources.
5. Open your reconciliation file when prompted.

Negative amount displayed

Some reconciliation line items show negative charges for the following reasons:

• You changed your subscription by canceling, upgrading, or altering licenses.
• You received credit for a service level agreement (SLA) violation, moving to Azure platform, or price adjustments.

Understand these negative amounts using the ChargeType attribute in the reconciliation line items.

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

To calculate costs, always use the EffectiveUnitPrice, not the UnitPrice. The EffectiveUnitPrice depends on these factors:

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

The subscription's UnitPrice remains the same during the billing term. A change in the price might appear in the reconciliation data, but it doesn't affect the cost.

Use EffectiveUnitPrice to confirm the cost and invoice your customers.

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

For some products, like Azure savings plans or reservations, you pay a fixed price monthly, annually, or one time for limited units. We don't charge you for daily usage of these products until you go over the limit. You only see the recurring charges on your invoice reconciliation line items.

Here's what you need to know:

• The fixed price you agreed to pay for the product is still due every month, even if you don't use it. This fixed price is the recurring charge on your invoice reconciliation line item.
• You pay no extra charges until you exceed the limit.
• You pay extra at a pay-as-you-go rate for any usage over the limit you agreed to.

You see recurring charges for Azure savings plans or reservations on your invoice data even if you don't use them. You need to pay extra charges at pay-as-you-go rate for any usage beyond the agreed limit.

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

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

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

Pay-as-you-go product

• To identify your Azure subscription, link the SubscriptionId on your invoice reconciliation line item with the SubscriptionId on your daily rated usage line item. The EntitlementId of the same line of your daily rated usage is your Azure subscription ID.

Azure reservation

• Link theReservationOrderId of your invoice reconciliation and the AdditionalInfo of the daily rated usage line item. The EntitlementId of the same line of your daily rated usage is your Azure subscription ID.

Azure savings plan

• Link the ReservationOrderId of your invoice reconciliation line item to the BenefitOrderId of the daily rated usage line item. The EntitlementId of the same line of your daily rated usage is your Azure subscription ID.

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

Azure credits only cover Microsoft Azure products, with some exceptions. To find out how much credit a customer has, check the invoice reconciliation and daily rated usage data.

Invoice reconciliation

• Subtotal and Total show a negative amount for credit line items.
• Use customerCredit as the ChargeType for credit line items. The CreditReasonCode indicates the credit type, such as "Azure Credit".
• To reconcile credits, group transactions by these fields: ProductId, SKUId, AvailabilityId, OrderDate, and either CustomerName or CustomerId. Add up the Total to get the credit amount.
• Charge and credit line items should have the same BillableQuantity.

Daily rated usage

• To filter the line items, use the customer tenant ID, the CreditType, and 100 as the CrditPercentage value.
• To find the credit amount, sum up the BillingPreTaxTotal.

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

Follow these steps to track your telco product charges on a daily and monthly basis:

• Daily rated usage reconciliation: Check your daily charges under BillingPreTaxTotal and your call minutes in "NumMin" under AdditionalInfo.

• Invoice reconciliation: Check your total call minutes and costs per call, look at BillableQuantity and Subtotal.

If you exceed your free talk time, you pay for each extra minute. To reconcile the charges and call minutes between your daily usage and monthly invoice, use the ProductId and SkuId.

Check charges and minutes by call type: domestic, international, or toll-free. The bill shows total call minutes and total amount before tax. The subtotal equals the sum of BillingPreTaxTotal, and BillableQuantity equals the sum of "NumMin."

How to find tiered pricing in the reconciliation data

Tiered pricing depends on your usage tier. Find the tiered pricing in your reconciliation data by looking at these fields:

• The UnitPrice in the line item shows the unit price for the first tier in the price list.

• The EffectiveUnitPrice in the line item reflects your usage tier's unit price from the price list. Adjustments like partner earned credit may change the final price.

• The PriceAdjustmentDescription indicates the discount percentage for your usage tier and other adjustments that affect the final price.

• See the final cost in individual line items based on your usage tier.

What do different prefixes for Reservation Order ID mean?

The prefix indicates where you bought your Azure reservations.

1. PCTest: From Partner Center for testing
2. RIOderId: From Partner Center for production
3. No prefix: From Microsoft Cost Management

Related content

• Understand your bill and reconciliation file—learn how to find them in Partner Center