Forum Discussion
RAISERROR or THROW in Power Query Store Procedure for Custom Errors
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.
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 ValueNoError
Can 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]),
When I did this it changed my m to the following
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) & "'"])[VALUE]),
Value = Source[Value]
in
ValueThen it gives me a message saying "The column 'Value' of the table wasn't found.
This seems much harder than it should be, I wonder why RAISERROR or THROW don't just natively show an error like I would hope!
Use [Value] not [VALUE}