Forum Discussion
Extract a specific text with DAX or in Custom Column in PowerQuery (vehicule registration number)
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 "")
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
)
Works 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?
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...)
I just finished my data analysis, what is different is that the "-" might have one or multiple spaces instead of them
Can you please provide again all the cases with a sample of data and expected output?
and this:
result should be : 9623-A-81
Result should be : 9787-A-81
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