How to update my reports with the new Azure meter names

  • The steps outlined below can be used to filter the mapping spreadsheet we’ve provided to only the meters you consume. To learn more about the improvements to meter names, click here. Note: the screenshots below were taken from a Direct customer subscription. The values in each column may be different depending on your subscription.

    1. Download the list of standardized meters here, and save the file locally (e.g. c:\AzureMeterMapping-en.xls).
    2. Either download a detailed usage summary or create a workbook based on the output from one of the Azure APIs available and save the file locally (e.g. c:\AzureUsage.xls). See an example using a detailed usage file below.
      • If you are a CSP Partner, download a usage-based reconciliation file from Partner Center. Note: Currently, the unit of measure is not found in the reconciliation file or the CSP Price List. Effective July 2017, the unit of measure will be included both in the reconciliation file and the CSP Price List.
      • If you are a Direct customer, download the ‘Version 2—Preview’ file from the Azure Account Portal.
      • If you are an EA customer, download the Usage Detail report under Reports > Download Usage in the Enterprise Portal.
      Example of downloaded csv file
    3. Open the ‘AzureUsage.xls’ file and remove any data unrelated to your daily usage (e.g. statement or Provisioning Status information). Rename the worksheet to ‘Sheet 1’ if it isn’t already named ‘Sheet 1.’
    4. Clean up the data in ‘AzureUsage.xls’ before running a vlookup to interpret meter naming changes.
      1. To ensure that vlookups return valid results, make sure to remove the quotation marks around the Meter ID (Resource GUID) of ‘AzureUsage.xls’ using Excel’s Find/Replace function. Then, move the Meter ID (Resource GUID) column to the left to become Column A to help with vlookups, and save the file. The data to be used for comparison should look similar to that below (Note that the variations in meter attribute names shown as column headers in the table below will depend on your specific Azure subscription).
        Screenshoot from excel file
    5. Open the ‘AzureMeterMapping-en.xls’ file and build a standard vlookup on the Meter ID (Resource GUID) to the ‘AzureUsage.xls’ file.
      1. Add a column to the right of column A in the ‘AzureMeterMapping-en.xls’ file.
      2. Enter the vlookup function in cell B2: =VLOOKUP(A2,'[AzureUsage.xls]Sheet 1'!$A:$A,1,FALSE)
      3. Filter out the ‘N/A’ values from Column B to eliminate any meters you do not utilize. Your updated file will look like the example below. Filtered result
      4. The results in the ‘AzureMeterMapping-en.xls’ file will display only the meters you have consumed that are in your usage summary. See Columns C through G for the old meter naming conventions. See Columns H through L for the new naming conventions.
  • The following section will provide guidance on how to summarize the meters you’ve consumed into one combined report that displays both the old and new names. These instructions should be followed if you would like to see how your reports will look once the new names are implemented. To learn more about the improvements to meter names, click here. Note: the screenshots below were taken from a Direct customer subscription. The values in each column may be different depending on your subscription.

    1. Download the list of standardized meters here and save the file locally (e.g. c:\AzureMeterMapping-en.xls).
    2. Either download a detailed usage summary or create a workbook based on the output from one of the Azure APIs available and save the file locally (e.g. c:\AzureUsage.xls). See an example using a detailed usage file below.
      • If you are a CSP Partner, download a usage-based reconciliation file from Partner Center. Note: Currently, the unit of measure is not found in the reconciliation file or the CSP Price List. Effective July 2017, the unit of measure will be included both in the reconciliation file and the CSP Price List.
      • If you are a Direct customer, download the ‘Version 2—Preview’ file from the Azure Account Portal.
      • If you are an EA customer, download the Usage Detail report under Reports > Download Usage in the Enterprise Portal.
      Example of downloaded csv file
    3. Open the ‘AzureUsage.xls’ file and remove any data unrelated to your daily usage (e.g. statement or Provisioning Status information).
    4. Clean up the data in ‘AzureUsage.xls’ before running a vlookup to interpret meter naming changes.
      1. To ensure that vlookups return valid results, make sure to remove the quotation marks around all cells of the ‘AzureUsage.xls’ file using Excel’s Find/Replace function. Then, move the Meter ID (Resource GUID) column to the left to become Column A to help with vlookups, and save the file. The data to be used for comparison should look similar to that below (Note that the variations in meter attribute names shown as column headers in the table below will depend on your specific Azure subscription).
    5. Open a new Excel file and name it ‘Usage Summary – New Names.xls’. Save the file locally.
    6. Copy and paste the data from the ‘AzureUsage.xls’ file to the ‘Usage Summary – New Names.xls’ file in the following order like in the example below. Copy and paste the data from the ‘AzureUsage.xls’
    7. In the ‘Usage Summary – New Names.xls’ file, build a vlookup in columns O-S to pull in columns G-K in the ‘AzureMeterMapping-en.xls’ file. Include the headers as well. See the example screenshot below. Usage Summary screenshoot
    8. Build a pivot from the ‘Usage Summary – New Names.xls’ file.
      1. To create the pivot table above, follow the steps below:
        1. Add a new sheet to the ‘Usage Summary – New Names.xls’ file workbook, and name the sheet ‘After-Before Summary’.
        2. Highlight all cells in the ‘Usage Summary – New Names.xls’ file.
        3. Click the Insert Tab and click PivotTable. Create the Pivot table to After-Before Summary sheet.
        4. Add the following PivotTable fields to Rows.
          1. Meter ID (Resource GUID)
          2. Meter Category (Service Name or Name)
          3. Meter Sub-category (Service Type or Type)
          4. Meter Name (Resource Name, Resource, Service Resource)
          5. Meter Region (Region)
          6. Unit (Unit of Measure)
          7. New convention - Service Name, Name, or Meter Category
          8. New convention - Service Type, Type, or Meter Sub-category
          9. New convention - Resource Name, Resource, Service Resource or Meter Name
          10. New convention - Meter Region or Region
          11. New convention - Unit or Unit of Measure
        5. Add the Consumed Quantity field to Values as a Sum of Consumed Quantity.
        6. Go to the Design tab and click Report Layout. Click Show in Tabular Form in the drop-down.
        7. Right-click on each column header, and uncheck “Subtotal <header name>” (e.g. Subtotal “Meter Category (Service Name or Name)”).
    9. Your final output should look like the below example: Final output screenshoot
  • The following section will provide guidance on how to summarize the meters you’ve consumed into one combined report that displays both the old and new names. These instructions should be followed if you need to keep using the old names in your reports temporarily after the new names are implemented. To learn more about the improvements to meter names, click here. Note: the screenshots below were taken from a Direct customer subscription. The values in each column may be different depending on your subscription.

    1. Download the list of standardized meters here, and save the file locally (e.g. c:\AzureMeterMapping-en.xls).
    2. Either download a detailed usage summary or create a workbook based on the output from one of the Azure APIs available and save the file locally (e.g. c:\AzureUsage.xls). See an example using a detailed usage file below.
      • If you are a CSP Partner, download a usage-based reconciliation file from Partner Center. Note: Currently, the unit of measure is not found in the reconciliation file or the CSP Price List. Effective July 2017, the unit of measure will be included both in the reconciliation file and the CSP Price List.
      • If you are a Direct customer, download the ‘Version 2—Preview’ file from the Azure Account Portal.
      • If you are an EA customer, download the Usage Detail report under Reports > Download Usage in the Enterprise Portal.
      Example of downloaded csv file
    3. Open a new Excel file and name it ‘Usage Summary – Old Names.xls’. Save the file locally.
    4. Clean up the data in ‘AzureUsage.xls’ before running a vlookup to interpret meter naming changes.
      1. To ensure that vlookups return valid results, make sure to remove the quotation marks around all cells of the ‘AzureUsage.xls’ file using Excel’s Find/Replace function. Then, move the Meter ID (Resource GUID) column to the left to become Column A to help with vlookups, and save the file. The data to be used for comparison should look similar to that below (Note that the variations in meter attribute names shown as column headers in the table below will depend on your specific Azure subscription).
    5. Copy and paste the data from the ‘AzureUsage.xls’ file to the ‘Usage Summary – Old Names.xls’ file in the following order like in the example below. Copy and paste the data from the ‘AzureUsage.xls’
    6. In the ‘Usage Summary – Old Names.xls’ file, build a vlookup in columns O-S to pull in columns B-F in the ‘AzureMeterMapping-en.xls’ file. Include the headers as well. See the example screenshot below. Usage Summary screenshoot
    7. Build a pivot from the ‘Usage Summary – Old Names.xls’ file.
      1. To create the pivot table above, follow the steps below:
        1. Add a new sheet to the ‘Usage Summary – New Names.xls’ file workbook, and name the sheet “After-Before Summary”.
        2. Highlight all cells in the ‘Usage Summary – New Names.xls’ file.
        3. Click the Insert Tab and click PivotTable. Create the Pivot table to After-Before Summary sheet.
        4. Add the following PivotTable fields to Rows.
          1. Meter ID (Resource GUID)
          2. Meter Category (Service Name or Name)
          3. Meter Sub-category (Service Type or Type)
          4. Meter Name (Resource Name, Resource, Service Resource)
          5. Meter Region (Region)
          6. Unit (Unit of Measure)
          7. Old convention - Service Name, Name, or Meter Category
          8. Old convention - Service Type, Type, or Meter Sub-category
          9. Old convention - Resource Name, Resource, Service Resource or Meter Name
          10. Old convention - Meter Region or Region
          11. Old convention - Unit or Unit of Measure
        5. Add the Consumed Quantity field to Values as a Sum of Consumed Quantity.
        6. Go to the Design tab and click Report Layout. Click Show in Tabular Form in the drop-down.
        7. Right-click on each column header, and uncheck “Subtotal <header name>” (e.g. Subtotal “Meter Category (Service Name or Name)”).
    8. Your final output should look like the below example: Final output screenshoot
  • The steps outlined below can be used to filter the mapping spreadsheet we’ve provided to only the meters you consume based solely on the meter name attributes. You would use these steps prior to the name changes taking effect if you do not have a usage file with the Meter ID (Resource GUID). To learn more about the improvements to meter names, click here. Note: the screenshots below were taken from a Direct customer subscription. The values in each column may be different depending on your subscription.

    1. Download the list of standardized meters here, and save the file locally (e.g. c:\AzureMeterMapping-en.xls).
    2. Open a new Excel file and name the file (e.g. ‘Old Names.xls’). Save the file locally. Add the following headers, which will need to be customized based on your Azure subscription. See the example below.
      Screenshoot from excel file
    3. Add the old name data from your file to the ‘Old Names.xls’ file based on the headers you’ve created. See the example screenshot below. Screenshoot from excel file
    4. In the ‘Old Names.xls’ file, in Column F, you will need to concatenate the data in each row. Enter the following formula in Cell F2: =CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2). See the example screenshot below.
      Screenshoot from excel file
      1. Copy and paste the content in Column F as text only, this will remove formulas but keep the results in the cells. Then, move column F to column A. Your file should appear similar to the one below.
        Copy and paste the data from the ‘AzureUsage.xls’
    5. In the ‘AzureMeterMapping-en.xls’ file, add a column between column F and G. Repeat Step 3 and 3i by concatenating columns B-F in the ‘AzureMeterMapping-en.xls’ file. See the example screenshot below.
      Screenshoot from excel file
    6. In the ‘AzureMeterMapping-en.xls’ file, add another column to the right-side of column G.
    7. Build a standard vlookup on column G in the ‘AzureMeterMapping-en.xls’ file to column A in the ‘Old Names.xls’ file.
      1. To build a standard vlookup, enter the vlookup function in cell H2: =VLOOKUP(G2,'[Old Names.xls.xlsx]Sheet1'!$A:$A,1,FALSE)
      2. The legacy names may have changed over time and therefore, will not display a match. See your current detailed usage summary from the Azure Management Portal to find the current name of your meter if a match is not found.
    8. Filter Column H to remove results with ‘N/A.’ Once you’ve filtered, the ‘AzureMeterMapping-en.xls’ file will only display the meters in your ‘Old Names.xls’ file. To view the new names aligned for the meters in your ‘Old Names.xls’ file, scroll to the right of the workbook to view columns I-M. See the results in the example screenshot below. Final output screenshoot
  • The steps outlined below can be used to filter the mapping spreadsheet we’ve provided to only the meters you consume based solely on the meter name attributes. You would use these steps after the name changes take effect if you do not have a usage file with the Meter ID (Resource GUID). To learn more about the improvements to meter names, click here. Note: the screenshots below were taken from a Direct customer subscription. The values in each column may be different depending on your subscription.

    1. Download the list of standardized meters here, and save the file locally (e.g. c:\AzureMeterMapping-en.xls).
    2. Open a new Excel file and name the file (e.g. ‘New Names.xls’). Save the file locally. Add the following headers, which will need to be customized based on your Azure subscription. See the example below.
      Screenshoot from excel file
    3. Add the new name data from your file to the ‘New Names.xls’ file based on the headers you’ve created. See the example screenshot below. Screenshoot from excel file
    4. In the ‘New Names.xls’ file, in Column F, you will need to concatenate the data in each row. Enter the following formula in Cell F2: =CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2). See the example screenshot below.
      Screenshoot from excel file
      1. Copy and paste the content in Column F as text only, this will remove formulas but keep the results in the cells. Then, move column F to column A. Copy and paste the data from the ‘AzureUsage.xls’
    5. In the ‘AzureMeterMapping-en.xls’ file, in column L, repeat Step 4 by concatenating columns G-K in the ‘AzureMeterMapping-en.xls’ file. See the example screenshot below.
      Screenshoot from excel file
    6. Build a standard vlookup on column G in the ‘AzureMeterMapping-en.xls’ file to column A in the ‘Old Names.xls’ file.
      1. To build a standard vlookup, enter the vlookup function in cell H2: =VLOOKUP(G2,'[Old Names.xls.xlsx]Sheet1'!$A:$A,1,FALSE)
      2. The legacy names may have changed over time and therefore, will not display a match. See your current detailed usage summary from the Azure Management Portal to find the current name of your meter if a match is not found.
    7. Filter Column M to remove results with ‘N/A’. Once you’ve filtered, the ‘AzureMeterMapping-en.xls’ file will only display the meters in your ‘New Names.xls’ file. To view the old names aligned for the meters in your ‘New Names.xls’ file, scroll to the left of the workbook to view columns B-F. See the results in the example screenshot below. Final output screenshoot
  • If you have further questions concerning the meter attribute naming changes, please contact Azure Support.