I'm trying to remove leading zeros with m language in the query editor

[Shelley]

Hi All,

What the heck am I doing wrong? I bet it is something tiny, but because I don't know the M Language, I'm missing it. I need to remove leading zeros from contract numbers prior to creating a Key. I cannot figure out the proper exact command.

I am doing this:

= Text.TrimStart([Contract_Number], "0")

I receive: "Expression. Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

Any ideas as to what I am missing?

Thanks!

[Interkoubess · Accepted solution]

Hi @Shelley,

Please try this code (I only changed the step Removed Columns to dbo_RepairTransaction in order to take into account the previous step:

let
    Source = Sql.Database("CSMDataService.cloudapp.net", "CSM_Master"),
    dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
    #"Added Custom" = Table.AddColumn(dbo_RepairTransaction, "Custom", each if Text.Start([Contract_Number],1)="0" then Text.TrimStart([Contract_Number],"0") else [Contract_Number]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Contract_Number"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Contract_Number"}}),
    #"Inserted Merged Column" = Table.AddColumn(dbo_RepairTransaction, "Key", each Text.Combine({[End_User_Key], [Contract_Number]}, "="), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}})
in
    #"Renamed Columns"

Let us know.

Ninter