Forum Discussion
Trim data
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!!
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,