In Log Analytics we can quickly export to Power BI just by running an Export to Power BI (M Query). However, if you are like me, you may not do a good job of keeping track of all of the queries that you write – especially if they are going to be used somewhere else like Power BI. This blog post will show how you can take the queries you built into Power BI and bring them back into Kusto for editing.
How to get data out of Log Analytics into Power BI?
Log Analytics makes it easy to query Log Analytics as we just paste these into the advanced editor in Power BI and voila – you have Log Analytics data in Power BI.
If you are like me, you have used this functionality a lot and over time you may have run into this. What happens when you want to make a change to the Kusto query you built the M Query from? Here’s the quick trick I use for this.
How to get your Kusto query back out of the MQuery:
In Power BI open the query in the Advanced Editor, and highlight the query section (highlighted in the graphic below) and copy the text into a blank Word document.
In my case this is the text of the query I am working on (the actual query doesn’t matter here, this is just a query I put together for some custom data we are gathering on Windows Operating System versions):
| where TimeGenerated > (ago(120days)) and Report_s == “”OSVersionsAll””
| project TimeGenerated, Value_s, ClientId_d, Count_d
| sort by TimeGenerated asc
Do a replace for “” with ” (hint – Control-H brings up replace)
Copy and paste the new version into your Log Analytics workspace query and you are now good to go!
Summary: If you need to get the original Kusto query back from an M Query pull up the advanced editor for the data source in Power BI, copy out the section with the Kusto query, replace the double “” with a single ” and then just re-paste the query into Log Analytics.