Trim a string with character then with 5 digits (C#####)

[on_qq]

Hi, how can I return a new column with the format in first character follow with 5 digits in power query? Thanks a lots. 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYwxDoAgEAS/sqG2gQOB8rS2QitCDDG0Fkp8vwTtNrOTiVFIReBpRqhXKXXAokhqEmmIgnXbavfeI+Sz5t/pnzEG8q7gp+AYrSPdsSKNtTT+qdwC1mELLFJ6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Address", type text}})
in
#"Changed Type"

[tackytechtom · Accepted solution]

Hi @on_qq ,

How about this?

Credits to @ImkeF.

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYwxDoAgEAS/sqG2gQOB8rS2QitCDDG0Fkp8vwTtNrOTiVFIReBpRqhXKXXAokhqEmmIgnXbavfeI+Sz5t/pnzEG8q7gp+AYrSPdsSKNtTT+qdwC1mELLFJ6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),
    #"ChangedType" = Table.TransformColumnTypes(Source,{{"Address", type text}}),
    #"CharactersToReplace" = Text.Combine(List.RemoveItems(List.Transform({1..126}, each Character.FromNumber(_)),{"0".."9"})),
    Convert=Table.AddColumn(ChangedType, "Custom1", each Text.SplitAny(
                            [Address],
                            CharactersToReplace)
                            ),
    Convert2=Table.AddColumn(Convert, "Custom2", each List.Select(
			[Custom1], 
			(li) => Text.Length(li) >=5){0}
			),
    #"Added Custom" = Table.AddColumn(Convert2, "Custom", each Text.Range ([Address], Text.PositionOf ([Address], [Custom2]) - 1, 6))
in
    #"Added Custom"

I suppose the idea is to fetch the postal code that is in the shape of "A12345". Be aware, though, that in case you have a street number with five or more digits, the solution above might not work as expected.

Let me know if this helps! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/