Skip to content
Installer Visual Studio CodeInstaller l’extension Power Query SDKCréer un nouveau projet d’extension (template “Data Connector”)Remplacer le contenu du fichier *.pq par le squelette ci-dessousBuild → génération du fichier .mezDéposer le .mez dans Documents\Power BI Desktop\Custom Connectors (puis autoriser les extensions dans Options > Sécurité, selon politique interne)
Support formation Rapports Power BI
- Pages
Connecteur personnalisé
sample35_ConnectorAPI.pbix
20 kB
Mode opératoire
Fichier pq
section ClientVentes;
// ===========================
// PUBLISH (entrée dans "Obtenir des données")
// ===========================
[DataSource.Kind="ClientVentes", Publish="ClientVentes.Publish"]
shared ClientVentes.Contents =
Value.ReplaceType(ClientVentesImpl,
type function (
ApiBaseUrl as (type text meta [Documentation.FieldCaption="API Base URL, par ex. https://akbbuehslmcrjsgupcon.supabase.co/functions/v1"]),
ApiPathClients as (type text meta [Documentation.FieldCaption="Chemin Clients par ex. /api/clients", Publish.Preview.Default="/api/clients"]),
ApiPathVentes as (type text meta [Documentation.FieldCaption="Chemin Ventes par ex. /api/ventes", Publish.Preview.Default="/api/ventes"]),
AuthBaseUrl as (type text meta [Documentation.FieldCaption="Auth Base URL, par ex. https://akbbuehslmcrjsgupcon.supabase.co", Publish.Preview.Default="https://akbbuehslmcrjsgupcon.supabase.co"]),
optional LoginPath as (type text meta [Documentation.FieldCaption="Login/Token Path", Publish.Preview.Default="/api/auth/token-from-key"]),
optional APIKey as (type text meta [Documentation.FieldCaption="API Key (prioritaire si non vide)"]),
optional ClientId as (type text meta [Documentation.FieldCaption="Client ID"]),
optional ClientSecret as (type text meta [Documentation.FieldCaption="Client Secret"]),
optional Scope as (type text meta [Documentation.FieldCaption="Scope"]),
optional Email as (type text meta [Documentation.FieldCaption="Email"]),
optional Password as (type text meta [Documentation.FieldCaption="Password"])
) as table
);
// ===========================
// IMPL
// ===========================
ClientVentesImpl =
(
ApiBaseUrl as text,
ApiPathClients as text,
ApiPathVentes as text,
AuthBaseUrl as text,
optional LoginPath as nullable text,
optional APIKey as nullable text,
optional ClientId as nullable text,
optional ClientSecret as nullable text,
optional Scope as nullable text,
optional Email as nullable text,
optional Password as nullable text
) as table =>
let
// ---- Defaults (adapter) ----
_ApiBaseUrl = ApiBaseUrl,
_ApiPathClients = ApiPathClients,
_ApiPathVentes = ApiPathVentes,
_AuthBaseUrl = AuthBaseUrl,
_LoginPath = if IsNonEmpty(LoginPath) then LoginPath else "/api/auth/token-from-key",
// ---- Auth precedence ----
Headers = GetAuthHeaders(_AuthBaseUrl, _LoginPath, APIKey, ClientId, ClientSecret, Scope, Email, Password),
// ---- Calls ----
ClientsJson = CallApiJson(_ApiBaseUrl, _ApiPathClients, Headers),
VentesJson = CallApiJson(_ApiBaseUrl, _ApiPathVentes, Headers),
ClientsData = GetDataArray(ClientsJson),
VentesData = GetDataArray(VentesJson),
TblClients = Table.FromList(ClientsData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
TblClients2 =
if Table.RowCount(TblClients) > 0 and Value.Is(TblClients{0}[Column1], type record)
then Table.ExpandRecordColumn(TblClients, "Column1", Record.FieldNames(TblClients{0}[Column1]))
else TblClients,
TblVentes = Table.FromList(VentesData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
TblVentes2 =
if Table.RowCount(TblVentes) > 0 and Value.Is(TblVentes{0}[Column1], type record)
then Table.ExpandRecordColumn(TblVentes, "Column1", Record.FieldNames(TblVentes{0}[Column1]))
else TblVentes,
// ---- Navigation table (Clients & Ventes) ----
Nav = #table(
type table [Name = text, Data = any, ItemKind = text, ItemName = text, IsLeaf = logical],
{
{"Clients", TblClients2, "Table", "Table", true},
{"Ventes", TblVentes2, "Table", "Table", true}
}
)
//Nav2 = Table.ToNavigationTable(Nav, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
Nav;
// ===========================
// AUTH HELPERS
// ===========================
GetAuthHeaders =
(
AuthBaseUrl as text,
LoginPath as text,
APIKey as nullable text,
ClientId as nullable text,
ClientSecret as nullable text,
Scope as nullable text,
Email as nullable text,
Password as nullable text
) as record =>
let
HasApiKey = IsNonEmpty(APIKey),
HasClientCred = IsNonEmpty(ClientId) and IsNonEmpty(ClientSecret),
HasUserPass = IsNonEmpty(Email) and IsNonEmpty(Password),
Result =
if HasApiKey then
// Mode 1 : APIKey uniquement (prioritaire)
[Accept="application/json", #"X-API-Key"=Text.From(APIKey)]
else if HasClientCred then
// Mode 2 : Client credentials -> token -> Bearer
let
Token = GetToken_ClientCredentials(AuthBaseUrl, LoginPath, Text.From(ClientId), Text.From(ClientSecret), Scope),
TokenType = "Bearer"
in
[Accept="application/json", Authorization = TokenType & " " & Token]
else if HasUserPass then
// Mode 3 : Email/Password -> token -> Bearer
let
Token = GetToken_EmailPassword(AuthBaseUrl, LoginPath, Text.From(Email), Text.From(Password)),
TokenType = "Bearer"
in
[Accept="application/json", Authorization = TokenType & " " & Token]
else
error "Aucune auth valide : renseigner APIKey, ou ClientId+ClientSecret, ou Email+Password."
in
Result;
GetToken_ClientCredentials =
(AuthBaseUrl as text, LoginPath as text, ClientId as text, ClientSecret as text, Scope as nullable text) as text =>
let
BodyText =
Uri.BuildQueryString([
grant_type = "client_credentials",
client_id = ClientId,
client_secret = ClientSecret,
scope = if Scope = null then "" else Text.From(Scope)
]),
Resp =
Json.Document(
Web.Contents(
AuthBaseUrl,
[
RelativePath = LoginPath,
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
Accept = "application/json"
],
Content = Text.ToBinary(BodyText),
ManualStatusHandling = {400,401,403,500}
]
)
),
Tok =
if Record.HasFields(Resp, "access_token") then Text.From(Resp[access_token])
else if Record.HasFields(Resp, "token") then Text.From(Resp[token])
else error "Token introuvable (client_credentials)."
in
Tok;
GetToken_EmailPassword =
(AuthBaseUrl as text, LoginPath as text, Email as text, Password as text) as text =>
let
LoginBody = Json.FromValue([ email = Email, password = Password ]),
Resp =
Json.Document(
Web.Contents(
AuthBaseUrl,
[
RelativePath = LoginPath,
Headers = [
#"Content-Type" = "application/json",
Accept = "application/json"
],
Content = LoginBody,
ManualStatusHandling = {400,401,403,500}
]
)
),
Tok =
if Record.HasFields(Resp, "token") then Text.From(Resp[token])
else if Record.HasFields(Resp, "access_token") then Text.From(Resp[access_token])
else if Record.HasFields(Resp, "data") and Record.HasFields(Resp[data], "token") then Text.From(Resp[data][token])
else error "Token introuvable (email/password)."
in
Tok;
// ===========================
// API HELPERS
// ===========================
CallApiJson =
(ApiBaseUrl as text, ApiPath as text, Headers as record) as any =>
let
Bin =
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.