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

[laciodrom_80]

Hi guys,

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

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!

[mwegener]

Hi @laciodrom_80,

Do you still need help?
Can you provide a sample units.json file?

Please give Kudos for support.

If I answered your question, please mark my post as solution, this will also help others.