Handling Web.Request Errors in PowerBI M language

I share here how I have improved my PowerBI Jira Template in respect of field selection flexibility.

Problem description

For my Jira 2 PowerBI Template, I wanted to handle the case where the imported filter does not have custom columns set. It was a hurdle for some users e.g. if you want to import all fields and do the field selection in PowerBI instead of beforehand in your Jira Filter.

Previously this line of code :

FilterColumns = Json.Document(Web.Contents(JiraRootUrl & "/rest/api/2/filter/" & Text.From(JiraFilterId) & "/columns")),

would through a 404 error if the Filter Columns were not manually set.

Solution

I have found the solution in this great resource Chris Webb's BI Blog:

https://blog.crossjoin.co.uk/2016/08/09/handling-404-not-found-errors-with-web-contents-in-power-query-and-power-bi/ 

In my updated version I handle the 404 error like this:

restUrl = JiraRootUrl & "/rest/api/2/filter/" & Text.From(JiraFilterId) & "/columns",

response = Web.Contents(restUrl,

            [ManualStatusHandling={404}]),

responseMetadata = Value.Metadata(response),

responseStatus = responseMetadata[Response.Status],

Columns = if responseStatus =404 then "" else

    let

        FilterColumns = Json.Document(response),

        #"Converted to Table" = Table.FromList(FilterColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"label", "value"}, {"label", "value"}),

        //Columns2 = Table.RemoveMatchingRows(#"Expanded Column1",{[label="Key"]},"label") // remove Key because not exported as field

        Fields = Text.Combine(#"Expanded Column1"[value],",")

    in

        Fields

In case the Filter Columns are not set, I get a responseStatus= 404 which I catch to return "" in this case.