Forum Discussion
Unable to load Azure log analytics data completely to Power BI
Hello All,
We are tracking logs in Azure Log analytics. The same needs to be consumed in Power BI for Visualizations.
As of now we have a row count of 7 million which will only increase.
These are the steps tried:
1. Using Power Query to load all the data as tables per day. Using this approach - Exceed the 500,000 row limit in Application Insights and Log Analytics with Power BI – The White Pages.
However the data loading takes more than 8 hours and it is completely un-reliable as it loads massive amounts of data. Additionally, I have not been able to load the data itself. System crashes and Power BI or Excel crashes when loading like this.
2. Searched for connectors - Not available (Azure Monitor, Azure Log Analytics)
3. Tried to use Gen 1 Dataflows - Our tenant does not have this active.
Note: The KQL query in Log analytics has a limitation as noted in the questions from other questions in the comunity so this option does not work well and this is what option 1 was using to itreate call the KQL from Log analytics and providing tables for each, which does not solve the requirement.
Incermental refresh does not suite us as we need to keep downloading the published report and re-publishing it.
I am now looking at other ways to load data from log analytics to Power BI.. Please let me know if you know of any other.
Hi AndrewPrabhakar,
No worries at all, your follow-up questions are completely valid. Log Analytics API authentication and IAM setup can be a little confusing at first.
You can use the Log Analytics REST API outside of Power BI, such as in PowerShell or Python scripts, which is actually a better fit for your needs. While Power BI uses the same API under the hood, it's not ideal for managing very large historical datasets directly. PowerShell or Python allows for more control over incremental exports and file storage.
For this setup, you'll generally need:
- An Azure AD App Registration
- API permissions for Azure Monitor / Log Analytics
- A client secret or certificate
- IAM access like Log Analytics Reader or Monitoring Reader on the workspace
Once set up, your script can authenticate to Azure, run KQL queries for recent data, export results to CSV files, and save them to SharePoint or a folder. Power BI can then connect to the folder using the Folder connector and combine the files automatically during refresh.
Since you’re looking for a low-cost or no-cost solution, PowerShell with Windows Task Scheduler and SharePoint or folder storage is likely the best approach without needing Fabric capacity or extra services.
It's also a good idea to export smaller daily or hourly files instead of large cumulative ones to keep Power BI refreshes stable and avoid performance issues.
Thank you.
14 Replies
Hi AndrewPrabhakar,
I would recommend extracting the data from Azure Log Analytics into a Fabric Lakehouse, using a notebook would probably be the best way to do that.
Then build your report off of the lakehouse, whichwill be much more efficient.
Thanks for the immediate response tayloramy . Our tenant does not have this subcription.
I believe there is costs for this?
Is there a way to keep a query as historical in Power query without loading to Power BI and Without refresh enabled, then one query which only increments day wise adds to the historical set and that query again takes the next day pushes to the historical table.
Some thing like a dummy table but only loads piece by piece.Hi AndrewPrabhakar,
Thank you tayloramy for your response to the query.
Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help.Thank you.
We don't have fabric capacity. So still in need of help to load data as requirement mentioned.
Hi AndrewPrabhakar,
Since Fabric capacity and Lakehouse options aren’t available in your tenant, Power BI by itself will have difficulty reliably ingesting and storing over 7 million Log Analytics rows directly through Power Query or KQL APIs. The crashes and long refresh times you’re experiencing are expected, as Log Analytics is designed for operational queries, not as a large historical data store.
To answer your follow-up, Power Query cannot permanently append and store historical data locally across refreshes without an external storage solution. Each refresh re-runs the queries from the source, so Power BI cannot keep an internal growing historical table as you described.
A better approach in your situation would be to export Log Analytics data incrementally outside Power BI (using tools like Azure Automation, Logic Apps, Azure Functions, or scheduled scripts), store the daily data in a cost-effective storage option like CSV/Parquet files in Azure Blob Storage, Azure SQL DB, or SharePoint/OneDrive, and then connect Power BI to this stored dataset. This method avoids repeatedly pulling millions of rows from Log Analytics and improves stability and performance.
If you also have limited access to Azure services, you could schedule daily exports from Log Analytics to CSV files, keep them in a folder, and use Power BI’s “Folder” connector to combine them automatically. This is generally more efficient for large historical datasets than querying Log Analytics directly with each refresh.
Thank you.
Hello v-sgandrathi,
Thanks for the information on this. I was trying to investigate the use of scheduled scripts and SharePoint folder. However, I am unable to get the download as to how to proceed with this.
I agree this is maximum effort from the Power BI developer but this is to only no cost option I have.
Do you have some links and steps to follow to proceed with this approach or anything that can help immensely.
Hi AndrewPrabhakar,
I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.
Thank you.
I have updated below.
"""Hello @v-sgandrathi,
Thanks for the information on this. I was trying to investigate the use of scheduled scripts and SharePoint folder. However, I am unable to get the download as to how to proceed with this.
I agree this is maximum effort from the Power BI developer but this is to only no cost option I have.
Do you have some links and steps to follow to proceed with this approach or anything that can help immensely."""
Hi AndrewPrabhakar,
Thanks for the update and for providing those extra details.
Given your need for a low-cost or no-cost solution, the SharePoint folder with scheduled exports is likely the most effective option.
You can set this up by creating a scheduled script (using PowerShell, Python, or Azure Automation if available) to run your Log Analytics KQL query daily, export only new data, and save the results as CSV files in a SharePoint or local/network folder. Power BI can then use the Folder connector to automatically combine all historical files during each refresh.
The process would generally be:
- Run the KQL query with a date filter (for example, last 1 day).
- Export the results to CSV.
- Save the CSV with a timestamp.
- Store the files in your SharePoint or folder location.
- Connect Power BI to the folder rather than directly to Log Analytics.
This approach reduces refresh pressure on Power BI and avoids repeatedly querying millions of records directly from Log Analytics.
Below are the refernce documents :
API access and authentication - Azure Monitor | Microsoft Learn
Power Query Folder Connector - Power Query | Microsoft LearnThank you.
It seems I need to set-up few IAM roles before I can call it anywhere else apart from Power BI or can the M code url be used in Power Shell, Python scripts.
Apologies for bringing these many things up.
Hi AndrewPrabhakar,
No worries at all, your follow-up questions are completely valid. Log Analytics API authentication and IAM setup can be a little confusing at first.
You can use the Log Analytics REST API outside of Power BI, such as in PowerShell or Python scripts, which is actually a better fit for your needs. While Power BI uses the same API under the hood, it's not ideal for managing very large historical datasets directly. PowerShell or Python allows for more control over incremental exports and file storage.
For this setup, you'll generally need:
- An Azure AD App Registration
- API permissions for Azure Monitor / Log Analytics
- A client secret or certificate
- IAM access like Log Analytics Reader or Monitoring Reader on the workspace
Once set up, your script can authenticate to Azure, run KQL queries for recent data, export results to CSV files, and save them to SharePoint or a folder. Power BI can then connect to the folder using the Folder connector and combine the files automatically during refresh.
Since you’re looking for a low-cost or no-cost solution, PowerShell with Windows Task Scheduler and SharePoint or folder storage is likely the best approach without needing Fabric capacity or extra services.
It's also a good idea to export smaller daily or hourly files instead of large cumulative ones to keep Power BI refreshes stable and avoid performance issues.
Thank you.
Hi AndrewPrabhakar,
Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help.
Thank you.
