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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors