Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
twailly
Frequent Visitor

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 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

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@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"

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

@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"

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

v-yuezhe-msft
Employee
Employee

@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]))

1.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

no, it can be 2 or 3

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.