The query, data does not have a valid m expression

[sontt]

Hi,

I use direct query to get data from db, in the sql query of direct query I refer list of text from another table in power query as a filter in where condition. All the things were ok in power query but when I clicked Close&Apply, it could not load to power bi. The error is "The query data does not have a valid M expression". My sql query like:

*
Query="
SELECT .............
FROM dbo.SO_Order so with (nolock)
..................................
WHERE so.CreatedDate >= DATEADD(DAY, DATEDIFF(DAY,1, GETDATE())-60, 0)
...............................................
AND sp.Name in ("&Text.Combine(sku,",")&")"

I will appreciate a guide on this

Thanks

[Jimmy801]

Hello @sontt 

Text.Combine needs a list. Your code has to look like this

Text.Combine({sku,","})

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

just a small fix to avoid another round of messages 😁

the correct power query sintax   is

Text.Combine(sku,",")

where sku is a list of strings  sku= {"str1", "str2", "str3"}

[sontt]

Hi Rocco, thanhks for your reply but when I apply your advice. It does not work.

It only work when syntax like this:

but this one cannot load to power BI.

[Anonymous]

hi @sontt ,

my observation was only about the syntax of the text.combine function. I did not follow the rest of the discussion and have no experience with the direct query context you refer to.

I'm sorry but I'm afraid I can be of help.

Only I would tell you, since, it seems to me, you are trying to construct a text that must be evaluated as an expression to pay attention to  "  within the text those that must be part of the expression.
perhaps they need to be 'escaped'. May be preceded by another " or sorrounded by bn2 " " !?

[Anonymous]

---

@Anonymous wrote:

hi @sontt ,

my observation was only about the syntax of the text.combine function. I did not follow the rest of the discussion and have no experience with the direct query context you refer to.

I'm sorry but I'm afraid I can be of help.

Only I would tell you, since, it seems to me, you are trying to construct a text that must be evaluated as an expression to pay attention to  "  within the text those that must be part of the expression.
perhaps they need to be 'escaped'. May be preceded by another " or sorrounded by bn2 " " !?

---

you should do somethink similar to this, I guess:

let
    sku={"Vorrei", "un", "cappunccino","al", "vetro,", "per","favore."},

    Source = Expression.Evaluate("Text.Combine(sku , "" "")",[Text.Combine=Text.Combine, sku=sku])
in
    Source

I suggest also you,for simplicity, change name from sku-abcd to sku_abcd, for example.

[sontt · Accepted solution]

Hi Rocco,

I found the problem, it's new feature from 06/2020(https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enhanced-dataset-metadata). I just enable this.

Thank you very much!

[v-xuding-msft]

Hi @sontt ,

Glad that you have resolved this issue! You could accept your answer as solution. Others who have the same questions will find the solution quickly. Thanks!