topic Re: Wild card in power query in DAX Commands and Tips

Wild card in power query

Anonymous — Wed, 05 Oct 2022 14:07:00 GMT

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" ?

Re: Wild card in power query

daXtreme — Wed, 05 Oct 2022 14:29:40 GMT

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

Re: Wild card in power query

v-xiaosun-msft — Thu, 06 Oct 2022 08:57:22 GMT

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.

Re: Wild card in power query

Rickmaurinus — Wed, 27 Sep 2023 13:47:31 GMT

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.