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!!
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
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.
Hi Seth4040 ,
Can you provide more data? to make tests here.
Please consider to not share sensitive data.
Thank you!
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 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!!