Wild card in power query

[Anonymous]

Hello so i have a column of products

Theres "product .01"

I want to make a m query formula that says if product contains "product" (anything in between) and ".01" to give me my result

I want to do this cause sometimes it "product 0.01" sometimes its "product   0.01" or "product  .01" so sometimes theres more space or no space in between so is there a wildcard i could use in m query between "product" and ".01" ?

[Rickmaurinus]

Power Query does not support wildcards, but there are alternative routes.

• Text.StartsWith( "MyText", "My" ) resembles SQL's WHERE x = 'My%'
• Text.EndsWith( "MyText", "xt" ) resembles SQL's WHERE x = '%xt'

For other wildcard characters, custom functions will be necessary.

I wrote an article about various wildcard approaches that work for different kinds of wildcards and others.

https://gorilla.bi/power-query/wildcards/

It can be challenging, but I am hopeful for future support of regex or wildcards.

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[v-xiaosun-msft · Accepted solution]

Hi @Anonymous ,

The wildcard filter is not directly available in Power Query. You can use Text.StartsWith() or Text.EndWith() to locate the string.

If you want to split the column, here is my solution.

Data sample:

Split column by “space”:

Split three columns by number of characters.

Final output:

You will see anything between "product" and ".01".

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Use the Power Query native functionality to split on a delimiter and remove empty entries.