parsing digits with JSON or XML

[twailly]

Hi,

I've a table column containing product codes like :

DG124X

DG100X

DG10X

DG11P

TT750X

TT700P

Is there a simple JSON code that I could use to extract the leading text, middle number and trailing text  as new columns ?

DG | 124 | X

DG | 100 | X

DG | 10   | X

DG | 11   | P

TT  | 750 | X

TT  | 700 | P

Thanks for your help

[Anonymous · Accepted solution]

@twailly,

Please add a blank query in Power BI Desktop, paste the following code to the Advanced Editor of the blank query and check the process that how to extract numbers, leading text and trailing text from your column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnE3NDKJUIrVATMNDBBMOMswAMwKCTE3hQoCmQYGAeiavD2DDC0sDXSDAgLBfB/PID9LC0Mz3ZAgvxCl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Add custsom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Added Custom" = Table.AddColumn(#"Add custsom", "Custom.1", each Text.Combine(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then " " else _))),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1.1", type text}, {"Custom.1.2", type text}, {"Custom.1.3", type text}, {"Custom.1.4", type text}, {"Custom.1.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom.1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Custom.1.5", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Custom.1.5.1", "Custom.1.5.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.1.5.1", type text}, {"Custom.1.5.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Custom.1.5.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"Custom.1.3", "Custom.1.4", "Custom.1.5.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Custom.1.1", "Leading text"}, {"Merged", "trailing text"}, {"Custom", "Number"}})
in
    #"Renamed Columns"

Regards,
Lydia

[twailly]

Thanks a lot Lydia,

This is exactly what I needed.

I like to ask you another question : do you know a good resource where I can learn this query language ?

Regards

Thierry

[Anonymous]

@twailly,

Adding to other's post, do you get expected result when creating custom columns in Query Editor of Power BI Desktop?

Custom = Text.Start([Column1],2)
Custom.1 = Text.End([Column1],1)
Custom.2 = Text.Range([Column1],Text.Length([Custom]),Text.Length([Column1])-Text.Length([Custom])-Text.Length([Custom.1]))

Regards,
Lydia

[twailly]

Thanks Lydia

I was not clear enough in my problem statement. the Leading and trailing strings are variable in lenght. Like : KIR1890-RPQ, LIRN9816-TRNT, ... I need somehow to extract the 0 to 9 digits in the middle of a variable lenght string.

Thierry

[Greg_Deckler]

Do you always have 2 characters at the beginning and 1 at the end?

[twailly]

no, it can be 2 or 3