Better rollup of errors in Power Query custom functions

[pelowski]

I'm looking for some best-practice advice.  I'm trying to understand how to better rollup errors within custom PQ functions I'm writing.

(When someone uses a function I write, I want it to bubble up good error messages if it's invoked with bad parameters for the data set in which it's been called.  And in general, I'm trying to better utilize the error-handling in PQ.)

Here is an example.  With a data set like this...

let
	SampleData =
		#table(
			{"Order Date", "Order Date Time", "Name", "Licenses", "Amount", "Variety of Types"},
			{
				{"1/5/2020", "1/5/20 8:42 AM", "John Doe", "2", "50.23", "1/5/2020"},
				{"1/23/2020", "1/23/20 10:21 AM", "Jane Doe", "4", "82.28", "1/23/20 10:21 AM"},
				{"2/8/2020", "2/8/20 3:12 PM", "Jefferson Doe", "1", "65.77", "Jefferson Doe"},
				{"2/27/2020", "2/27/20 4:26 PM", "Jameson Doe", "12", "102.42", "12"},
				{"3/4/2020", "3/4/20 9:28 PM", "Janet Doe", "17", "85.19", "85.19"}
			}),
	#"Changed Type" = Table.TransformColumnTypes(SampleData,{{"Order Date", type date}, {"Order Date Time", type datetime}, {"Name", type text}, {"Licenses", Int64.Type}, {"Amount", Currency.Type}, {"Variety of Types", type any}})

in
	#"Changed Type"

and a custom function like this...

// fnDateKey.pq
(table as table, NewColumnName as text, DateColumnName as any) =>
let
	ErrorTableColumnDoesNotExist = if not(Table.HasColumns(table, DateColumnName)) then error Error.Record("Column not found", "The column " & DateColumnName & " doesn't exist.") else null,
    ValueType = Expression.Evaluate("vt(TableOfData[" & DateColumnName & "]{0})", [TableOfData=table,vt=Value.Type]),
	ErrorWrongType = if not (Type.Is(ValueType, type date) or Type.Is(ValueType, type datetime)) then error Error.Record("Wrong Data Type", "The data type for '" & DateColumnName & "' is neither a date nor a datetime column.") else null,
    
    // Add a copy of the column here because otherwise it's hard to represent a dynamic column name in the each [DynamicColumnNameHere] syntax
    CreateStaticColumn = Table.DuplicateColumn(table, DateColumnName, "StaticDateColumn"),
    DateKeyField = Table.AddColumn(CreateStaticColumn, NewColumnName, each Text.Combine({Text.From(Date.Year([StaticDateColumn])), Text.PadStart(Text.From(Date.Month([StaticDateColumn])), 2, "0"), Text.PadStart(Text.From(Date.Day([StaticDateColumn])), 2, "0")}), Int64.Type),
    RemoveStaticColumn = Table.RemoveColumns(DateKeyField,{"StaticDateColumn"}),

    // Is this the best way to return the error messages from above?
    FinalStep = if ErrorTableColumnDoesNotExist <> null then ErrorTableColumnDoesNotExist else if ErrorWrongType <> null then ErrorWrongType else RemoveStaticColumn
in
	FinalStep

And then when calling them like so I test the error conditions...

fnDateKey(SampleData, "DateKey", "InvalidColumnName")
fnDateKey(SampleData, "DateKey", "Name")

I can see error messages like so in the cases where the DateColumnName either doesn't exist, or is not a Date or DateTime column.

Column not found errorColumn wrong type

Ultimately, if there is an error generated in one of the first steps, I'd like to just skip ahead to the end and exit the function, but I don't know if there is any way to do that.  And according to my interpretation of what I read in Gil Raviv's PQ book the lazy M engine should automatically skip steps that aren't used, but I'm not sure if that will happen if there is a multi-check IF statement in the final step.  It might not be able to know to skip all the in-between steps because an error was generated above.

So my questions are as follows...

1. Is there a better way to accomplish what I'm doing in the function above?
   (The actual purpose of the function isn't as important to me compared to how to roll-up the errors, but if you've got suggestions on how to improve the DateKey part as well, I won't turn them down.  😁)
2. Can I exit the function quickly (and if so how?) if certain things are not true about the parameters specified?
3. Is there a way I can improve the message the bubbles up?  I'm not sure why it reads "An error occurred in the '[nothinghere]' query".
4. In the above, I don't have any try/otherwise statements because I'm not sure how to use that syntax with the skip-ahead-to-end-if-error-experienced part.  I'm not sure if this is good or bad as I'm a little concerned that perhaps in certain cases the ErrorType & ErrorWrongType steps might fail above.

I feel like when an error is generated as part of a custom function the resulting default errors are less than ideal for sometime to figure out how or why they occurred.

Any help you can provide will be greatly appreciated.  I'm writing more and more custom functions and they're saving me a lot of time and I'm hoping a better understanding of the error-handing-rollup within them will help to make them a little more bullet-proof and easier to use.

[dax]

Hi @pelowski , 

I am not clear about your requirement, did you want to ignore the error in M code?   It seems that you need to use try in M code, I suggest that you could @Imkef or other super users , who could offer more suggestiosns to you.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi