Forum Discussion

Seth4040's avatar
Seth4040
Regular Visitor
1 year ago
Solved

Trim data

I have data that I need trimmed either a DAX or under Transform Data in the desktop.  I have a account number that need to be 56702737-2 (Example) and some of these have data before the account numbe...
Need Help
  • v-sathmakuri's avatar
    v-sathmakuri
    1 year ago

    Hi Seth4040  , 

    Thank you for reaching out to Microsoft Fabric Community.

     

    In Power BI, open the Power Query Editor, select your table, and add a new custom column using the formula below. Also attached the pbix file for reference.

     

    =Text.Middle(Text.Select(Text.Range([Vendor Invoice], Text.PositionOfAny([Vendor Invoice], {"0".."9"})), {"0".."9", "-"}), 0, 11)

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thank you!!

BhavinVyas3003's avatar
BhavinVyas3003
Super User
1 year ago

Try to add similar m code by creating new column say Clean Account Number,

let
txt = [Vendor Invoice],
pattern = Text.Select(txt, {"0".."9", "-"}),
dashPos = Text.PositionOf(pattern, "-"),
account =
if dashPos = 8 and Text.Length(pattern) >= 10
then Text.Middle(pattern, 0, 10)
else null
in
account

  • Seth4040's avatar
    Seth4040
    Regular Visitor
    1 year ago

    Sorry I am trying to see where to get this and start within Power Query.