[Update 11/29/2017: With all workspaces being converted to the new query language, the original “non-converted workspace” sections of this blog post can be disregarded. I am leaving this blog post in place for historical purposes to show how this functionality changed with the new query language.]

Microsoft recently released the new query language for Log Analytics which I personally believe to be “the most significant update to the Log Analytics framework since the release of the HTTP data collector API.” The new query language greatly extends the capabilities of Log Analytics but it also opened the door to another large change which is a bit more subtle. In a non-converted workspace, you can enable a preview feature called “PowerBI Integration” which allows you to push data to PowerBI based on a search query you define in OMS (I’ve used this functionality in several blog posts including the following):

• Using Power BI and OMS to visualize performance information
• Visualizing weather information in Power BI with data from Microsoft OMS
• Using Power BI and OMS for security dashboards and reports

The ability to push data to Power BI was extremely helpful but it had two major limitations:

• Since you were pushing data you couldn’t query directly from Power BI to gather your data as you do with other sources and there were limits as to how frequently these exports could be scheduled.
• The data which was pushed was only available in the web version of Power BI – not in the desktop version of Power BI (which has more capabilities).

Digging into the Power BI functionality:

The screenshots below show a non-converted workspace and how the PowerBI Integration features.

Once you have converted your workspace, the option to enable the “PowerBI integration” no longer exists.

When you run a query in Log Analytics you see the same option available for PowerBI. The screenshot below is from a non-converted workspace.

The screenshot below adds the “Analytics” option and shows a converted workspace.

The difference is what happens which you hit the PowerBI button. In the non-converted workspace this raised a dialog box where you defined the query.

In the converted workspace, this creates a text file which you can save off. This text file contains both the query and the process to use the query in Power BI desktop.

An example of this export is shown below:

/*

The exported Power Query Formula Language (M Language) can be used with Power Query in Exceland Power BI Desktop.

For Power BI Desktop follow the instructions below:

1) Download Power BI Desktop from https://powerbi.microsoft.com/desktop/

2) In Power BI Desktop select: ‘Get Data’ -> ‘Blank Query’->’Advanced Query Editor’

3) Paste the M Language script into the Advanced Query Editor and select ‘Done’

*/

let AnalyticsQuery =

let Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/8bfeadbe-0dcb-4b43-84cb-631271aaba13/resourceGroups/oi-default-east-us/providers/Microsoft.OperationalInsights/workspaces/a965ed09-6a65-4b9b-9676-ef5f16709ff8/api/query?api-version=2017-01-01-preview",

[Query=[#"query"="Perf | where CounterName == ""% Processor Time"" | summarize AggregatedValue = avg(CounterValue) by Computer

, bin(TimeGenerated, 1h) | sort by TimeGenerated desc | limit 2000",#"x-ms-app"="OmsAnalyticsPBI",#"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 }

}),

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

To add this data, we open Power BI desktop and choose Get Data. From the Get Data list we choose the “Blank Query” shown below.

From this view we open the Advanced Editor.

Paste the query into the Advanced Editor view and click Done.

You may need to Edit your credentials to access the data.

(In my case I need to authenticate to my Organization account)

NOTE: It is recommended to use service principal for authentication otherwise might be issues when you change your personal account password.

Once we connect we can see the data in Power BI.

Then we close and apply this query.

From here you can build your visualizations you normally would. In my example this is “Query2” shown on the right side of the screen.

The screenshot below shows a simple visualization of the data which I requested via the query.

Summary: Converted workspaces in Log Analytics replace the preview feature approach which pushes data into Power BI with the ability to query data from Power BI. This approach also makes it possible to use the desktop version of Power BI which removes the limitations we had seen when working with the data we had pushed out to Power BI. In a follow-up blog post I will review other changes that I have found to the user experience after conversion of a workspace has occurred.