Hi Chris,

It's tricky as I also have 365 and can't test it, but can you use Splitter.SplitTextByEachDelimiter and set BLK as the delimiter? Then you can split again by the hyphen delimiter.

Mynda

Hi Mynda,

Thanks for your reply and this is one workaround, however I got the following pattern and others

ABC_BLK-234ROOF-ARCH-1

BLK234 is what I want

Hi Mynda,

Attached sample of dataset

first tab was using O365 non-digit to digit which was quite ideal but unfortunate all my user don't have O365

2nd tab I use Text.PositionOf to extract out "Blk" or "Block",  and currently I only need the number after Blk or block,  is there a way to identify the position of alphabet  or special character ( -, _,  : etc),  then text.beforeDelimiter of these alphabet or special character

Thank you !

Hi Chris

See if this formula solution helps.

Sunny

Hi Sunny,

Thanks for your solution,  and using native excel will take very long to process the formulae,  and also the original raw pattern may have space in between the blk and the number that I want,  sometimes "-", "_" etc

I hope to achieve similar to non-digit to digit splitting using non-O365 PQ coding

Thank you very much !

Hi Chris,

What happens if you use the function returned by split by non digit in your earlier version of Excel? e.g. in Query Table1 (2) add a custom column with this formula:

= Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"})

Mynda

Hi Mynda,

in my Excel 2019 desktop version

Expression.Error: The name "Splitter.SplitTextByCharacterTransition" wasn't recognized.  Make sure it's spelled correctly

Hi Chris,

I've written a function in Power Query that takes your Custom Column and then extracts everything up to the first number to character transition.  So in your Custom Column you have Blk123BBZXT and my function gives you Blk123.

Here's the function code - check the attached file for your solution.

let

ExtractChars = (values as list) as list =>

let

Extract = List.Generate
(

()=> [Char = values{0}, counter = 1, nullval = 0],

each [counter] < List.Count(values),

each [ Char = if nullval < 2 then values{[counter]} else null,

counter = [counter] + 1,

nullval = if [nullval] = 0 then if List.Contains({"0".."9"}, values{[counter]}) then 1 else 0 else

if [nullval] = 1 then if not List.Contains({"0".."9"}, values{[counter]}) then 2 else 1 else 2
],

each [Char]
)

in
Extract

in
ExtractChars

Regards

Phil

Hi Philip

Really appreciate your effort in building a custom PQ function, I think space, "-", "_" scenario you script didn't take into consideration, apologise for not covering all scenario of my actual datasets

blk 3

test Blk 124

blk-23

blk-1

Probably you can explain to me the place to amend,  am not sure I can do it myself if I encounter more scenerio that not cover by the scripts

Mynda ：-   Do you have any online course on how to build custom function (PQ) like you guys uses,  really is very useful for unstructured data if we do not want to use Advanced analytics tool to transform those datasets

Hi Chris,

Yes, always best to include all scenarios 🙂

I just needed to make an adjustment to the loop counter in the function to this

each [counter] <= List.Count(values),

otherwise it works as is - see attached file.

Functions in PQ are just 'regular' queries that have been formatted slightly differently to take input parameters. I wrote a blog about this

Power Query Custom Functions • My Online Training Hub

The key to creating a function like the one I wrote here for you is knowing the in-built PQ functions and a good understanding of the M language

Power Query M function reference - PowerQuery M | Microsoft Docs

Regards

Phil

Hi Philip,

Thanks for all your help

Can you briefing explain the codes and I will try to digest and understand it,   so far didn't code (don't know how to also) such a complicated PQ code,  for normal bringing in number to square it or bringing in text for Text.Beforelimiter etc I can do it,  not for such cumulative count to check whether it is numeric or character,  very curious that how this codes know I want "Blk",  "block"

pardon me Philip

Thank you !