Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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,
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.
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
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,
Static Query Source overview as follows:
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.