Unable to load Azure log analytics data completely to Power BI

[AndrewPrabhakar]

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 Pag....
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.

[v-sgandrathi]

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:

1. Run the KQL query with a date filter (for example, last 1 day).
2. Export the results to CSV.
3. Save the CSV with a timestamp.
4. Store the files in your SharePoint or folder location.
5. 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.

[v-sgandrathi]

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.

[AndrewPrabhakar]

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."""

[v-sgandrathi]

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.

[AndrewPrabhakar]

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. 

[v-sgandrathi]

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.

[AndrewPrabhakar]

We don't have fabric capacity. So still in need of help to load data as requirement mentioned.

[tayloramy]

Hi @AndrewPrabhakar, 

Yes, Lakehouses require a Fabric capacity. 

[AndrewPrabhakar]

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.

[tayloramy]

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.  

[AndrewPrabhakar]

Thanks for the immediate response @tayloramy . Our tenant does not have this subcription.

I believe there is costs for this?