Forum Discussion
Helper IExtract 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 "")
Super UserGlad 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...)
Helper Iand this:
result should be : 9623-A-81
Result should be : 9787-A-81
ryan_mayuyou 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