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

[Dave1]

Hi Shelley,

Sorry for my late response. Sure, you can do this transformation without adding and removing a column.

Try is formula:

= Table.ReplaceValue(#"Change Type", each if Text.Start([Contact_Number],1) = "0" then "0" else "Contact_Number","",Replacer.ReplaceText,{"Contact_Number"})

Change #"Change Type" by your most recent step name.

Please let me know if this helps you.

[Shelley]

@Dave1 I think we're really close. No errors or messages, but it appears the preceding zeros are still there. I must be missing something yet. Here's the code:

let
    Source = Sql.Database("DataService.cloudapp.net", "Master"),
    dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
    #"Remove 0s" = Table.ReplaceValue(dbo_RepairTransaction, each if Text.Start([Contact_Number],1) = "0" then "0" else "Contact_Number","",Replacer.ReplaceText,{"Contact_Number"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Remove 0s", "Key", each Text.Combine({[End_User_Key], [Contract_Number]}, "="), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}})
in
    #"Renamed Columns"

When I click on the last applied step, the zeros are still showing in the data - even after refreshing the preview. What am I missing?

[Dave1]

Hi Shelley, 

I am not able to generate the error message you received.  Technically, I don't see anything wrong with your code. 

  You can try this:          = each Text.TrimStart([Contract_Number], "0")

If possible, could you post a snapshot of some sample dataset? I assume that Contact_Number column is a text data type field. 

[Shelley]

Yes Contract Number is a text field type. Some contract numbers begin with or contain alpha characters, some are all numbers, while nulls from ERP system are shown as #. Below is the code I tried, including the "each." When I close it, all appears well, but it does not remove the leading zeros (from the contract numbers that have them) and when I click on the step, I now see the "parameter" message below. Could it be because every line doesn't have a preceding zero? Do I have to check first and then remove it, otherwise leave the row alone?

[Interkoubess]

Hi @Shelley,

So create a condition like this:

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if Text.Start([Contract_Number],1)="0" then Text.TrimStart([Contract_Number],"0") else [Contract_Number])

Let us know if it does not work.

Ninter

[Shelley]

@Interkoubess Thanks, Ninter. I'll try it, but this looks like it adds a column. Is there a way to do it without adding a column? This table contains millions of records and I hate to add another column that's essentially a duplicate. I was trying to avoid this.

[Interkoubess]

Yes @Shelley it added a column but you can delete your original [Contract_number] and rename the new column by Contract_number then you still have one column and it will work even if you load (future) data: this is M(agic).

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "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"}})

Let us know if it does not work...

Ninter

[Shelley]

@Interkoubess I think we're close, but I have to insert it above the step where I create a Key and so I think the sources are out of order now with the commands. It is not working. No errors or messages, but the preceding zeros persist. Here's the code. Line 3 is where I need to insert your suggestion.

let
    Source = Sql.Database("DataService.cloudapp.net", "Master"),
    dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "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"

[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

[Shelley · Accepted solution]

@Interkoubess Thanks for the reply. I finally got it to work, but another step (second to last line) had to be changed to point to the previous step. I couldn't have done it without you. Thanks!

let
    Source = Sql.Database("DataService.cloudapp.net", "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(#"Renamed Columns1", "Key", each Text.Combine({[End_User_Key], [Contract_Number]}, "="), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}})
in
    #"Renamed Columns"

[Shelley]

Thanks to both @Interkoubess and @Dave1 for your help. I really appreciate it!

[hjaf]

I just found this solution to make use of TrimStart 

= Table.TransformColumns(#"Previous Step",{{"Contract_Number", each Text.TrimStart(_, "0"), type text}})

Maybe useful to you or whoever may struggle with this in the future 🙂 

[Interkoubess]

Hi @Shelley,

Could you please give examples with sample data and expected outcomes and we will try to help.

Anyway what about changing the data type for example to Number in Power query, it removes leading zeros...

Ninter

[Shelley]

Good thought on changing from text to number to remove the leading zeros. I already thought of that and tried it, but there are some contract numbers with alpha characters, so this didn't work.