Analyze Azure Data Factory logs - part 3: Power BI

Case
In a previous blog post we showed how to query the logs of Azure Data Factory within Azure Log Analytics, but how can you show that same data in a Power BI report?

Using KQL as source for Power BI

Solution

1) Export KQL query as M query

The previous step of writing that query is the most important step. Make sure you have a query, or better a couple of queries, that represent a dimensional model instead of just a simple table. This will make creating Power BI reports so much easier and faster.

• Go to the query editor of the Log Analytics workspace
• Then open an existing query (via Query explorer) or write a new one.
• Once the query is successful, hit the Export option and choose "Export to Power BI (M query)".
• This will result in downloading a text file with an M query in it. Analyze the generated M query and optionally adjust it to your own needs

Export to Power BI (M query)

Note that you can also see your KQL query as a parameter for Rest API call and that besides the M query it also contains some explanation for Power BI.

2) Blank Query Power BI

Now the last step is fairly easy. Especially when your queries are already modeled in a dimensional model. If not you will have to do some extra steps

• Open Power BI desktop and choose Get Data
• In the Other section at the bottom you will see the Blank Query option
• Select it and click Connect. A new Power Query Editor window will open
• Now hit the Advanced Editor option in the Home ribbon.
• A third Advanced Editor window will open. This is where you copy and paste the content of the downloaded file of step 1 and click on Done.
• Give the query a name and click on Close & Apply in the Home ribbon.

Repeat this step for all queries that you created for your report and then you can start creating that report! Well, you might need to add some relations between the tables for the best result.

Add the M query to Power BI

Conclusion

In this post we showed you how to use a KQL query in Power BI. Make sure most of the easy transformations are done within KQL. This saves you a lot of time in Power BI. And as mentioned before: make your query like a fact and dimension table. This makes it so much easier to generate the report.