Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.