cancel
Showing results for 
Search instead for 
Did you mean: 

CSV exported with Pyton or R script does not respect data types

When using Power Query to run an Python or R script that writes data to a CSV, the resulting CSV automatically converts text to numbers and drops leading zeroes, even though the data type is specified as text both in Power Query and in the Python/R script. The issue is not present when running exactly the same script directly in R or Python, so it seems to be an issue related to Power Query.

 

Here are some examples scripts that can be used to reproduce the issue. Note that the data type is specified as text both in Power Query and inside the Python/R script.

Using R script:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA3MDUzU4rViVYyNDQwMDICMw2MDQwMgcxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)dataset$Column1 <- as.character(dataset$Column1)#(lf)fileName <- paste(""C:/test.txt"")#(lf)write.table(dataset, sep = "","", file = fileName, quote = FALSE, row.names = FALSE, col.names = FALSE)",[dataset=#"Changed Type"])
in
    #"Run R script"

 

 

Using Python script:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA3MDUzU4rViVYyNDQwMDICMw2MDQwMgcxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Run Python script" = Python.Execute("# 'dataset' holds the input data for this script#(lf)import pandas#(lf)data = pandas.DataFrame(dataset)#(lf)data = data.astype(str)#(lf)data.to_csv('C:/test.txt')",[dataset=#"Changed Type"])
in
    #"Run Python script"

 

 

Input data is (as text):

070566
110022
030012 

 

Expected output is:

070566
110022
030012

 

Actual output is:
70566
110022
30012

 

See more in these topics:
https://community.powerbi.com/t5/Power-Query/Keep-leading-zeroes-when-export-to-CSV-using-R-or-Pytho...

https://community.powerbi.com/t5/Power-Query/Python-script-step-drop-leading-zeros/m-p/1587259

 

Status: New
Comments
v-chuncz-msft
Community Support

@magnus_b 

 

The issue is reported internally and I will keep you posted with any updates.

CRI 240539388

v-chuncz-msft
Community Support

@magnus_b 

 

This is by design. Our Product Group is aware of the issue and tracking it on internal item 597269. We appreciate your feedback which definitely helps us improve the product and user experience.