DocumentDB Parameters

[NielsDecoene]

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

[v-yuezhe-msft]

@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]

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

[v-yuezhe-msft]

@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]

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

[v-yuezhe-msft]

@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]

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

[v-yuezhe-msft · Accepted solution]

@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]

Well that worked, putting three " does the trick, who would have thought...

Any explication about why three are required?

I'll mark this as answered, thanks alot!