Power bi python график

Use Python in Power Query Editor

You can use Python, a programming language widely used by statisticians, data scientists, and data analysts, in the Power BI Desktop Power Query Editor. This integration of Python into Power Query Editor lets you perform data cleansing using Python, and perform advanced data shaping and analytics in datasets, including completion of missing data, predictions, and clustering, just to name a few. Python is a powerful language, and can be used in Power Query Editor to prepare your data model and create reports.

Prerequisites

You’ll need to install Python and pandas before you begin.

• Install Python — To use Python in Power BI Desktop’s Power Query Editor, you need to install Python on your local machine. You can download and install Python for free from many locations, including the Official Python download page, and the Anaconda.
• Install pandas — To use Python with the Power Query Editor, you’ll also need to install pandas. Pandas is used to move data between Power BI and the Python environment.

Use Python with Power Query Editor

To show how to use Python in Power Query Editor, take this example from a stock market dataset, based on a CSV file that you can download from here and follow along. The steps for this example are the following procedure:

1. First, load your data into Power BI Desktop. In this example, load the EuStockMarkets_NA.csv file and select Get data >Text/CSV from the Home ribbon in Power BI Desktop.
2. Select the file and select Open, and the CSV is displayed in the CSV file dialog.
3. Once the data is loaded, you see it in the Fields pane in Power BI Desktop.
4. Open Power Query Editor by selecting Transform data from the Home tab in Power BI Desktop.
5. In the Transform tab, select Run Python Script and the Run Python Script editor appears as shown in the next step. Rows 15 and 20 suffer from missing data, as do other rows you can’t see in the following image. The following steps show how Python completes those rows for you.
6. For this example, enter the following script code:

import pandas as pd completedData = dataset.fillna(method='backfill', inplace=False) dataset["completedValues"] = completedData["SMI missing values"] 

Note You need to have the pandas library installed in your Python environment for the previous script code to work properly. To install pandas, run the following command in your Python installation: pip install pandas

When put into the Run Python Script dialog, the code looks like the following example:

After you select OK, Power Query Editor displays a warning about data privacy.

For the Python scripts to work properly in the Power BI service, all data sources need to be set to public. For more information about privacy settings and their implications, see Privacy Levels. Notice a new column in the Fields pane called completedValues. Notice there are a few missing data elements, such as on row 15 and 18. Take a look at how Python handles that in the next section.

With just three lines of Python script, Power Query Editor filled in the missing values with a predictive model.

Create visuals from Python script data

Now we can create a visual to see how the Python script code using the pandas library completed the missing values, as shown in the following image:

Once that visual is complete, and any other visuals you might want to create using Power BI Desktop, you can save the Power BI Desktop file. Power BI Desktop files save with the .pbix file name extension. Then use the data model, including the Python scripts that are part of it, in the Power BI service.

Want to see a completed .pbix file with these steps completed? You’re in luck. You can download the completed Power BI Desktop file used in these examples right here.

Once you upload the .pbix file to the Power BI service, a couple more steps are necessary to enable data to refresh in the service and to enable visuals to be updated in the service. The data needs access to Python for visuals to be updated. The other steps are the following steps:

• Enable scheduled refresh for the dataset. To enable scheduled refresh for the workbook that contains your dataset with Python scripts, see Configuring scheduled refresh, which also includes information about Personal Gateway.
• Install the Personal Gateway. You need a Personal Gateway installed on the machine where the file is located, and where Python is installed. The Power BI service must access that workbook and re-render any updated visuals. For more information, see install and configure Personal Gateway.

Considerations and limitations

There are some limitations to queries that include Python scripts created in Power Query Editor:

• All Python data source settings must be set to Public, and all other steps in a query created in Power Query Editor must also be public. To get to data source settings, in Power BI Desktop select File > Options and settings > Data source settings. From the Data Source Settings dialog, select the data sources and then select Edit Permissions. and ensure that the Privacy Level is set to Public.
• To enable scheduled refresh of your Python visuals or dataset, you need to enable Scheduled refresh and have a Personal Gateway installed on the computer that houses the workbook and the Python installation. For more information on both, see the previous section in this article, which provides links to learn more about each.
• Nested tables, which are table of tables, are currently not supported.

There are all sorts of things you can do with Python and custom queries, so explore and shape your data just the way you want it to appear.

Feedback

Submit and view feedback for

Источник

Create Power BI visuals with Python

This tutorial helps you get started creating visuals with Python data in Power BI Desktop. You use a few of the many available options and capabilities for creating visual reports by using Python, pandas, and the Matplotlib library.

Prerequisites

• Install Python on your local machine.
• Enable Python scripting in Power BI Desktop.
• Install the pandas and Matplotlib Python libraries.
• Import the following Python script into Power BI Desktop:

import pandas as pd df = pd.DataFrame(< 'Fname':['Harry','Sally','Paul','Abe','June','Mike','Tom'], 'Age':[21,34,42,18,24,80,22], 'Weight': [180, 130, 200, 140, 176, 142, 210], 'Gender':['M','F','M','M','F','M','M'], 'State':['Washington','Oregon','California','Washington','Nevada','Texas','Nevada'], 'Children':[4,1,2,3,0,2,0], 'Pets':[3,2,2,5,0,1,5] >) print (df) 

Create a Python visual in Power BI Desktop

1. After you import the Python script, select the Python visual icon in the Power BI Desktop Visualizations pane.
2. In the Enable script visuals dialog box that appears, select Enable. A placeholder Python visual image appears on the report canvas, and the Python script editor appears along the bottom of the center pane.
3. Drag the Age, Children, Fname, Gender, Pets, State, and Weight fields to the Values section where it says Add data fields here. Based on your selections, the Python script editor generates the following binding code.
   • The editor creates a dataset dataframe with the fields you add.
   • The default aggregation is Don’t summarize.
   • Similar to table visuals, fields are grouped and duplicate rows appear only once.
4. With the dataframe automatically generated by the fields you selected, you can write a Python script that results in plotting to the Python default device. When the script is complete, select the Run icon from the Python script editor title bar to run the script and generate the visual.

Tips

• Your Python script can use only fields that are added to the Values section. You can add or remove fields while you work on your Python script. Power BI Desktop automatically detects field changes. As you select or remove fields from the Values section, supporting code in the Python script editor is automatically generated or removed.
• In some cases, you might not want automatic grouping to occur, or you might want all rows to appear, including duplicates. In those cases, you can add an index field to your dataset that causes all rows to be considered unique and prevents grouping.
• You can access columns in the dataset by using their names. For example, you can code dataset[«Age»] in your Python script to access the age field.
• Power BI Desktop replots the visual when you select Run from the Python script editor title bar, or whenever a data change occurs due to data refresh, filtering, or highlighting.
• When you run a Python script that results in an error, the Python visual isn’t plotted, and an error message appears on the canvas. For error details, select See details in the message.
• To get a larger view of the visualizations, you can minimize the Python script editor.

Create a scatter plot

Create a scatter plot to see if there’s a correlation between age and weight.

In the Python script editor, under Paste or type your script code here, enter this code:

import matplotlib.pyplot as plt dataset.plot(kind='scatter', x='Age', y='Weight', color='red') plt.show() 

Your Python script editor pane should now look like the following image: The code imports the Matplotlib library, which plots and creates the visual.

Select the Run script button to generate the following scatter plot in the Python visual.

Create a line plot with multiple columns

Create a line plot for each person that shows their number of children and pets.

Under Paste or type your script code here, remove or comment out the previous code, and enter the following Python code:

import matplotlib.pyplot as plt ax = plt.gca() dataset.plot(kind='line',x='Fname',y='Children',ax=ax) dataset.plot(kind='line',x='Fname',y='Pets', color='red', ax=ax) plt.show() 

Select the Run button to generate the following line plot with multiple columns:

Create a bar plot

Create a bar plot for each person’s age.

Under Paste or type your script code here, remove or comment out the previous code, and enter the following Python code:

import matplotlib.pyplot as plt dataset.plot(kind='bar',x='Fname',y='Age') plt.show() 

Select the Run button to generate the following bar plot:

Limitations

Python visuals in Power BI Desktop have the following limitations:

• The data the Python visual uses for plotting is limited to 150,000 rows. If more than 150,000 rows are selected, only the top 150,000 rows are used, and a message appears on the image. The input data also has a limit of 250 MB.
• If the input dataset of a Python visual has a column that contains a string value longer than 32,766 characters, that value is truncated.
• All Python visuals display at 72 DPI resolution.
• If a Python visual calculation exceeds five minutes, the execution times out, which results in an error.
• As with other Power BI Desktop visuals, if you select data fields from different tables with no defined relationship between them, an error occurs.
• Python visuals refresh upon data updates, filtering, and highlighting. The image itself isn’t interactive.
• Python visuals respond to highlighting elements in other visuals, but you can’t select elements in the Python visual to cross-filter other elements.
• Only plots to the Python default display device display correctly on the canvas. Avoid explicitly using a different Python display device.
• Python visuals don’t support renaming input columns. Columns are referred to by their original names during script execution.

Security

Python visuals use Python scripts, which could contain code that has security or privacy risks. When you attempt to view or interact with a Python visual for the first time, you get a security warning. Enable Python visuals only if you trust the author and source, or after you review and understand the Python script.

Licensing

Python visuals require a Power BI Pro or Premium Per User (PPU) license to render in reports, refresh, filter, and cross-filter. Users of free Power BI can consume only tiles that are shared with them in Premium workspaces.

The following table describes Python visuals capabilities based on licensing.

Author Python visuals in Power BI Desktop	Create Power BI service reports with Python visuals	View Python visuals in reports
Guest (Power BI embedded)	Supported	Not supported	Supported in Premium/Azure capacity only
Unmanaged tenant (domain not verified)	Supported	Not supported	Not supported
Managed tenant with free license	Supported	Not supported	Supported in Premium capacity only
Managed tenant with Pro or PPU license	Supported	Supported	Supported

For more information about Power BI Pro licenses and how they differ from free licenses, see Purchase and assign Power BI Pro user licenses.

Next steps

This tutorial barely scratches the surface of the options and capabilities for creating visual reports using by Python, pandas, and the Matplotlib library. For more information, see the following resources:

For more information about Python in Power BI, see:

Источник