Forum Discussion

twailly's avatar
twailly
Frequent Visitor
8 years ago
Solved

parsing digits with JSON or XML

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 a...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
twailly
Frequent Visitor
8 years ago

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