Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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:
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:
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:
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |