Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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,
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
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
@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
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
Do you always have 2 characters at the beginning and 1 at the end?
no, it can be 2 or 3
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |