Unable to load data from batabase using kusto query

[AnupTandale]

I am trying to load data in power bi but unable to load 

Kusto query - 

// -- Helper functions
let GetColumnValue = (column_id: long) {
database('db1').GetLatestSnapshotView(project_column_values)
| where project_column_id == column_id
| project
id=project_item_id,
value
};

// -- Lookup values for dropdowns
let focus_lookup=datatable(key: string, value: string) [
'key1', 'Core Adoption',
'key2', 'Actions Adoption',
'key3', 'Security Adoption',
'key4', 'Custom',
'key5', 'Migration Enablement'
];

let issues_lookup=datatable(key: string, value: string) [
'keyA', '0-None',
'keyB', '1-Demo/Discussion',
'keyC', '2-PoC',
'keyD', '3-Pilot',
'keyE', '4-Advanced/Enterprise'
];

// Convert Join Keys to matching data types
let canonicalSetup = database("db2").issues
| project
issue_id_dec=todecimal(id),
issue_id=number,
issue_closed=closed_at;

// -- Root query of required project item+issue data
let projectItems = database('db1').GetLatestSnapshotView(project_items)
| where content_type == 'Issue' and project_id == 1017
| join kind=inner canonicalSetup on $left.content_id == $right.issue_id_dec
| project
id,
issue_id,
issue_closed;

//-- Get field values for each required column
let salesforceColumn = GetColumnValue(1111) | project id, salesforce_parent_id=value;
let endColumn = GetColumnValue(2222) | project id, end_date=todatetime(value);
let focusColumn=GetColumnValue(3333)
| join kind=leftouter focus_lookup on $left.value == $right.key
| project id, focus=coalesce(value1, value);
let issuesColumn=GetColumnValue(4444)
| join kind=leftouter issues_lookup on $left.value == $right.key
| project id, issues=coalesce(value1, value);

let fiscal_quarter=database("db3").calendar_date;

let commonQuery = projectItems
| join kind=leftouter salesforceColumn on id
| join kind=leftouter endColumn on id
| join kind=leftouter issuesColumn on id
| join kind=leftouter focusColumn on id
| project
event_type="FastTrack",
event_detail=focus,
customer_entity_type='Salesforce Parent Account',
customer_entity_id=salesforce_parent_id,
parent_account_id=salesforce_parent_id,
event_entity_id=issue_id,
event_entity_type='Issue',
event_date=end_date
| where isnotempty(event_date);

let FastTrack_data= commonQuery
| join kind=leftouter fiscal_quarter on $left.event_date == $right.['date']
| sort by event_date asc
| project
event_id =hash_md5(strcat(tostring(event_type), tostring(event_detail))),
event_type,
event_detail,
customer_entity_type,
customer_entity_id,
parent_account_id,
customer_quarter_id =hash_md5(strcat(tostring(fiscal_quarter_name), tostring(customer_entity_id))),
tostring(event_entity_id),
event_entity_type,
event_date;

let services_delivered_data=database("analytics").events
//| where event_type !in~ ('Excluded Event Type')
| project
event_id,
event_type,
event_detail,
customer_entity_type,
customer_entity_id,
parent_account_id,
customer_quarter_id,
event_entity_id,
event_entity_type,
event_date;

let events_all = FastTrack_data | union services_delivered_data;

let metrics_all = database("analytics").metrics;

let customer_event_metrics = events_all
| join kind=inner metrics_all on parent_account_id
| where isnotempty(parent_account_id) and measurement_date between (datetime_add('month', -12, todatetime(event_date))) and (datetime_add('month', 12, todatetime(event_date)))
| order by event_date
| project
event_id,
customer_entity_type,
customer_entity_id,
parent_account_id,
metric_id,
event_entity_type,
event_entity_id,
customer_quarter_id,
measurement_date,
event_date,
metric_value;
customer_event_metrics

When we uncomment the line "// | where event_type !in~ ('Excluded Event Type')" , the query works fine. However, if we leave it commented out, the query fails while loading.

If we add "| take 10000" , there are no issues, but without it, the query fails to load the data. We have broken down the query and verified each table, but there are no null columns

Can someone please help me in resolving the issue

[AnupTandale]

Hi @DataNinja777 ,
The total number of rows in the table is around 87 million, which is less than 1 billion. However, is it still possible that Power BI is unable to load the data because of large number of data?

[Anonymous]

Hi @AnupTandale,

Thanks for the reply from DataNinja777.

To be honest, YES, Power BI might still struggle to load 87 million rows due to hardware limitations, inefficient data models, or configuration constraints, even though the row count is below 1 billion. 

You could optimize the data model like removing unused columns, leverage the Incremental refresh to keep the recent data, or you could upgrade hardware directly. Use a machine with 32+ GB RAM and an SSD for smoother performance.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[DataNinja777]

Hi @AnupTandale ,

It looks like your query is failing due to performance or memory issues when loading data into Power BI. Since the query works fine when filtering out certain event types (where event_type !in~ ('Excluded Event Type')) or limiting the dataset (| take 10000), the root cause is likely the volume of data being processed. One way to address this is by filtering data earlier in the pipeline to reduce the overall size before Power BI attempts to load it. You can modify your query to limit events to a specific timeframe, such as the last two years:

kusto

let events_all = FastTrack_data
| union services_delivered_data
| where event_date >= datetime_add('month', -24, now());

Another option is to use batching to handle large datasets in chunks, which can improve performance when processing large tables:

kusto

| evaluate batch(5000)

 The issue may also be related to the multiple join operations in the query, which can be expensive when dealing with large datasets. To improve performance, try reducing the size of metrics_all before performing the join:

kusto

let filtered_metrics = database("analytics").metrics
| where measurement_date >= datetime_add('month', -12, now());

let customer_event_metrics = events_all
| join kind=inner filtered_metrics on parent_account_id
| project event_id, event_date, metric_value;

 Sorting large datasets can also consume significant memory, potentially causing the failure. Instead of sorting the entire dataset, try limiting the number of rows before sorting:

kusto

| take 50000
| sort by event_date asc

To debug the issue further, you can use print statements at different points in the query to identify where the failure occurs:

kusto

print count=count()

Place this before and after joins to check if certain steps significantly reduce the dataset size or cause issues. If the query works when excluding a specific event type but fails otherwise, there may be a data issue such as unexpected nulls or a particularly large subset of data that causes Power BI to time out. Reducing the data volume with filtering, batching, and optimized joins should help resolve the issue. Let me know if you need additional refinements.

Best regards,