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 ,
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:
I am getting an error for some reason.
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 nullI 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!