exception - Power BI Test Odata Access

Question

I've got a challenge with some M code. What I'd like to do is query all Odata tables from a source and return the column headers for each table for easier crosschecking. This works except for 2 cases.

1. Expression.Error Access to the resource is forbidden.

I would like the result to be:

1. {{"Permission Fail"},{"Dummy"}} <- this would allow for the rest of the function to give back a single column header to diagnose failures

current function:

let
    headers = (TableName as text) =>
    let
        query = OData.Feed("https://{orghere}.api.crm.dynamics.com/api/data/v8.2/" & TableName & "?$top=1"),
        #"Demoted Headers" = Table.DemoteHeaders(query),
        TransposeTable = Table.Transpose(#"Demoted Headers"),
        Rename1 = Table.RenameColumns(TransposeTable,{{"Column1", "Column Name"}}),
        FinalTable = try Table.RenameColumns(Rename1,{{"Column2", "Column Data"}}) otherwise Table.AddColumn(Rename1, "Column Data", each "no data")
    in FinalTable in
    headers

Above is the function so far but it doesn't contain error handling for access forbidden. I've tried wrapping the "query" step in a try..otherwise statement to account for the access failure, but no luck.

Any suggestions or pointers are greatly appreciated!

question from:https://stackoverflow.com/questions/65601353/power-bi-test-odata-access

Answer 1

Waitting for answers