Forum Discussion
Unable to load Azure log analytics data completely to Power BI
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,
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.
