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

Appropriate roles: Billing admin | Global admin

Partner Center allows you to download reconciliation files for a detailed view of each charge in a billing period.

You can see the charges for each customer's subscriptions, and the events that affect them (such as adding more licenses to a subscription in the middle of a term).

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:

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, reconciliation line items 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. 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.

5. You see a message at the top of the page. It says that the file is being prepared and should be ready on the Reports (NCE) page soon. Processing time varies with the amount of data.

6. The status shows the progress of your request. Search for existing reports before you download a new one to save time and avoid duplication.

What each status means:

• Queued: Request is 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 find 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 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 Value) 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, and wait for the loading process to complete.

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

   

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

   Tip

   To fix data loading problems caused by line breaks:

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

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.

    

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

    

    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, you can transfer the limited transaction data to Excel by following these steps.

    

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

Some reconciliation line items might have negative charges because:

• You changed your subscription, such as canceling, upgrading, or changing licenses.
• You received credit for a service level agreement (SLA) violation, Azure usage, or price adjustments.

The ChargeType attribute in the reconciliation line items explains more.

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: Here, you can see how many minutes you call and pay every day. Your daily charges are under BillingPreTaxTotal. Your call minutes are in "NumMin" under AdditionalInfo.

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

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

You can check your charges and minutes by call type: domestic, international, or toll-free. Your bill shows the total amount and the subtotal before tax. The subtotal matches the sum of BillingPreTaxTotal and the BillableQuantity matches the sum of "NumMin."

How to find tiered pricing in the reconciliation data

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

• The UnitPrice is the unit price for the first tier in the price list.

• The EffectiveUnitPrice is based on the unit price for your usage tier from the price list. Other adjustments (example, partner earned credit) might change the final price.

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

• You can 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: Purchases in Partner Center for testing purposes
2. RIOderId: Purchases in Partner Center for real customers
3. No prefix: Purchases through Microsoft Cost Management

Next steps

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