Hi
I've googled this and nothing has helped. I have data that is 4,725 lines long in an Excel table. I used Data > Get & Transform > From Table to get it into a query. Only 49 lines load and line 50 is errors across each column. I've looked at line 50 in the data and it looks normal. When I close and load to a table it doesn't work and an error shows when I hover over the query in the query pane in Excel, which I've used as the topic name here.
I recreated each step one by one. The problem seemed to arise when I filtered by a custom column. I had added a custom column to identify which rows to keep and delete based on two criteria. I did this using this formula:
= if Text.Contains([Charger Name], "CirTch") and [Consumption] = 0 then "Delete" else "Keep"
Adding this column worked fine but when I did the next step, which was to filter it to only keep the "keep" rows that's when I got the error.
At this point I still have all the data in the table so this step hasn't led to the reduced rows with the error row.
Does anyone have any idea what is happening here?
Thanks
Mardi
Hi Mardi,
If you remove the 'Change Type' step (it's usually the second step), do the errors disappear?
Mynda
Hi Mynda
No although it changed to [Expression.Error] We cannot convert the value 32232700000000000 to type Text
I had stuffed around a lot and done a series of change types and renames:
Changed type (the one it did automatically that I have now deleted)
Renamed columns
Changed type1
Renamed columns1
Changed type2
Renamed columns2
Changed type3
Renamed columns3
Changed type4
Renamed columns4
Added custom
Filtered rows
So I set up a new query and did just one changed type and one rename column, then added the custom column at which point all was fine. The problem started, again, in the new one when I filtered the custom column to hide the ones with "Delete" in the row. Then I got the same error when closing and loading again:
[Expression.Error] We cannot convert the value null to type Logical
A box popped up with more info when I tried to close and load:
Excel couldn't refresh the table 'Data' from the connection 'Query - Data'. Here's the error message from the external data source:
The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.
More Details:
OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Logical..
An error occured while processing the table 'Data'.
The current operation was cancelled because another operation in the transaction failed.
The data is in the same spreadsheet as the query, which is in a folder in a drive I have access to.
I have removed errors from the date and time columns and changed ChargerIDs that aren't actual chargers to "unidentified" and I am not getting the errors anymore. I'm not sure this is a solution but since it works with what I am trying to do I'm happy with it for now.
Hi Mardi,
Glad you found a workaround. It sounds like the change type step is the cause of the errors in the Date and Time columns. I usually set the types in the last step if possible.
If you want to investigate further, the error you're getting is a result of setting a data type to 'Logical' in a column that contains nulls.
Mynda
Thanks for your help Mynda. What is a "logical" data type?
I've just signed up for the PowerBI course - I love this stuff! I watch people doing all this manual work in spreadsheets and it makes me want to scream but they are set in their ways and don't want to learn. My boss has just set up a spreadsheet with all these manual calcs to do monthly reporting in and I am expected to insert part of that report manually each month. I don't want to tread on his toes but I'm going to see what I can do to automate it. So thanks for all the resources on your site, and your great courses.
Logical data types are TRUE and FALSE. Maybe you have a column containing these values?
I'm sure your boss will be thrilled if you can automate the reporting. Go for it.
Mynda
