Power Query è una funzionalità poco nota di Excel e Power BI, ma incredibilmente utile. Tra le varie opzioni, ci consente infatti di utilizzare un endpoint di un web service come sorgente dati per un foglio di lavoro, come mostrato in figura.

La configurazione necessaria per gestire un resultset JSON come quelli tipicamente esposti da ASP.NET Web API richiede alcuni passaggi (dettagliati in questo articolo https://www.howtoexcel.org/power-query/how-to-access-a-json-api-with-power-query/), e una volta completati avremo una tabella con i dati dalla nostra Web API, di cui possiamo anche effettuare il refresh all'occorrenza.

Dato che è pensato per utilizzi in scenari enterprise, Power Query supporta ovviamente anche API protette con diverse tipologie di security, incluso ovviamente il protocollo OAuth2 implementato da Azure Active Directory. Il requisito per far sì che il tutto funzioni correttamente è quello di restituire nella risposta 401 - Unauthorized, le informazioni necessarie per recuperare un token valido. Più precisamente, dobbiamo sfruttare l'header www-authenticate che ASP.NET Core, come comportamento di default, popola solo indicazione sulla tipologia di token attesa:

www-authenticate: Bearer

Fortunatamente possiamo personalizzare questo header nelle opzioni di configurazione del middleware JwtBearer:

services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddJwtBearer(options =>
    {
        options.Audience = this.Configuration["AzureAd:Audience"];
        options.Authority = string.Format(this.Configuration["AzureAd:AadInstance"], this.Configuration["AzureAD:TenantId"]);
        options.Challenge = "Bearer realm=\"mymightysite.com\" " + 
            $"authorization_uri=\"https://login.windows.net/{this.Configuration["AzureAD:TenantId"]}/oauth2/authorize\" " +
            $"resource_id=\"{this.Configuration["AzureAd:ClientId"]}\"";
    });

Nel codice in alto, abbiamo modificato il contenuto della proprietà Challenge, specificando, tra le varie cose, l'URL di Azure Active Directory tramite cui effettuare la richiesta di autorizzazione, e il ClientID dell'applicazione che abbiamo registrato.

In questo modo, nel momento in cui proviamo a registrare la Data Source in Excel, o al primo refresh, avremo la possibilità di effettuare l'autenticazione con il nostro Tenant e di ottenere un token con cui chiamare il Web Service.