Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
NielsDecoene
Helper I
Helper I

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

 

1 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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

1.PNG

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors