[Power Query] Custom Functions

 

 

 

 

 

 

let
    IsValidEmail = (input as text) =>
    let
        name = Text.BeforeDelimiter(input,"@",{0,RelativePosition.FromEnd}),
        ext = Text.AfterDelimiter(input,".",{0,RelativePosition.FromEnd}),
        domain = Text.BetweenDelimiters(input,name&"@","."&ext),
        return =
            Text.Length(name) > 0 and
            Text.Length(domain) > 0 and
            Text.Length(ext) > 1 and Text.Length(ext) < 6 and
            name = Text.Select(name, {"a".."z","A".."Z","0".."9","_","-","."}) and
            domain = Text.Select(domain, {"a".."z","A".."Z","0".."9","_","-","."}) and
            ext = Text.Select(ext, {"a".."z","A".."Z"})
    in
        return
in
    IsValidEmail

 

 

Text.Length(name)

let
    IsValidEmail = (input) =>
        if input = "" or input = null then null
        else
    let
        name = Text.BeforeDelimiter(input,"@",{0,RelativePosition.FromEnd}),
        ext = Text.AfterDelimiter(input,".",{0,RelativePosition.FromEnd}),
        domain = Text.BetweenDelimiters(input,name&"@","."&ext),
        return =
            Text.Length(name) > 0 and
            Text.Length(domain) > 0 and
            Text.Length(ext) > 1 and Text.Length(ext) < 6 and
            name = Text.Select(name, {"a".."z","A".."Z","0".."9","_","-","."}) and
            domain = Text.Select(domain, {"a".."z","A".."Z","0".."9","_","-","."}) and
            ext = Text.Select(ext, {"a".."z","A".."Z"})
    in
        return
in
    IsValidEmail

 

 

 

 

https://www.myonlinetraininghub.com/power-query-custom-functions

 

https://stackoverflow.com/questions/61173583/is-there-a-way-i-can-use-a-regex-in-power-query-from-excel-without-using-r