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

Appropriate roles: Billing 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 improve the download experience and boost efficiency, the Partner Center now processes new commerce reconciliation files asynchronously. To download a file:

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

2. Go to the Billing overview (NCE) task menu > Billing | Billing Overview (NCE) page and choose the year > select the invoice number > find the reconciliation file on the side panel > select Download report.

3. Look for a notification at the top of the page indicating that your file is being processed. This file should be available shortly on the Reports (NCE) page, depending on the volume of data.

4. Monitor your download's progress on the Reports (NCE) page using the status column of the request line item.

5. Check already downloaded reports before submitting a new request to save time and prevent duplication.

Status meanings:

• Queued: Request submitted
• Processing: Data collection in progress
• Completed: Report ready for download
• Failed: Error in generating the report
• No data found: No relevant data available for the report

If your file exceeds the row limit in Excel

If you can't open your .csv file in Microsoft Excel because it exceeds Excel's row limit, you have these options to open the file:

• 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:

• To reflect changes in your subscription due to cancellations, upgrades, or license modifications.
• To account for credits received from service level agreement (SLA) violations, Azure platform migration, or price adjustments.

Understand these negative amounts by referring to the ChargeType attribute in the reconciliation line items.

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

To accurately calculate costs, always use the EffectiveUnitPrice, not the UnitPrice. The following factors influence the EffectiveUnitPrice:

• Promotions, tiered pricing, and adjustments such as partner earned credit.

• The number of days billed, counted from when a billing event occurs until the end of the charge cycle.

During the billing term, the subscription's UnitPrice remains constant. If you notice UnitPrice variance in the reconciliation data, rest assured that it doesn't affect the actual cost.

To ensure accurate billing and customer invoicing, use EffectiveUnitPrice.

Why does my invoice reconciliation data show a recurring charge for a product without daily charges?

For certain products, such as Azure savings plans or reservations, a fixed price is billed monthly, annually, or as a one-time payment for a specified limit. Here's a clearer breakdown:

Key points to understand

• Fixed price commitment: The recurring charge on your invoice reflects the fixed price you agreed to pay, regardless of usage. This agreement ensures you have access to the product within the agreed limit without daily charges.

• No extra charges until limit exceeded: You only incur more costs once your usage surpasses the set limit and the pay-as-you-go rate applies.

• Visible recurring charges: Even if you don't use the product, the recurring charge for Azure savings plans or reservations still appears on your invoice data. Extra charges apply only when usage exceeds the agreed limit.

To effectively monitor your costs, make sure to regularly review how your Azure reservation or savings plan is linked to your Azure subscription. This proactive approach can help you manage your expenses efficiently and ensure you're only paying for what you use above your agreed limits.

To review the costs, check how an Azure reservation or savings plan is 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 resource

• To identify your Azure subscription, first find the SubscriptionId on your invoice reconciliation line item. Then, match it with the SubscriptionId on your daily rated usage line item. The EntitlementId from the same line of your daily rated usage is your Azure subscription ID.

Azure reservation

• To identify your Azure subscription for an Azure reservation, find the ReservationOrderId on your invoice reconciliation line item. Link it with the AdditionalInfo on the daily rated usage line item. The EntitlementId from the same line of your daily rated usage is your Azure subscription ID.

Azure savings plan

• To identify your Azure subscription for an Azure savings plan, connect the ReservationOrderId on your invoice reconciliation line item with the BenefitOrderId on the daily rated usage line item. The EntitlementId from the same line of your daily rated usage is your Azure subscription ID.

How to identify Azure credits in invoice reconciliation and daily rated usage data

Understanding how to track Azure credits is essential for effective reconciliation and credit management of Azure products. Here's how you can find these credits in both invoice reconciliation and daily rated usage data.

Invoice reconciliation

To identify the credits:

• Use the ChargeType field and filter the "customerCredit" line items. The CreditReasonCode specifies the type of credit.

• Look for Subtotal and Total fields showing negative amounts for these line items.

• For accuracy, group line items by these fields: ProductId, SKUId, AvailabilityId, OrderDate, and either CustomerName or CustomerId. Then, sum the Total to determine the credit amount.

• Ensure the BillableQuantity is the same for both charge and credit line items.

Daily rated usage

To filter and find credit amounts:

• Use the CustomerId, CreditType, and 100 for the CreditPercentage value to filter the line items.

• Sum the BillingPreTaxTotal of these line items to find the credit amount.

By following these steps, you can accurately track and manage Azure credits, ensuring a clear understanding of your reconciliation data.

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.

Understanding Reservation Order ID prefixes

To ensure a smooth experience with Azure reservations, it's essential to understand the meaning behind different Reservation Order ID prefixes. The prefix indicates where you bought your Azure reservations from:

• PCTest: Indicates reservations bought from Partner Center for testing purposes.
• RIOderId: Denotes reservations bought from Partner Center for production use.
• No prefix: Refers to reservations purchased through Microsoft Cost Management.

By recognizing these prefixes, you can better manage your reservations and streamline your Azure operations.

Related content

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