Pass startdate and enddate parameters to the Power BI (M language) query

[usomaraju]

Hi, 

I need a quick help on how can we pass parameters to the query that i downloaded from the Azure appinsights and get the source by copying that query to the Blank query in Power BI.There i need to have the select option for to get the data between two dates.

here is my query

let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.applicationinsights.ioxxxxxxxxxxxxxxx",
[Query=[#"query"="requests
| where timestamp > datetime(2020-03-01T00:00:00Z) and (resultCode == ""500"" or resultCode == ""200"" )
| sort by timestamp asc nulls last
| project timestamp, resultCode, operation_Name, url, success
",#"x-ms-app"="AAPBI",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string", Text.Type },
{ "int", Int32.Type },
{ "long", Int64.Type },
{ "real", Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool", Logical.Type },
{ "guid", Text.Type },
{ "dynamic", Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuery

[Greg_Deckler]

You can define query parameters StartDate and EndDate for example. Then just use the text "StartDate" and "EndDate" without the double quotes in your query.

[usomaraju]

Thank you Greg for quick response

i have tried like below, but i get the below error

An error occurred in the ‘Query3’ query. Expression.Error: 4 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]

Added the highlighted lines to my query

(startdate as date, enddate as date) =>
let Source = Json.Document(Web.Contents("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
startdate = Date.ToText(startdate, "MM")&"/"&Date.ToText(startdate, "dd")&"/"&Date.ToText(startdate, "yyyy"),
enddate = Date.ToText(enddate, "MM")&"/"&Date.ToText(enddate, "dd")&"/"&Date.ToText(enddate, "yyyy"),
[Query=[#"query"="requests
| where timestamp > startofday('" & startdate & "') and timestamp < endofday('" & enddate & "') and (resultCode == ""500"" or resultCode == ""200"" )
| sort by timestamp asc
| project timestamp, resultCode, operation_Name, url, success ",#"x-ms-app"="AAPBI",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string", Text.Type },
{ "int", Int32.Type },
{ "long", Int64.Type },
{ "real", Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool", Logical.Type },
{ "guid", Text.Type },
{ "dynamic", Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table

[artemus]

Why you are getting an error

You need to move startdate and endate outside of the function call. Either just before Datatable or between your let and Source (yes you can forward reference variables)

However, that is probably not what you want to do, or what Greg was implying.

When you say "pass parameters to the query" What do you mean. From another data source, from the user manually typing it in. As a relative date/time to the current time?

[usomaraju]

Hi,

When i moved my variables before to the datatable, now i get the error says

An error occurred in the ‘Query3’ query. DataSource.Error: Web.Contents failed to get contents from 'https://api.applicationinsights.xxxxxxxxxxxxxxxxxxquery?query=requests%0D%0A%09%09%7C%20%20where%20t...' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.applicationinsights.io/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/query
Url=https://api.applicationinsights.io/v1/appsxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/query?qu...

Can anyone help us how to solve the issue.

and when i say pass parameters means, from the Azure appinsights we had like last one year of data and when we need tthe data source, user needs enter the value through a date parameter.

How 

How would I build a parameter where users can pass such values through the query. 

Please let me know. 

[artemus]

I haven't really used appinsights before.

Maybe you can make your life easier if you use Azure Data explorer connector. Not sure if this works though:

1.  Create a new connection.

2. find Azure Data Explorer (Kusto) connector

3. Put in your we address (and optionally database). Leave the query box blank.

4. Choose Direct Query

5. Don't filter the data just close and load. It won't download all the data.

6. Add filtering in the visual editor, and it should make queries for you as you apply filters.

6a. If you run into trouble with a visual loading, change the aggregation of the fields to don't summerize.