Forum Discussion
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
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
6 Replies
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,
LydiaThanks 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
- Greg_Deckler
Community Champion
Do you always have 2 characters at the beginning and 1 at the end?
no, it can be 2 or 3
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,
LydiaThanks 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
