Forum Discussion

NielsDecoene's avatar
NielsDecoene
Helper I
9 years ago
Solved

DocumentDB Parameters

Hey,

 

I'm currently having a small problem with a SQL query i'm writing on an Azure DocumentDB in Power BI

So i have this simple query:

[Query = "SELECT c.id AS Leverancier_ID, c.leverancier_naam AS Leverancier FROM c WHERE c.type = ""Leverancier"" AND c.project_id = Project"]

 

now the part c.project_id refers to a Power BI parameter called Project however when attempting to execute the query as is, DataFormat.Error is returned.

 

Paramater is marked as text

If i type static 

[Query = "SELECT c.id AS Leverancier_ID, c.leverancier_naam AS Leverancier FROM c WHERE c.type = ""Leverancier"" AND c.project_id = ""Project B"""]

this does return results but is not the intended behaviour.

 

Kind regards

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    NielsDecoene,

    How about changing query to the following?

    DocumentDB.Contents(#####, [Query = "SELECT c.id AS Leverancier_ID, c.leverancier_naam AS Leverancier FROM c WHERE c.type = ""Leverancier"" AND c.project_id = """& Project & """"])

    Regards,

    Lydia

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    NielsDecoene,


    Use the following query instead.

     

    [Query = "SELECT c.id AS Leverancier_ID, c.leverancier_naam AS Leverancier FROM c WHERE c.type = ""Leverancier"" AND c.project_id = ""& Project & """]

     


    Also please mark sure that you have add code like below to define Project as text.

     

    let   parameterSource = (Project as text)=>

     


    Regards,
    Lydia Zhang

    • NielsDecoene's avatar
      NielsDecoene
      Helper I
      9 years ago

      So in my Advanced Editor i have the following:

      let
      parameterSource = (Project as text),
      Source = DocumentDB.Contents(#####, [Query = "SELECT c.id AS Leverancier_ID, c.leverancier_naam AS Leverancier FROM c WHERE c.type = ""Leverancier"" AND c.project_id = ""& Project & """])
      in
      Source

       

      Now i'm still getting an empty Table, i don't know if i did something wrong.

      The parameter does get passed down as shown below

      http://prntscr.com/fhcht3

       

      Thank you in advance

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        NielsDecoene,

        Use the following code instead.

        let 
    parameterSource = (Project as text) => 
let   
    DocumentDB.Contents(#####, [Query = "SELECT c.id AS Leverancier_ID, c.leverancier_naam AS Leverancier FROM c WHERE c.type = ""Leverancier"" AND c.project_id = ""& Project & """])
in
    Source
in
    parameterSource


        Regards,