- Installer Visual Studio Code
- Installer l’extension Power Query SDK
- Créer un nouveau projet d’extension (template “Data Connector”)
- Remplacer le contenu du fichier *.pq par le squelette ci-dessous
- Build → génération du fichier .mez
- Déposer le .mez dans Documents\Power BI Desktop\Custom Connectors (puis autoriser les extensions dans Options > Sécurité, selon politique interne)
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 =
Web.Contents(
ApiBaseUrl,
[
RelativePath = ApiPath,
Headers = Headers,
Timeout = #duration(0,0,2,0),
ManualStatusHandling = {400,401,403,500}
]
),
J = Json.Document(Bin)
in
J;
GetDataArray = (ApiResponse as any) as list =>
if Value.Is(ApiResponse, type record) and Record.HasFields(ApiResponse, "data") and Value.Is(ApiResponse[data], type list)
then ApiResponse[data]
else if Value.Is(ApiResponse, type list)
then ApiResponse
else {};
IsNonEmpty = (v as any) as logical =>
v <> null and Text.Trim(Text.From(v)) <> "";
// ===========================
// KIND / PUBLISH METADATA
// ===========================
ClientVentes = [
TestConnection = (dataSourcePath) => { "ClientVentes.Contents", dataSourcePath },
Authentication = [
Anonymous = [] // Les secrets sont passés en paramètres dans cette version (APIKey/ClientId/Email...)
],
Label = "Client & Ventes",
SupportsEncryption = true
];
ClientVentes.Publish = [
Beta = true,
Category = "Other",
ButtonText = { "Client & Ventes", "Connexion à l'API Client & Ventes" },
SourceImage = ClientVentes.Icons,
SourceTypeImage = ClientVentes.Icons,
LearnMoreUrl = "https://github.com/gonzalezpat/connecteur-powerbi",
SupportsDirectQuery = false
];
ClientVentes.Icons = [
Icon16 = { Extension.Contents("ClientVentes16.png"), Extension.Contents("ClientVentes20.png"), Extension.Contents("ClientVentes24.png"), Extension.Contents("ClientVentes32.png") },
Icon32 = { Extension.Contents("ClientVentes32.png"), Extension.Contents("ClientVentes40.png"), Extension.Contents("ClientVentes48.png"), Extension.Contents("ClientVentes64.png") }
];
