Trim data

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

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:

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,

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

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!!

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!!

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!!