Use a Python script to annotate special points on a line chart

[v-xinruzhu-msft]

Scenario:  

In the use of Power BI's built-in visual objects, some users wish to annotate certain values in line charts. However, Power BI's built-in visual objects do not provide this functionality. As a workaround, we can use Python to create a line chart and annotate some special data points in it. Let's take a look at the example below.    

   

Expected Result: 

Firstly we have some sample data in an Excel Workbook as below.  

We would like to create a line chart to display these data with Date on X-axis and Value on Y-axis. In the line chart, we hope to highlight the maximum and minimum data points and annotate their values.ww 

How:  

Let's take a look at how to use Python to achieve this goal. 

1.First, you need to download a Python compiler from the official Python website. 
Download Python | Python.org 
  

2.Second, you need to install some libraries to handle Excel files and perform plotting. Here, pandas is mainly used to read Excel files, and matplotlib is used for plotting. Open the command line interface and run the following commands to install these libraries. 

pip install pandas matplotlib openpyxl 
 

3.In Power BI Desktop, go to Options -> Python scripting to configure the appropriate settings for the scripting environment.  

4.Click on the Python Visual in the list of visual objects in Power BI Desktop, and enter the following code in the code box.

import pandas as pd  
import matplotlib.pyplot as plt 
import matplotlib.dates as mdates #Import the appropriate package name 
df = pd.read_excel('xxx'(sample file path), header=0, names=['Date', 'Value']) #Replace the file address with your actual file address 
df['Date'] = pd.to_datetime(df['Date']) #Convert the ‘Date’ column to datetime format  
max_value = df['Value'].max() 
min_value = df['Value'].min() 
plt.figure(figsize=(10, 6))  #Create a new figure with a specified size (10x6 inches) 
plt.plot(df['Date'], df['Value'], marker='o', label='Values') #Plot the values against dates with circular markers. 
plt.scatter(df['Date'][df['Value'].idxmax()], max_value, color='red', s=100, zorder=3) #Scatter plot the maximum value point in red  
plt.scatter(df['Date'][df['Value'].idxmin()], min_value, color='green', s=100, zorder=3)  #Scatter plot the minimum value point in green  
plt.annotate(f'Max: {max_value}', (df['Date'][df['Value'].idxmax()], max_value), 
             textcoords="offset points", xytext=(30,10), ha='center', 
             arrowprops=dict(arrowstyle='-|>', 
            connectionstyle='angle,angleA=0,angleB=90,rad=10',color='black'))  #Annotate the maximum value with its corresponding date 
plt.annotate(f'Min: {min_value}', (df['Date'][df['Value'].idxmin()], min_value), 
             textcoords="offset points", xytext=(30,10), ha='center', 
             arrowprops=dict(arrowstyle='-|>', 
            connectionstyle='angle,angleA=0,angleB=90,rad=10',color='black'))  #Annotate the minimum value with its corresponding date 
plt.title('Line Plot of Values with Dates')  #Set the plot title 
plt.xlabel('Date')  #Set the x-axis label 
plt.ylabel('Value')  #Set the y-axis label 
plt.legend()  #Display the legend 
plt.gca().xaxis.set_major_locator(mdates.MonthLocator())  #Set major tick locations on the x-axis to months 
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m')) #Format the x-axis dates as ‘YYYY-MM’ 
plt.gcf().autofmt_xdate()  #Automatically format the x-axis date labels for better readability 
plt.grid(True)  #Display grid lines 
plt.show()  #Show the plot 

5.Finally, click on 'Run Script' to see the desired effect.  

Summary:  

When the functionality provided by Power BI's built-in visual objects is insufficient to meet users' needs, we can consider using Python scripts to achieve more complex requirements. 

Author:  Jialong Y. 

Reviewer: Ula and Kerry 

Use a Python script to annotate special points on a line chart.pbix