Extracting part of a string - before, after and in between characters

[onedayover]

Hi

I have a table with a field "source_id". Can anyone help me with how to create a new column "new_source_id" extracting the data as it appears in the example below. Data will vary for each source id but this is pretty much all there is in terms of combinations:

source_id	new_source_id
delete_A1
delete_EAI_A_1
delete_sandbox
0107	0107
0107-sandbox	0107
0107-imports	0107
0107-QTeachPD	0107
0107-teaching	0107
0107-profdev	0107
delete_0107	0107
delete_0107-imports	0107
delete_0107-sandbox	0107
delete_0107-teaching	0107
delete_0107-QTeachPD	0107
2351	2351
2351-sandbox	2351
2351-imports	2351
2351-QTeachPD	2351
2351-teaching	2351
2351-profdev	2351
delete_2351	2351
delete_2351-imports	2351
delete_2351-sandbox	2351
delete_2351-teaching	2351
delete_2351-QTeachPD	2351
A182	A182
A182-sandbox	A182
A182-imports	A182
A182-QTeachPD	A182
A182-teaching	A182
delete_A182	A182
delete_A182-imports	A182
delete_A182-sandbox	A182
delete_A182-teaching	A182
delete_A182-QTeachPD	A182
0018-0107-profdev	0018-0107
A092-A092-profdev	A092-A092

Thanks 🙂

[tamerj1 · Accepted solution]

Hi @onedayover 
If you wish to create a new column using dax you can use the following code (attached sample file)

new_source_id = 
VAR Digits = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" )
VAR String = Data[source_id]
VAR Items1 = SUBSTITUTE ( String, "_", "|" )
VAR Items2 = SUBSTITUTE ( Items1, "-", "|" )
VAR Length = PATHLENGTH ( Items2 )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Item", PATHITEM ( Items2, [Value] ) )
VAR T3 = 
    FILTER ( 
        T2,
        VAR Length2 = LEN ( [@Item] )
        VAR T4 = GENERATESERIES ( 1, Length2, 1 )
        VAR T5 = ADDCOLUMNS ( T4, "@Letter", MID ( [@Item], [Value], 1 ) )
        VAR T6 = FILTER ( T5, [@Letter] IN Digits )
        RETURN  
            COUNTROWS ( T6 ) > 1
    )
RETURN
    CONCATENATEX ( T3, [@Item], "-" )

[onedayover]

Hi tamerj1, this is great work, thank you. Just one thing, how would I ensure that the leading zero isn't dropped in the new column? For example, row 4 the 107 should be 0107 as will all souce ids that start with 0.

[tamerj1]

Hi @onedayover 

leading zero is never dropped in this solution. Row 4 originally does not contain a leading zero. 

[amitchandak]

@onedayover , Try this code, paste this in a blank query in power bi, This will work only for numbers.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZJRC4IwFIX/Suw5YRqRPQ7qobeC3kTEcpVQKirRz0/NbefqhV4c9/vGztkwikSmn7rVifLFUixEvLRkrw6JSma4SYvsUn4slr7cdMOwmNlzmyjPX1VZt82Mn846vT6Ou5loe54X95mo6vKW6TfyseC0EGAuHzVTGzVXBj13i2C17p9wWMwMOZS7epTDwVRAIyrc81g+Np0WAszlo2Zqo+bKoOduofww6OZhMTPkUO7qUQ4HUwGNrLD/Ow0GzOWgZuqh/hPKtpXSD73pf23Y7z5yG3jDx+2wTMTxFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source_id = _t, new_source_id = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"source_id", type text}, {"new_source_id", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "source_id", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"source_id.1", "source_id.2", "source_id.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"source_id.1", type text}, {"source_id.2", type text}, {"source_id.3", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "source_id.1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"source_id.1.1", "source_id.1.2", "source_id.1.3", "source_id.1.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"source_id.1.1", type text}, {"source_id.1.2", type text}, {"source_id.1.3", type text}, {"source_id.1.4", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each Text.Combine ({if (try Number.FromText([source_id.1.1]) otherwise null) <> null then [source_id.1.1] else null ,
if (try Number.FromText([source_id.1.2]) otherwise null) <> null then [source_id.1.2] else null,
if (try Number.FromText([source_id.1.3]) otherwise null) <> null then [source_id.1.3] else null,
if (try Number.FromText([source_id.1.4]) otherwise null) <> null then [source_id.1.4] else null,
if (try Number.FromText([source_id.2]) otherwise null) <> null then [source_id.2] else null,
if (try Number.FromText([source_id.3]) otherwise null) <> null then [source_id.3] else null

}, "-"))
in
#"Added Custom"