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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      NielsDecoene,

      Could you please show me the screenshot about the specific statement you use to connect to the Azure DocumentDB with static project( Project B)? Also show me the relevant code in Advanced Editor using screenshot.



      Regards,

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

        Static Query Source overview as follows:

        http://prntscr.com/fhpowk

        Static Query Advanced Editor:

        let
        Source = DocumentDB.Contents("https://selectieproject.documents.azure.com:443/", "selectieprojectDB", null, [Query = "SELECT c.id AS Leverancier_ID, c.leverancier_naam AS Leverancier FROM c WHERE c.type = ""Leverancier"" AND c.project_id = ""Project B"""]),
        #"Expanded Document" = Table.ExpandRecordColumn(Source, "Document", {"Leverancier_ID", "Leverancier"}, {"Document.Leverancier_ID", "Document.Leverancier"})
        in
        #"Expanded Document"

         

        Hope this helps