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 the charges for each customer's subscriptions and the events that affect them, such as adding licenses mid-term. For detailed invoice information, refer to read your bill.

Understand reconciliation file fields

Here are the reconciliation files and what they show:

Reconciliation file field	Includes
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 invoice reconciliation	Aggregated costs, quantities, and usage for all new commerce products for a billing period

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

To ensure accurate financial auditing and reporting, it's crucial to have detailed reconciliation files. These files often contain large amounts of transactional data, which can be challenging to download directly from the portal.

To make this process smoother, use the billed invoice reconciliation API. This method helps you retrieve detailed invoice data efficiently, even for large files.

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.

3. Select the invoice number.

4. Find the reconciliation file on the side panel.

5. Select Download report.

6. 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.

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

8. Check already downloaded reports on the Reports (NCE) page before submitting a new request to save time and prevent duplication.

Status meanings:

• Queued: Request submitted and waiting to be processed.
• Processing: Data collection is currently in progress.
• Completed: Report ready for download and you can access it.
• Failed: There was an error in generating the report; try again or contact support.
• No data found: No relevant data was available for the report; check your criteria and try again.

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: Power Query allows you to import and transform data from .csv files without facing the constraint of Excel's row limit. It's useful for large datasets that require data cleaning and manipulation before analysis.
• Open a reconciliation file in Power BI: Power BI is a powerful tool for handling large datasets. It provides robust visualization and reporting capabilities, making it ideal for comprehensive data analysis.

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. For instance, if the ChargeType is "customerCredit," it indicates a refund due to SLA violations, Azure credit, or price adjustments.

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: Discounts or special offers that reduce the price.

• Tiered pricing: Different prices based on the quantity consumed.

• Adjustments such as partner earned credit: Credits earned through managed service to your customers that reduce the cost.

• Additionally, the price might be adjusted based on the number of days billed. The number of days billed is counted from when a billing event occurs until the end of the charge cycle. For instance, if a billing event occurs on the 10th day of a 30-day charge cycle, the price is adjusted based on the remaining 20 days of the charge cycle.

It's important to note that 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. Always use EffectiveUnitPrice for precise cost calculations.

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

Understanding how billing works for certain products, such as Azure savings plans or reservations, can help you manage your costs effectively. Here's a detailed breakdown:

Key points to understand

• Fixed price commitment: You're billed a fixed amount each month, no matter how much you use the service. For example, if you agree to a fixed price of USD 100 per month for a specific Azure service, you're billed this amount each month regardless of usage.

• No extra charges until the limit is exceeded: You don't get extra charges until you exceed the limit. Once your usage surpasses the agreed limit, the pay-as-you-go rate applies. For instance, if your limit is 100 hours of virtual machine usage, and you use 120 hours, you're charged for the extra 20 hours at the pay-as-you-go rate.

• 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 because you committed to a fixed price agreement. Extra charges apply only when usage exceeds the agreed limit. For example, if you agree to pay USD 100 per month for a service, this amount is billed every month regardless of usage. 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. Take control of your Azure costs today by regularly reviewing your subscription links and usage limits!

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:

Tip

To easily identify products purchased through different or the same Azure subscriptions, consider using resource groups or tags. This approach ensures clarity by linking each product with its respective subscription, even when subscriptions have identical names. It allows billing admins to quickly identify and organize resources based on their subscription, simplifying tracking and reporting, and boosting operational efficiency.

Pay-as-you-go resource

To identify your Azure subscription ID for any pay-as-you-go resources,

• Find the SubscriptionId on your invoice reconciliation line item.

• 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 ID for an Azure reservation,

• Find the ReservationOrderId on your invoice reconciliation line item.

• Connect it with the "ReservationOrderId" within 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 ID for an Azure savings plan,

• Find the ReservationOrderId on your invoice reconciliation line item.

• Connect it 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 helping to identify the reason behind the credit.

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

• For accuracy, group "customerCredit" 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 to maintain consistency and accuracy in your credit management process.

Daily rated usage

To filter and find credit amounts:

• Use the CustomerId, CreditType (for example, Azure credit offer), and 100 for the CreditPercentage value to filter the line items.

• Sum the BillingPreTaxTotal of these line items to find the credit amount. This step ensures that you capture the total pretax credit amount accurately for your records.

Note

Some Azure credits might not appear in the daily rated usage data. Always refer to the invoice reconciliation data for complete credit information.

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. This method ensures that your daily charges and call minutes are accurately recorded and billed.

Note

Quantity in the daily rated usage data represents an approximation and not the exact call minutes used.

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

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

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.225	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.220755	47.7
Total							10.89							49.3

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, which categorizes your usage into different levels based on the volume of resources consumed. To find the tiered pricing in your reconciliation data, look at the following fields and ensure you're optimizing your usage to benefit from the best rates.

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

• EffectiveUnitPrice: The "EffectiveUnitPrice" reflects the unit price for your usage tier from the price list. Adjustments like partner-earned credit might change the final price.

• PriceAdjustmentDescription: The "PriceAdjustmentDescription" indicates any discounts or adjustments that affect the final price, such as a 10% discount or partner-earned credit.

For the final cost, check the EffectiveUnitPrice and PriceAdjustmentDescription attributes 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 identify the source of each reservation, 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