Forum Discussion

sontt's avatar
sontt
Resolver I
5 years ago
Solved

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

Need Help

7 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        sontt
        Resolver I
        5 years ago

        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.