Power BI visualization of Salesforce data entirely in Python

image.png

Overview

Both Salesforce and Power BI are craftsmanship, and I think that it is difficult for people who analyze and predict data with Python (I do not want to learn too much extra skill), but when I open the lid, it is Python code I've cooked everything, so I'll record it.

Beforehand

It's Windows because it uses Power BI Desktop. When I was managing the version with pyenv-win, I got stuck in the Power BI mystery error swamp, so I installed Python 3.8 in the MS Store. If it is 3.9, cdata will not be entered. Power BI Due to adult circumstances, numpy is also a version specification.

> pip install seaborn sqlalchemy numpy==1.19.3 

Extract Salesforce data with Dataframe

In SFDC projects, you may have used Dataloader to drop csv or $ sfdx since ancient times, but you don't need it anymore. You can create a Dataframe with just a few lines of code.

import cdata.salesforce as mod
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("salesforce:///[email protected];Password=xxx;SecurityToken=yyy;")
df = pd.read_sql("SELECT Column1, Column2 FROM Object1;", engine)
How to insert cdata

☟ Drop whl from here and pip. https://www.cdata.com/jp/drivers/salesforce/python/

> pip install ~/Downloads/CData.Python.Salesforce/win/Python38/cdata_salesforce_connector-20.0.7654-cp38-cp38-macosx_10_9_x86_64.whl 
How to get SecurityToken ☟ (I will borrow it)

https://qiita.com/kuwazzy/items/68bc7c4a5b36721cfe83

Get data without dare to use Salesforce connector

Where to use the connector properly ☟ ☟ Dare to import with a script.

☟ Put in the script and add df at the end. (The official doc is print (df), but I passed if I made a mistake) image.png

I'm talking about whether there is any merit, but if you force it

  1. Get only the columns you need
  2. Can be processed in advance
  3. If you do not change the setting, you can prevent the connector from setting Datetime to'Month'column (String) etc.
  4. You can copy and paste the Python file that you analyzed in advance.

Is that so?

Use Python Visual

☟ Select Py from Visualizations. image.png ☟ Check the column to use. image.png ☟ The code of ☟☟ will appear in the script editor, so I will add it. image.png image.png

The dataset is prepared from the beginning, and the graph is displayed when pyplot.show () is done at the end. As you can see by pressing the open button in the editor, the ☟ code is hidden.

# Prolog - Auto Generated #
import os, uuid, matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot
import pandas

os.chdir(u'C:/Users/user/PythonEditorWrapper_e52c53d3-3ad9-4b06-9404-d671e336fb61')
dataset = pandas.read_csv('input_df_bc91c5f1-75c9-47a1-be02-0e0ad04c0b60.csv')

matplotlib.pyplot.figure(figsize=(5.55555555555556,4.16666666666667), dpi=72)
matplotlib.pyplot.show = lambda args=None,kw=None: matplotlib.pyplot.savefig(str(uuid.uuid1()))
# Original Script. Please update your script content here and once completed copy below section back to the original editing window #
#The following code, which creates a dataframe and removes duplicate lines, is always executed and acts as a script preamble.

# dataset = pandas.DataFrame(Column1, Column2)
# dataset = dataset.drop_duplicates()

#Paste or enter the script code here.
##Hoge Hoge Hoge Hoge

# Epilog - Auto Generated #
os.chdir(u'C:/Users/user/PythonEditorWrapper_e52c53d3-3ad9-4b06-9404-d671e336fb61')

Create a dummy df and display the graph

image.png pd and plt are imported for ease of writing. If you prepare a preprocessing statement such as df = dataset, it is very convenient to use the Python code at hand as it is.

TIPS

Japanese localization (tofu measures)

import matplotlib.font_manager as fm
from matplotlib import rcParams
fm.findSystemFonts()
rcParams['font.family'] = 'sans-serif'
rcParams['font.sans-serif'] = ['Hiragino Maru Gothic Pro', 'Yu Gothic', 'Meirio', 'Takao', 'IPAexGothic', 'IPAPGothic', 'Noto Sans CJK JP']

Appearance

It seems that you have to adjust the numbers and label size for Power BI, and craftsmanship is absolutely necessary here, but I think that the hurdle is low for those with high pyplot power.

Machine learning

It seems that scikit-learn and xgboost can be used, so it seems that prediction results can also be output. (Still trying) https://docs.microsoft.com/en-us/power-bi/connect-data/service-python-packages-support

Recommended Posts

Power BI visualization of Salesforce data entirely in Python
Real-time visualization of thermography AMG8833 data in Python
Geographic information visualization of R and Python that can be expressed in Power BI
Automatically update Power BI Desktop in Python
Recommendation of Altair! Data visualization with Python
How to send a visualization image of data created in Python to Typetalk
The story of reading HSPICE data in Python
A well-prepared record of data analysis in Python
Python Data Visualization Libraries
Full-width and half-width processing of CSV data in Python
Not being aware of the contents of the data in python
List of Python code used in big data analysis
Let's use the open data of "Mamebus" in Python
Display UTM-30LX data in Python
Visualization of data by prefecture
Equivalence of objects in Python
Python application: data visualization # 2: matplotlib
Implementation of quicksort in Python
The Power of Pandas: Python
Try scraping the data of COVID-19 in Tokyo with Python
Basic summary of data manipulation in Python Pandas-Second half: Data aggregation
[Homology] Count the number of holes in data with Python
Comparison of data frame handling in Python (pandas), R, Pig
Get Leap Motion data in Python.
A simple data analysis of Bitcoin provided by CoinMetrics in Python
Read Protocol Buffers data in Python3
Get data from Quandl in Python
[ns3-30] Enable visualization of Python scripts
Division of timedelta in Python 2.7 series
Proper use of Python visualization packages
MySQL-automatic escape of parameters in python
Handle NetCDF format data in Python
Handling of JSON files in Python
Data visualization in Python-draw cool heatmaps
Implementation of life game in Python
Waveform display of audio in Python
Hashing data in R and Python
Impressions of touching Dash, a data visualization tool made by python
[Python] Visualization of longitudinal data (plot, boxplot, violin plot, confidence interval, histogram)
Easy data visualization with Python seaborn.
Law of large numbers in python
Implementation of original sorting in Python
Python application: data visualization part 1: basic
Reversible scrambling of integers in Python
Data analysis starting with python (data visualization 1)
[Blender Python] Arrange custom property data in template_list () of UI layout
Data analysis starting with python (data visualization 2)
Story of power approximation by Python
Power of forecasting methods in time series data analysis Semi-optimization (SARIMA) [Memo]
Memo of pixel position operation for image data in Python (numpy, cv2)
Plot CSV of time series data with unixtime value in Python (matplotlib)
Code reading of faker, a library that generates test data in Python
Python: Preprocessing in machine learning: Handling of missing, outlier, and imbalanced data
Get the key for the second layer migration of JSON data in python
Get additional data in LDAP with python
Conversion of string <-> date (date, datetime) in Python
Data input / output in Python (CSV, JSON)
Check the behavior of destructor in Python
Python Application: Data Visualization Part 3: Various Graphs
Python visualization tool for data analysis work
(Bad) practice of using this in Python