Replace value on column by value of another column and trim the value

[Anonymous]

Hello,

I am trying to copy the values from column 'ref_id' into column 'material'. The ref_id values are of the form 00000000001234-KG, and I want to copy just the number on 'material', so it would be 1234 in this case.

This is what I'm trying and its result. Note that in the image I'm trying to remove leading zeros only. I'm waiting for your answer to add the part to remove what is after "-".

Any help?

[sevenhills · Accepted solution]

Power Query ~ can be done using Number.FromText

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkAGJhYmFrre7kqxOnSQiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ref_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ref_id", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "material", each Number.FromText(Text.BeforeDelimiter([ref_id], "-")), Int64.Type)
in
    #"Inserted Text Before Delimiter"

Ref: https://docs.microsoft.com/en-us/powerquery-m/number-fromtext