Hi All,

I have a column in an excel sheet that can take different roles in other words values would be of a certain format depending on the role. For example in some cases the format can be "nnn" where n is a numeric character whilst in other cases in can be "sss" where s is any letter of the alphabet. I have loaded the sheet into power query and would like to create separate tables for each role and hence would need to filter on a regular expression. Does such a capability exist in Power Query? If not what is the best alternative?

Thank you for your help

John

 

No, Power Query does not support regex, you have to use "M" programming language.

https://support.office.com/en-us/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819

If you could post a sample of your data, I'd be able to have a better idea of what you are trying to do.  I assume that you are trying to identify if it is a number and number values go in one table or if it is text and text values go in another table?

• Proposed as answer by Michael AmadiModerator 20 hours 31 minutes ago

No, Power Query does not support regex, you have to use "M" programming language.

https://support.office.com/en-us/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819

If you could post a sample of your data, I'd be able to have a better idea of what you are trying to do.  I assume that you are trying to identify if it is a number and number values go in one table or if it is text and text values go in another table?

• Proposed as answer by Michael AmadiModerator Sunday, July 26, 2015 10:36 AM

yes, one possibility in M would be: =

Table.AddColumn(#"previous step", "NumbersOnly", each if Value.Is([Column], type number) = true then [Column] else null)

If you really want them into their own tables in the data model, you would create multiple Power Query queries and each one would filter out the right row values for that column and you might remove all other columns? Not exactly sure what you are attempting to do.

Hi Imke, this is checking the overall column value which does answer the example I provided above. Regular expressions is about matching/testing each character, so for example I could have a code '1234-xyz' and HA1-7GZ appearing in the same column in an excel worksheet and I needed to separate these out into different groups I would need to test
in the 1st example: [0-9][0-9][0-9][0-9][-][a-z][a-z][a-z] whilst in the 2nd example [A-Z][A-Z][0-9][-][0-9][A-Z][A-Z]. Such a feature would be really useful. Is it possible in M Query to loop through each character? Thank you John 

M doesn't have a typical for loop or while loop as part of its specification:

http://go.microsoft.com/fwlink/?linkid=320633

It does have an "each" keyword that you might be able to use. Otherwise, seems like you would have to use the Text.Length functions and Text.At functions along with Number.IsNaN and one heck of a big nested series of if statements.

Are your codes the actual values that you want to group by, because you can do "Group By" in your Power Query or is there some other grouping that you want to do like 1234-xyz belongs in "group1" and HA1-7GZ belongs in "group2"? If the latter, what is the logic behind the groupings?

Hi John & Seth,

although there is no dedicated function in M for a loop, you can create this behaviour at least as far as me programming rookie understands it, i.e.: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/

So it should be possible to do the checks letter by letter. Use Text.ToList to separate the letters and Text.Combine to reassemble them.

Hi John,

you should vote for the feature request: https://social.technet.microsoft.com/Forums/en-US/c83672a9-c208-4b29-872e-4032fdec67f3/regular-expressions-in-power-query?forum=powerquery