M Code to extract between characters that are not same position

Power Query
1

Hi all,

Does anyone know how to extract the year from this text string? I trired “Text Extraction” and “Delimiter” but it is not coming out the way I want.

I want the year in one column but I am also showing “Gen”.

Thank you for your help

image
image692×112 10.6 KB

2

Hi @ysherriff ,

One question - if Gen-YYYY format is fix you can use

Split by Delimiter - custom delimiter like:

image

And then Split by Delimiter - by position (4 digits for year) :

image

Pbix Example:

Sample_for_ysherriff.pbix (17.3 KB)
Sample1.xlsx (8.6 KB)

2 Likes
3

@ysherriff You can use this: ysherriff.pbix (25.3 KB)

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45Wck/N0zUyMDTSdczJUQhOTVeK1cEh6JSTn64LkTEy0nX3DcAlGgsA",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ #"Campaign Name" = _t ]
    ),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Campaign Name", type text } } ),
    AddedCustom = 
        Table.AddColumn (
            ChangedType,
            "Custom",
            each 
            Number.From ( 
                List.Select (
                    Text.Split ( _[Campaign Name], "-" ),
                    each Value.Is ( Value.FromText ( _ ), Int64.Type )
                ){0}
            ),
            Int64.Type
        )
in
    AddedCustom

Another option:

Table.AddColumn (
    ChangedType,
    "Custom",
    each List.RemoveNulls (
        List.Transform (
            Text.Split ( _[Campaign Name], "-" ),
            each try Number.From ( _ ) otherwise null
        )
    ){0},
    Int64.Type
),
3 Likes
4

Perfect Mr. Sharma. Much appreciated.

And thanks for your willingness to help mspanic. Much appreciated

1 Like