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,
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 Learn
Thank 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.