Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Remove leading Zero's in Query

Hi,   I need to remove leading zeros from a character string, ideally at query level.   I have seen the solution: http://community.powerbi.com/t5/Desktop/Remove-leading-zero-s-from-a-string/m-p/...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    You can just choose Transform - Foormat - Trim and then adjust the generated code to have zeroes trimmed from the start.

     

    let
    Source = #table({"String"},List.Zip({{"000MarcelBeug","000Ol"}})),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"String", each Text.TrimStart(_,"0")}})
in
    #"Trimmed Text"
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

You can just choose Transform - Foormat - Trim and then adjust the generated code to have zeroes trimmed from the start.

 

let
    Source = #table({"String"},List.Zip({{"000MarcelBeug","000Ol"}})),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"String", each Text.TrimStart(_,"0")}})
in
    #"Trimmed Text"
Shelley's avatar
Shelley
Post Prodigy
8 years ago

@MarcelBeug

Hi There, I'm wondering if you'd be so kind as to help me too. I am also trying to remove leading zeros, but not every record has them. Some records are alphanumeric. I have tried using your code below like this:

 

let
    Source = Sql.Database("CSMDataService.cloudapp.net", "CSM_Master"),
    dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
    #"Trimmed Text" = Table.TransformColumns(dbo_RepairTransaction,{{"Contract_Number", each Text.TrimStart(_, "0")}}),
    #"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"

 

It appears to run without any errors or messages, but it also doesn't work.

 

Thanks in advance for any advice.

 

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    It doesn't work because you forgot to adjust the step reference in the next step.

     

    let
    Source = Sql.Database("CSMDataService.cloudapp.net", "CSM_Master"),
    dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
    #"Trimmed Text" = Table.TransformColumns(dbo_RepairTransaction,{{"Contract_Number", each Text.TrimStart(_, "0")}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Trimmed Text", "Key", each Text.Combine({[End_User_Key], [Contract_Number]}, "="), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}})
in
    #"Renamed Columns"