The step between python process and M engine drops…

Issue Description:
When using Python script in Query Editor, the step between python process and M engine automatically converts text (00123) to number (123), i.e. it drops leading zeros.

 

Currently, this is by design.

 

Environment:

Power BI Desktop Version: 2.93.981.0 64-bit (May 2021) and earlier versions

 

Repro Steps:

1. Add a blank query and paste code below to the Advanced Editor

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMjCCUMYGSrGxAA==", 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}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each "'" & [Column1]),

    #"Run Python script" = Python.Execute("# 'dataset' holds the input data for this script#(lf)dataset[""Column1_new""] = dataset[""Custom""].str.slice(start=1)",[dataset=#"Added Custom"]),

    dataset = #"Run Python script"{[Name="dataset"]}[Value]

in

    dataset

 

2. Check data in [Column1] and note it drops leading zeros

 

 

Workaround: 

We may use Power Query M language to add a single quote ( ‘ ) in front of the text value and then remove it in Python script.

 

See data in [Column1_new] and note it keeps leading zeros.

 

 

Author: Sam Zha

Reviewer: Ula Huang, Kerry Wang