Transform Run Python Script Step Data Types

[Anonymous]

Hello Power BI users,

When using a Run Python Script transformation in Power BI using pandas all integer/float columns have the Text data type after the transformation and need to be changed back to number. As the dataset is large I expect this transformation to text and back to number consumes a lot of unnecessary computing power.

Additionaly, when converting the text to decimal number, the decimal seperator is interpreted incorrectly, causing "1.0" to be converted to "10", instead of "1,0".

The optimal solution would be to allow numerical columns added in a Run Python Scripts step to be interpreted as decimal/whole numbers instead of text.

Run Python Script Step

# 'dataset' holds the input data for this script

dataset['ones_int'] = 1

dataset['ones_int_dtype'] =dataset['ones_int'].dtype

dataset['ones_float'] = 1.0

dataset['ones_float_dtype'] = dataset['ones_float'].dtype

Output of Run Python Script step (note "ones_int" and "ones_float" are of type ABC, thus text )

Output of changing column types (note "ones_float" are converted to 10 instaed of 1,0)

[Syndicate_Admin]

Hello Power BI Community,

ENGLISH

After a few weeks I found the solution and it is embarrassingly simple, just add "en-US" to the changed type step. Thus the changed type step becomes:

SPANISH

After a few weeks, I found the solution and it is embarrassingly simple, just add "en-US" to the modified type step. Therefore, the changed type step becomes:

[Anonymous · Accepted solution]

Hello Power BI Community,

After a few weeks I found the solution and it is embarrassingly simple, just add "en-US" to the changed type step. Thus the changed type step becomes:

[V-lianl-msft]

Hi @Anonymous ,

Have you defined the data type in the script? 

import pandas as pd
data = [['A',1],['B',2],['C',3]]
df = pd.DataFrame (data, columns=['type','value'], dtype=float)
print(df)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft,

The problem occurs when reading the data back in Power BI, thus after the Python Script Step. In Python the data type is for example float64, however Power BI interprets it as text.

The data types of the Pandas DataFrame are thus not correctly interpreted in Power BI after processing the data in Python, int64 and float64 are both interpreted as text.

Kind regards,

Mark