Trim data

[Seth4040]

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 number and sometimes after the account number or both before and after the account number.  I need help being able to trim so I just have the account number.  I have attached a screenshot, the ones in pink I need to trim and the one in green is what they should be.

[v-sathmakuri]

Hi @Seth4040 ,

I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @Seth4040 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @Seth4040 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[BhavinVyas3003]

Click on custom column and apply the M Query as per your requirement.

[DataNinja777]

Hi @Seth4040 ,

To extract the account number using DAX within Power BI, you can create a calculated column that isolates the numeric pattern in the format of XXXXXXXX-X (i.e., 7 to 8 digits, a hyphen, and a single digit). This solution assumes that the account number consistently follows that structure and appears somewhere within the text string, possibly surrounded by additional characters.

You may use the following DAX expression for the calculated column:

Extracted Account Number = 
VAR TextString = [Vendor Invoice]
VAR HyphenPos = SEARCH("-", TextString, 1, -1)
VAR StartPos = 
    IF(
        HyphenPos >= 8,
        HyphenPos - 8,
        1
    )
VAR Extracted = MID(TextString, StartPos, 10)
RETURN
    IF(
        ISNUMBER(VALUE(LEFT(Extracted, 1))),
        Extracted,
        BLANK()
    )

This formula identifies the position of the hyphen within the string, calculates the appropriate starting point for extraction by assuming 8 characters precede the hyphen, and then extracts a 10-character substring. It includes a basic validation to ensure that the extracted string begins with a numeric character. If the pattern varies more significantly, or if a higher level of precision is required, it would be advisable to handle this logic in Power Query, where text manipulation capabilities are more advanced.

Best regards,

[Bibiano_Geraldo]

Hi @Seth4040 ,

In power query add a new custom colum and paste this Code:

Table.AddColumn(#"Changed Type1", "Custom", each let
  token = Text.BeforeDelimiter([YourColumnName], " "),
  parts = Text.Split(token, "-"),
  digitsOnly = List.Select(
    parts,
    each Text.Length( Text.Remove( _, {"0".."9"} ) ) = 0
  )
in
  if List.Count(digitsOnly) >= 2 then
    digitsOnly{0} & "-" & digitsOnly{1}
  else
    null)

Make sure to replace YourColumnName with real name of your column (Vendor Invoice)

the output should look like this:

[Seth4040]

I am getting an error for some reason.

[Bibiano_Geraldo]

Sorr, consider to use this one:

let
  // 1) Strip off anything after the first space (so “53551117-10 FQUG” → “53551117-10”;
  //    “AP-ORL-56403401-F-10” stays full because it has no spaces)
  token = Text.BeforeDelimiter([YourColumn], " "),
  // 2) Split on the hyphen
  parts = Text.Split(token, "-"),
  // 3) Keep only the *all-digit* parts
  digitsOnly = List.Select(
    parts,
    each Text.Length( Text.Remove( _, {"0".."9"} ) ) = 0
  )
in
  // 4) If we found at least two digit-only parts, stitch them back together with a hyphen
  if List.Count(digitsOnly) >= 2 then
    digitsOnly{0} & "-" & digitsOnly{1}
  else
    null

[Seth4040]

I used this one and works but still getting data on the Front side of the Vendor Invoice.  It will say NULL and then looked up what the deal was and all of them have data on the front side.

[Bibiano_Geraldo]

Hi @Seth4040 ,

Can you provide more data? to make tests here.

Please consider to not share sensitive data.

Thank you!

[Seth4040]

Vendor Invoice	Status	Custom
FQ 53362278-10	PAID
CHATM 53253786-10	PAID
WIL 52913084-10	PAID
53954321-10 SANFD	PAID	53954321-10
54388077-10 WIL F	PAID	54388077-10
55345830-10	PAID	55345830-10

[v-sathmakuri · Accepted solution]

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]

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]

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