Search code examples
excelpowerbipowerquerydata-cleaningm

M Query to find and extract strings of numbers with a specific digit count from a random position in a string

I've found answers to similar questions and tried to adapt them to my circumstance, but nothing has worked for me. I've tried to "translate" my existing and working Excel formula, but couldn't manage and copilot has been worse than useless.

In PowerBI, I need to add a column that finds a 13 digit number in another column and extracts that. The number can be anywhere in the string and is not reliably delimited by any characters. It can also be one of several numbers, but the only 13 digit one.

The closest I've come so far (that is, the only thing that has worked somewhat) is:

= if Text.Length(Text.Select(Text.From([campaign_name]),{"0".."9"}))=13 then Text.Select(Text.From([campaign_name]),{"0".."9"}) else null

But unfortunately, this will only work if there are no additional numbers in the string beyond the 13 digit one.

In a second scenario I also need to find any 15+ digit numbers and then only use the left 15 digits of that, but first things first.

Can someone help me out?

Sample Data:

enter image description here

Solution

  • enter image description here

    let 
a = Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"})([Column]),
b = List.Transform(a, each Text.Start(_,13)),
c = List.Transform(b, (x)=> if Text.Length(x) = 13 and Value.Is(Value.FromText(x), type number) then x else null),
d = List.RemoveNulls(c){0}?
in d

    enter image description here