Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

v-rzhou-msft

The step between python process and M engine drops leading zeros

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

RicoZhou_0-1625623394426.png

 

RicoZhou_1-1625623394429.png

 

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.

RicoZhou_2-1625623394432.png

 

 

Author: Sam Zha

Reviewer: Ula Huang, Kerry Wang