Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I'm going crazy trying to extract a substring, matching particular pattern : 00-0000 (0=number). My idea is to use PowerQuery.
I've a long list of text like:
[Name]:
bar-cacciatopo-pluto
Pippo-PlutoCMD 17-1012 - asd
45-236 CMD 18-1011
topotopo 15-1500pippo -mao
My purpose is to extract all the 18-1000, if present:
a) determinate the position of all the "-" : using List.PositionOfAny([ListColumn], Occurrence.All) ?
b) check if
- in the two characters before are numbers AND
- in the next 4 characters are numbers
c) Extract these 2 characters & "-" & 4characters
At the moment I have no idea how to loop among all found "-" and so perform the desired checking...
Do you have any suggestions?
Many thanks!
Hi @volpinara
1.determinate the position of all the "-" : using List.PositionOfAny([ListColumn], Occurrence.All) ?
As tested, using List.PositionOfAny may cause errors.
2. to determinate the position of all the "-" , you could use Text.PositionOfAny
Add column->Custom column
Custom=Text.PositionOf([Name],"-") Custom.1=Text.PositionOf([Name],"-",Occurrence.All)
[Custom] Returns the position of the first occurrence of the text value substring found in text,
[Custom.1] Returns the position of all occurrence.
3. you could directly extract the "18-1000" substring
Assume your data has rows which have more than one string "18-1000" in each row, then you can extract each "18-1000".
Add column->Custom column
Custom=Text.PositionOf([Name],"18-1000",Occurrence.All) Custom.1=if [Custom]=null then null else Text.Middle([Name], [Custom], 7)
Code in Advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkos0k1OTE7OTCzJL8jXLcgpLclXitWJVgrILADyA0B8Z18XBUNzXUMDQyMFXYXE4hSwAhNTXSNjMwWwpAVI0hAsDDIGhBUMTXUNTQ0MCkDmKOjmJuajSYP0kCANE1GKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOf([Name],"18-1000",Occurrence.All)), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom]=null then null else Text.Middle([Name], [Custom], 7)) in #"Added Custom1"
Note: before add [Custom.1], please change the [Custom] to number data type.
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |