Using Auvik Stats APIs to create line charts in Power BI

Creating a Power BI line chart is as easy as selecting the line chart visualization and adding data in the required fields. But preparing the data so it displays properly requires some preparation. 

Auvik’s Power BI templates already come with much of the data preparation done—but if  you want to add more data sources from our Stats APIs that aren’t included in the templates, follow the process below.

Prepare the data from Auvik’s APIs queries to easily create visualizations

Create a timestamp that includes the date and time

The timestamp Auvik provides in our APIs is in Unix minutes, and needs to be converted to a format Power BI can understand as date and time.

1. In the Power Query editor, click Add column, then select Custom Column.
2. Set the new column name to StatisticTimeStamp.
3. In the Column custom formula field, enter:
   

=#datetime(1970,1,1,0,0,0) +  #duration(0,0,0,([#"Unix-Minutes Timestamp"]*60))

By multiplying by 60, this function converts Unix minutes to standard Unix time. It then converts the standard Unix time to a standard calendar date and time. 

4. Click OK to create the new column with the given name and formula. 
5. After the new column is created, make sure the format selected is Date/Time.

Extract time from the timestamp into a separate column

1. In the Power Query editor, click Add new column, then select Custom Column.
2. Set the new column name to TimeColumn. 
3. In the Column custom formula field enter:
   

=DateTime.Time([StatisticTimeStamp])

Extract the date from the timestamp into a separate column

1. In the Power Query editor, click Add new column, then select Custom Column.
2. Set the new column name to DateColumn.
3. In the Column custom formula field enter:

=DateTime.Date([StatisticTimeStamp])

Create a Sort-TimeStamp column

The Sort-TimeStamp column allows you to rearrange the rows in chronological order based on the timestamps.

1. In the Data Menu in Power BI, click New column.

2. Use this formula in the new column:
   

StatSortOrder = FORMAT(Query1[StatisticTimeStamp], "YYYYMMDDHHMMSS")

The output needs to be formatted as YYYYMMDDHHMMSS. This specific formatting ensures that when the table sorts, the data is correctly ordered to the second. 

3. After the new column is created, ensure the column format is set to Whole number. Select the StatSortOrder column, go to Column tools, open the drop-down, and select Whole number.

4. Right-click on the title of the StatSortOrder column and from the drop-down select Sort ascending. This re-sorts the table in chronological order.

 

Create the dashboard visualization

1. From the Report menu in Power BI, select the line chart visualization from the visualizations panel.
   
   
   
2. Drag and drop the StatTimeStamp metric to the X-axis in the visualization. You need to use the timestamp from the specific statistic you’re using, not from other statistics.

3. For the legend, add a column that says Device Name or something similar that helps you identify and differentiate between devices.
4. For Values, drag the metric that you want plotted, such as device bandwidth, RAM usage, etc. 
5. Select an appropriate measurement for that metric. You’ll see a drop-down arrow next to the value of the metric. For most data provided by Auvik, the Maximum measurement is the most appropriate, as it displays the maximum value at the interval.

Note: If your initial line chart plots only a single or a few dots, you may need to drill down in the data by clicking the down-pointing arrows in the chart a few times until data shows. See image below.

If the line chart isn’t displaying values appropriately (especially in regards to dates), then follow the steps in this next section: 

1. Go to the Data menu in Power BI.
2. Click on the table you’re working on and select the StatTimeStamp column.
3. If you look at the Data Type column, it likely says Date/Time. 
4. Change the format of that column to Text. Accept any warnings you may see. After the column has been converted to text format, revert the format of the StatTimeStamp column back to Date/Time format.