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)

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

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

Is that so?

Use Python Visual

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

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

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

Power BI visualization of Salesforce data entirely in Python