Forum Discussion
RAISERROR or THROW in Power Query Store Procedure for Custom Errors
If you want a complete refresh failure to occur if some condition isn't true, then you could add a step after the first step to the query like:
= if Table.RowCount(Source) = 0 then error "No rows in table!" else Source
Note if you add a column and put similar logic there, you will instead get a warning that some of the rows contain errors when you try and do a refresh.
Sorry, the example I posted was overly simplified. In actuality, I will query probably 3-4 tables, make sure a value isn't missing when it should be using a combination of 3-4 variables in the stored procedure. I didn't want to post anything overly complex like that because all I'm looking for is a mechanism to display that error to the Excel user from the SQL Database.
Like I said, it's a bit too complex to just implement on the Excel side. Thanks for your idea though!
Oh I think I get what your saying... you want to handle an error from SQL and give it to the user.
In that case it would be something like:
let
Result = (try <Your SQL sproc call>),
HasError = Result[HasError],
ErrorMessage = Result[Error][Message],
ErrorDetails = Result[Error][Detail],
ValueNoError = Result[Value]
in
if HasError then ErrorMessage else ValueNoError
You can also do more in depth info checking with ErrorDetails.
Sorry, was away for a bit. Trying this now, will update on results!
Ok, I have tried the following, but I don't seem to get anything for the error.
let
Project_ID=Excel.CurrentWorkbook(){[Name="Param"]}[Content]{0}[#"Project_ID"],
Source = (try Sql.Database("myserver", "mydb", [Query="exec DBO.SP_MY_SP '" & Number.ToText(Project_ID) & "'"])),
HasError = Source[HasError],
ErrorMessage = Source[Error][Message],
ErrorDetails = Source[Error][Detail],
ValueNoError = Source[Value]
in
if HasError then ErrorMessage else ValueNoErrorCan I do the error on Source?
There might be ssome delay loading happening. Try using:
Source = (try Sql.Database("myserver", "mydb", [Query="exec DBO.SP_MY_SP '" & Number.ToText(Project_ID) & "'"])[Value]),