Forum Discussion

NielsDecoene's avatar
NielsDecoene
Icon for Helper I rankHelper 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_naa...
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

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
    Icon for Helper I rankHelper 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,

      • NielsDecoene's avatar
        NielsDecoene
        Icon for Helper I rankHelper I
        9 years ago

        I've done so accordingly, what once was a query now turned into a function. 
        http://prntscr.com/fhpcbm

        If i type a correct value in the Project parameter and invoke it i'm getting an empty table.

        http://prntscr.com/fhpcmk

        Advanced Editor Code for the advanced editor:

        let
        Source = test("Project B")
        in
        Source

         

        with test being the name of former query/currently function

         

        Thx in advance