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

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

enter image description here

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

enter image description here

  1. I go to advanced editor

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

  1. I press on "Extract Values"

enter image description here

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

enter image description here

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

enter image description here

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.

enter image description here

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.

enter image description here

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

enter image description here

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.



Solution 1:[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([]))
}
  1. You can use Power Query ADX connector and copy your query/function As Is

From Azure Data Explorer

eAzure Data Explorer (Kusto)

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

Replace Errors

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 David דודו Markovitz