locked
Export to Power BI Query (M) and Azure Application Insights REST API RRS feed

  • Question

  • Hi,

    I have a query which I exported as Power BI Query (M), see below. Using the Azure Application Insights REST API I execute the same query and save the results for historical analysis in Azure Blob Storage.

    It would be helpful if the M Query to analyse the saved results in Power BI was also generated for the Blob Storage scenario. For example generate an M Query which can read saved results from Azure Blob Storage or a string.

    Power BI Query (M) for API (example)

    let AnalyticsQuery =
    let Source = Json.Document(Web.Contents("xxxxxxxxxxxxxxxxxxxxxxxxxxxx", 
    [Query=[#"csl"="traces       
        | where timestamp > ago(24h)
        | where customDimensions.['Payload methodName'] ==  'GetNextSomething'  
        | project
            className = tostring(customDimensions.['Payload className']),
            methodName = tostring(customDimensions.['Payload methodName']), 
            executionTicksMS = tolong(customDimensions.['Payload methodExecutionTimeMS']), 
            executionTime = timestamp
        | summarize 
            Average_timeMS = avg(executionTicksMS),
            Called_times = count(executionTicksMS)
    ",#"x-ms-app"="AAPBI"],Timeout=#duration(0,0,4,0)])),
    TypeMap = #table(
    { "DataType", "Type" }, 
    { 
    { "Double",   Double.Type },
    { "Int64",    Int64.Type },
    { "Int32",    Int32.Type },
    { "Int16",    Int16.Type },
    { "UInt64",   Number.Type },
    { "UInt32",   Number.Type },
    { "UInt16",   Number.Type },
    { "Byte",     Byte.Type },
    { "Single",   Single.Type },
    { "Decimal",  Decimal.Type },
    { "TimeSpan", Duration.Type },
    { "DateTime", DateTimeZone.Type },
    { "String",   Text.Type },
    { "Boolean",  Logical.Type },
    { "SByte",    Logical.Type }
    }),
    DataTable = Source[Tables]{0},
    Columns = Table.FromRecords(DataTable[Columns]),
    ColumnsWithType = Table.Join(Columns, {"DataType"}, TypeMap , {"DataType"}),
    Rows = Table.FromRows(DataTable[Rows], Columns[ColumnName]), 
    Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0 }, c{3}}))
    in
    Table
    in AnalyticsQuery


    Power BI Query (M) for Azure Blob Storage (beginnings of an example):

    let AnalyticsQuery = let Source = AzureStorage.Blobs("test"), #"perf-results" = Source{[Name="perf"]}[Data],
    ...
    ...


    Actual results returned from Azure Application Insights REST API:
    {
       "Tables":[
          {
             "TableName":"Table_0",
             "Columns":[
                {
                   "ColumnName":"Average_timeMS",
                   "DataType":"Double",
                   "ColumnType":"real"
                },
                {
                   "ColumnName":"Called_times",
                   "DataType":"Int64",
                   "ColumnType":"long"
                }
             ],
             "Rows":[
                [
                   10.238636363636363,
                   264
                ]
             ]
          },
          {
             "TableName":"Table_1",
             "Columns":[
                {
                   "ColumnName":"Value",
                   "DataType":"String",
                   "ColumnType":"string"
                }
             ],
             "Rows":[
                [
                   "{\"Visualization\":\"table\",\"Title\":\"\",\"Accumulate\":false,\"IsQuerySorted\":false,\"Kind\":\"\",\"Annotation\":\"\",\"By\":null}"
                ]
             ]
          },
          {
             "TableName":"Table_2",
             "Columns":[
                {
                   "ColumnName":"Timestamp",
                   "DataType":"DateTime",
                   "ColumnType":"datetime"
                },
                {
                   "ColumnName":"Severity",
                   "DataType":"Int32",
                   "ColumnType":"int"
                },
                {
                   "ColumnName":"SeverityName",
                   "DataType":"String",
                   "ColumnType":"string"
                },
                {
                   "ColumnName":"StatusCode",
                   "DataType":"Int32",
                   "ColumnType":"int"
                },
                {
                   "ColumnName":"StatusDescription",
                   "DataType":"String",
                   "ColumnType":"string"
                },
                {
                   "ColumnName":"Count",
                   "DataType":"Int32",
                   "ColumnType":"int"
                },
                {
                   "ColumnName":"RequestId",
                   "DataType":"Guid",
                   "ColumnType":"guid"
                },
                {
                   "ColumnName":"ActivityId",
                   "DataType":"Guid",
                   "ColumnType":"guid"
                },
                {
                   "ColumnName":"SubActivityId",
                   "DataType":"Guid",
                   "ColumnType":"guid"
                },
                {
                   "ColumnName":"ClientActivityId",
                   "DataType":"String",
                   "ColumnType":"string"
                }
             ],
             "Rows":[
                [
                   "2017-04-10T05:47:58.8303677Z",
                   4,
                   "Info",
                   0,
                   "Query completed successfully",
                   1,
                   "781c4146-c215-494b-8ef5-bb57eaaa38e4",
                   "781c4146-c215-494b-8ef5-bb57eaaa38e4",
                   "01b7cddc-74a1-4a81-a0ca-2da1b62f5a6e",
                   "0bd47d97-4c29-4cf2-8140-115a8ffbce8a"
                ],
                [
                   "2017-04-10T05:47:58.8303677Z",
                   6,
                   "Stats",
                   0,
                   "{\"ExecutionTime\":0.0625078,\"resource_usage\":{\"cache\":{\"memory\":{\"hits\":4931,\"misses\":230,\"total\":5161},\"disk\":{\"hits\":179,\"misses\":0,\"total\":179}},\"cpu\":{\"user\":\"00:00:00.2031250\",\"kernel\":\"00:00:00.0468750\",\"total cpu\":\"00:00:00.2500000\"},\"memory\":{\"peak_per_node\":385878176}}}",
                   1,
                   "781c4146-c215-494b-8ef5-bb57eaaa38e4",
                   "781c4146-c215-494b-8ef5-bb57eaaa38e4",
                   "01b7cddc-74a1-4a81-a0ca-2da1b62f5a6e",
                   "0bd47d97-4c29-4cf2-8140-115a8ffbce8a"
                ]
             ]
          },
          {
             "TableName":"Table_3",
             "Columns":[
                {
                   "ColumnName":"Ordinal",
                   "DataType":"Int64",
                   "ColumnType":"long"
                },
                {
                   "ColumnName":"Kind",
                   "DataType":"String",
                   "ColumnType":"string"
                },
                {
                   "ColumnName":"Name",
                   "DataType":"String",
                   "ColumnType":"string"
                },
                {
                   "ColumnName":"Id",
                   "DataType":"String",
                   "ColumnType":"string"
                }
             ],
             "Rows":[
                [
                   0,
                   "QueryResult",
                   "PrimaryResult",
                   "f93f12eb-fc2d-4412-a8ac-98afa197a20b"
                ],
                [
                   1,
                   "QueryResult",
                   "@ExtendedProperties",
                   "dbe3af4e-2fe2-45ab-adcf-97b167d0b505"
                ],
                [
                   2,
                   "QueryStatus",
                   "QueryStatus",
                   "00000000-0000-0000-0000-000000000000"
                ]
             ]
          }
       ]
    }
    PS. Any help with constructing the Power BI Query (M) for Azure Blob Storage appreciated.



    Tuesday, April 11, 2017 8:32 AM