Excel removes my query connection on it's own and gives me several error messages

Question

I know that this is a really long post but I'm not sure of what part of my process is making my file crash, so I tried to detail everything about what I did to get to the error messages.

So, first of all, I created a query on Kusto, which looks something similar to this but in reality is 160 lines of code, this is just a summarized version of what my code might do just to show my working process.

• First, what I do in Session_Id_List is create a list of all distinct Session Id's from the past day.
• Then on treatment_alarms1 I count the amount of alarms for each type of alarm that was active during each session.
• Then, on treatment_alarms2 I create a list which might look something like this

1x Alarm_Type_Number1

30x Alarm_Type_Number2

7x Alarm_Type_Number3

and like that for each treatment, so I have a list of all alarms that were active for that treatment.

Lastly, I create a left outer join with Session_Id_List and treatment_alarms2. This means that I will get shown all of the treatment ID's, even the ones that did not have any active alarms.

let _StartTime = ago(1d);
let _EndTime = ago(0d);
let Session_Id_List = Database1
| where StartTime >= _StartTime and StartTime <= _EndTime
| summarize by SessionId, SerialNumber, StartTime
| distinct SessionId, StartTime, SerialNumber;
let treatment_alarms1 = Database1
| where StartTime >= _StartTime and StartTime <= _EndTime and TranslatedData_Status == "ALARM_ACTIVE" 
| summarize number_alarms = count() by TranslatedData_Value, SessionId 
| project final_Value = strcat(number_alarms, "x ", TranslatedData_Value), SessionId;
let treatment_alarms2 = Database1
| where StartTime >= _StartTime and StartTime <= _EndTime and TranslatedData_Status == "ALARM_ACTIVE" 
| join kind=inner treatment_alarms1 on SessionId
| summarize list_of_alarms_all = make_set(final_Value) by SessionId
| project SessionId,  list_of_alarms_all;
let final_join = Session_Id_List
| join kind=leftouter treatment_alarms2 on SessionId;
final_join
| project SessionId, list_of_alarms_all

Then I put this query into Excel, by using the following method

1. I go to Tools -> Query to Power BI on Kusto Explorer

2. I go to Data -> Get Data -> From Other Sources -> Blank Query

3. I go to advanced editor

4. I copy and paste my query and press "Done" at the bottom

5. If you see now, the preview of my data will show "List" on the list_of_alarms_all column, rather than showing me the actual values of the list.

6. To fix this issue I first press the arrows on the header of the column

7. I press on "Extract Values"

8. I select Custom -> Concatenate using special characters -> Line Feed -> Press OK

9. That works fine for all of the ID's that do have alarms on them, it shows them as a list and tells me how many there are, the issue is with the ID's that did not have any treatments where I get "Error" on the Excel preview. Once I press "Close & Load" the data is put on the worksheet and it looks fine, the "Error" are all gone and instead I get empty cells where the "Error" would be at.

The problem now starts when I close the file and try to open it again.

First I get this message. So I press yes to try and enter the file.

Then I get this other message. The problem with this message is that it says that I have the file open when that is not true. I even tried to restart my laptop and open the file again and I would still get the message when in reality I don't have that file open.

Then I get this message, telling me that the connection to the query was removed.

So my problem here is that 1) I can't edit the file anymore unless I make a copy because I keep getting the message saying that I already have the file opened and it is locked for editing and 2) I would like to refresh this query with VBA maybe once a week from now on but I can't because when I save the file the connection to the query is deleted by excel itself.

I'm not sure of why this is happening, I'm guessing it's because of the "Error" I get on the empty cells when I try to extract the values from the lists. If anybody has any information on how I can fix this so I don't get these error messages please let me know.

Answer 1

I was not able to reproduce your issue, however there are some things you might want to try.

1. Within ADX, you could wrap you query with a function, so you won't have to copy a large piece of code into your Excel.
2. You could deal with null values (this is what gives you the Error values) already in your query. Note the use of coalesce.

// I used datatable to mimic your query results

.create-or-alter function SessionAlarms()
{
    datatable (SessionId:int,list_of_alarms_all:dynamic)
    [
         1, dynamic([10,20,30])
        ,2, dynamic([])
        ,3, dynamic(null)
    ]
    | extend list_of_alarms_all = coalesce(list_of_alarms_all, dynamic([]))
}

3. You can use Power Query ADX connector and copy your query/function As Is

4. If you haven't dealt with null values in you KQL you can take care of the error in Excel by using Replace Errors