cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Handle REST api request in M language to display both data and errors in report

Hi guys,

 

I'm afraid it isn't possible, but hope springs eternal Smiley Very Happy

 

I'm succesfully getting json data by an http request and I also handle 404 status error code:

 

let

    GetJson = Web.Contents("http://localhost:8282/prova/units.json",
        [
            Headers = [CacheControl="no-cache", ContentType="application/json"],
            ManualStatusHandling={404}
        ]
    ),
    GetMetadata = Value.Metadata(GetJson),
    GetResponseStatus = GetMetadata[Response.Status],
    
    Output = if GetResponseStatus = 200 
                then let 
                        FormatAsJson = Json.Document(GetJson),
                        #"Converted to Table" = Table.FromList(FormatAsJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                        #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "type", "subTypeId", "subTypeName", "subTypeDescription", "subTypeCategory", "pdsrId", "pdsrName", "zone", "operatorId", "operatorEtsoCode", "operatorBusinessName", "marketEnablings"}, {"id", "name", "type", "subTypeId", "subTypeName", "subTypeDescription", "subTypeCategory", "pdsrId", "pdsrName", "zone", "operatorId", "operatorEtsoCode", "operatorBusinessName", "marketEnablings"})                                                
                    in 
                        FormatAsJson
                else let 
                        errorCode = GetResponseStatus     
                    in 
                        errorCode,
    #"Converted to Table" = Table.FromList(Output, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "type", "subTypeId", "subTypeName", "subTypeDescription", "subTypeCategory", "pdsrId", "pdsrName", "zone", "operatorId", "operatorEtsoCode", "operatorBusinessName", "marketEnablings"}, {"Column1.id", "Column1.name", "Column1.type", "Column1.subTypeId", "Column1.subTypeName", "Column1.subTypeDescription", "Column1.subTypeCategory", "Column1.pdsrId", "Column1.pdsrName", "Column1.zone", "Column1.operatorId", "Column1.operatorEtsoCode", "Column1.operatorBusinessName", "Column1.marketEnablings"})


in 
    #"Expanded {0}"

I would like the report based on this query shows the data getted by the query when it ends well, but I would also like to see the status error code if it fails.

The problem is that when I run the query for the first time without errors, power bi creats its own model for the data returned in the case of no errors, so if I try to cause an error (for example changing the name of the units.json file on the web server so to produce a 404 error) and then I update the report dataset....power bi shows the pop-up "It's impossible to convert 404 value into type List"

 

Could it be possible write a query which allows me to display into the report data if it ends well and error code if it ends bad? 

 

Many thanks in advance for any clue!

Luca