Log In
Register
Home
August 21, 2019
Offline
Hi I know this can be easily done in Office 365 using Split Column then select non-digit to digit, how about user who do not have O365
e.g. 123_BLK234-4-A-1
I had 180K of records with Blk, block follow by number inside a string, want to extract to Blk 123, Block 56, Blk 142
current I am using Text.PostOf([Paht], "blk") to get the position then Text.Middle, then delete those I don't want using List.cumulative (manually eye ball put in a list then Text.BeforeDelimiter
it tooks quite lot time for the query to run
is there a better way using other M-code (wildcard for non-digit, digit)
Thank you !!!
See Answer
July 16, 2010
Offline
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
August 21, 2019
Offline
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
August 21, 2019
Offline
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 !
VIP
Trusted Members
June 25, 2016
Offline
Hi Chris
See if this formula solution helps.
Sunny
August 21, 2019
Offline
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 !
July 16, 2010
Offline
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
August 21, 2019
Offline
Hi Mynda,
in my Excel 2019 desktop version
Expression.Error: The name "Splitter.SplitTextByCharacterTransition" wasn't recognized. Make sure it's spelled correctly
October 5, 2010
Offline
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
August 21, 2019
Offline
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
October 5, 2010
Offline
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
Answers Post
August 21, 2019
Offline
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 !
