Forum Discussion
Helper IExtract a specific text with DAX or in Custom Column in PowerQuery (vehicule registration number)
Hi
I have a set of data from which I need to extract a registration number with the following structure: "[x]-a-[y]" knowing that:
x and y are composed of a nonspecific number of digits
a is a unique letter (can be any letter from a to z)
Here is an example of my input in the first column with the wanted result in the second column below (ps: if there is no text with a registration number in it, give "NA" as the result
you can try this in PQ
= Table.AddColumn(#"Changed Type", "Custom", each
[a=Text.Remove([Column1],{"-"," "}),
b=Text.PositionOfAny(a,{"0".."9"},2),
c=Text.Middle(a,List.Min(b),List.Max(b)+1),
d=Text.PositionOfAny(c,{"a".."z","A".."Z"}),
e=try Text.Insert(Text.Insert(c,d,"-"),d+2,"-") otherwise null
][e])pls see the attachment below
Easy enough,
= Table.AddColumn(#"Changed Type", "SN", each let start = Text.PositionOfAny([Text], {"0".."9"}, 0), end = Text.PositionOfAny([Text], {"0".."9"}, 1) in try Text.Replace(Text.Range([Text],start,end-start+1), " ", "-") otherwise "")
11 Replies
- AmiraBedh
Super User
If you want to use DAX, you need to create a CC :
ExtractedCode = VAR HyphenPosition = FIND("-", [Name], 1, LEN([Name])) VAR CheckPattern = IF ( ISERROR(HyphenPosition), "NA", MID( [Name], HyphenPosition - 4, 10 ) ) RETURN IF ( ISERROR(VALUE(LEFT(CheckPattern, 1))), "NA", CheckPattern )
medmbchr1989Works like a charm, thanks!!
However, when I used your code, I discovered that some of the data are a bit different, below is an example (very large dataset, 10M lines); any way to resolve this?
- AmiraBedh
Glad to help don't forget to mark the answer as accepted please 🙂
You may need to study all the cases in your data.
I recommed you (since your data is large) to handle this before going to Power BI (using Python, SQL...)
- medmbchr1989
Hello, thanks for your input
Both not working unfortunately
- AmiraBedh
I updated the answer
- ThxAlot
Easy enough,
= Table.AddColumn(#"Changed Type", "SN", each let start = Text.PositionOfAny([Text], {"0".."9"}, 0), end = Text.PositionOfAny([Text], {"0".."9"}, 1) in try Text.Replace(Text.Range([Text],start,end-start+1), " ", "-") otherwise "")
