M query support

[li_pham]

Hi,

I have problem with M query, as this part: Query=[jql= ( _ )], it causes the error because it need the " " to run the jql query like the sample: Query=[jql="project in ('Test') AND created >= 2023-01-01"].

Could you help me to put the " into the Query=[jql= ( _ )]?

[jst_jdennis · Accepted solution]

To add the quotation marks to the Query=[jql=(_)] part of your M query, you can modify the "Added Custom" step to include the quotation marks in the URL string. Here's an example:

// Now let's create a new column URL with the URL we are interested at. Note the maxResults and startAt parameters this time
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "https://xxxxx.atlassian.net/rest/api/2/search?jql=" & Uri.EscapeDataString("project in ('Test') AND created >= 2023-01-01&startAt=" & Text.From([startAt])))

In this example, we use the Uri.EscapeDataString function to encode the JQL query string, and add the quotation marks directly in the URL string. This should allow the query to run successfully.

Credit: ChatGPT | https://chat.openai.com/chat

[li_pham]

hi @jst_jdennis ,

Thank you for your suggestion. Actually, I need to break into 3 parts for the url: web.content, Relative Path and Query because PowerBI service cannot set schedule refresh if I put into 1 row of url as your solution.

But when breaking down, I get stuck with adding " into Query=[jql=(_)]

  data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents("https://xxxxx.atlassian.net/rest/api",
 [
  RelativePath="2/search",
  Query=[jql= ( _ )]
 ]
))),