cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

The query, data does not have a valid m expression

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: The query, data does not have a valid m expression

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!

View solution in original post

7 REPLIES 7
Highlighted
Super User II
Super User II

Re: The query, data does not have a valid m expression

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

Highlighted
Memorable Member
Memorable Member

Re: The query, data does not have a valid m expression

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

 

Highlighted
Resolver I
Resolver I

Re: The query, data does not have a valid m expression

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

sontt_0-1599875384192.png

It only work when syntax like this:

sontt_1-1599875505671.png

but this one cannot load to power BI.

sontt_2-1599875556259.png

 

Highlighted
Memorable Member
Memorable Member

Re: The query, data does not have a valid m expression

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

Highlighted
Memorable Member
Memorable Member

Re: The query, data does not have a valid m expression


@Rocco_sprmnt21 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.

Highlighted
Resolver I
Resolver I

Re: The query, data does not have a valid m expression

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!

View solution in original post

Highlighted
Community Support
Community Support

Re: The query, data does not have a valid m expression

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!

 

 

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors