Custom connector for Power BI source rest API with Oauth2 authentication

TecnoIsowean — Mon, 07 Aug 2023 14:14:04 GMT

We are developing dashboards by connecting to the ERP (Finnegans) through an API REST with AOuth2.

We developed an M code in Power Query to connect with the data and published dashboards to Power BI Service, where they can be viewed correctly. However, the issue we are facing is that we are unable to schedule automatic refreshes “the following data sources don’t support refresh”

We understand that this error is because the API connection is generated using a dynamic Access Token under the OAuth2 protocol. While researching online, we came across the following link, that suggests creating a Custom Connector to address this issue: https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-query-power-bi/

We've managed to create a custom connector that with the API that gets the connection token to the ERP (Finnegans) API in Visual Studio:

----Code in VS

section FinnegansFede2; [DataSource.Kind="FinnegansFede2", Publish="FinnegansFede2.Publish"] shared FinnegansFede2.Contents = () => let // Definición de los datos de autenticación UrlBase1 = "https://api.teamplace.finneg.com/", RelativePathValue1 = "api/oauth/token", GrantTypeParam = "grant_type=" & Uri.EscapeDataString("client_credentials"), ClientIdParam = "client_id=" & Uri.EscapeDataString("48695d6c4b7b95915165a72e4e7e0631"), ClientSecretParam = "client_secret=" & Uri.EscapeDataString("51381fb4d17416da53ca9d507dbb221g"), QueryString1 = GrantTypeParam & "&" & ClientIdParam & "&" & ClientSecretParam, FullUrl1 = UrlBase1 & RelativePathValue1 & "?" & QueryString1, tokeni = Text.FromBinary(Web.Contents(FullUrl1)), // Construcción del token de acceso (Access Token) UrlBase = "https://api.teamplace.finneg.com/", RelativePathValue = "api/reports/USR_ANALISISDEFACTURADECOMPRAISW", AccessToken = Uri.EscapeDataString(tokeni) in AccessToken; // Data Source Kind description FinnegansFede2 = [ Authentication = [ Basic = [] ], Label = Extension.LoadString("DataSourceLabel") ]; // Data Source UI publishing description FinnegansFede2.Publish = [ Beta = true, Category = "Other", ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") }, LearnMoreUrl = "https://powerbi.microsoft.com/", SourceImage = FinnegansFede2.Icons, SourceTypeImage = FinnegansFede2.Icons ]; FinnegansFede2.Icons = [ Icon16 = { Extension.Contents("FinnegansFede216.png"), Extension.Contents("FinnegansFede220.png"), Extension.Contents("FinnegansFede224.png"), Extension.Contents("FinnegansFede232.png") }, Icon32 = { Extension.Contents("FinnegansFede232.png"), Extension.Contents("FinnegansFede240.png"), Extension.Contents("FinnegansFede248.png"), Extension.Contents("FinnegansFede264.png") } ];

----

We used this connector into Power BI Desktop and successfully published it to Power BI Service and established automatic refreshes by setting up an On-premises Gateway.

Now we’re struggling to build a connector with the Api that brings the actual Data. We’ve tried two ways and failed in both:

We used the custom connector previously mentioned that brings the token (Origen1) in Power Query M query to obtain the table. The code is as follows:

-------------M code

let Origen1 = FinnegansFede2.Contents(null), FechaDesdeParam = "FechaDesde=" & Uri.EscapeDataString(desde), FechaHastaParam = "FechaHasta=" & Uri.EscapeDataString(hasta), DimensionParam = "PARAMWEBREPORT_dimension=" & Uri.EscapeDataString("DIMCTC"), EmpresaParam = "PARAMEmpresa=" & Uri.EscapeDataString("EMPRE01"), QueryString = Origen1 & "&" & FechaDesdeParam & "&" & FechaHastaParam & "&" & DimensionParam & "&" & EmpresaParam, UrlBase = "https://api.teamplace.finneg.com/", RelativePathValue = "api/reports/USR_ANALISISDEFACTURADECOMPRAISW", FullUrl = UrlBase & RelativePathValue & "?" & QueryString, Origen = Json.Document(Web.Contents(FullUrl)), DatosOriginales = Table.FromList(Origen, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

DatosOriginales

---------------------------

This works within Power BI Desktop and allows us to publish. However, the error while attempting to schedule automatic refresh remains (the following data sources don’t support refresh…”). Since the API for the data is still in the M code we didn’t solve the issue.

We’ve tried developing a Custom Connector in Visual Studio for the actual data, instead of just bringing the token. The code looks like this:

-----------

// This file contains your Data Connector logic
section Finnegans;

[DataSource.Kind="Finnegans", Publish="Finnegans.Publish"]
shared Finnegans.Contents = (optional message as text) =>
let
UrlBase1 = "https://api.teamplace.finneg.com/",
RelativePathValue1 = "api/oauth/token",
GrantTypeParam = "grant_type=" & Uri.EscapeDataString("client_credentials"),
ClientIdParam = "client_id=" & Uri.EscapeDataString("48695d6c4b7b95915165a72e4e7e0631"),
ClientSecretParam = "client_secret=" & Uri.EscapeDataString("51381fb4d17416da53ca9d507dbb221f"),
QueryString1 = GrantTypeParam & "&" & ClientIdParam & "&" & ClientSecretParam,
FullUrl1 = UrlBase1 & RelativePathValue1 & "?" & QueryString1,
tokeni = Text.FromBinary(Web.Contents(FullUrl1)),
UrlBase = "https://api.teamplace.finneg.com/",
RelativePathValue = "api/reports/USR_ANALISISDEFACTURADECOMPRAISW",
AccessToken = "ACCESS_TOKEN=" & Uri.EscapeDataString(tokeni),
FechaDesdeParam = "FechaDesde=" & Uri.EscapeDataString("2023-07-01"),
FechaHastaParam = "FechaHasta=" & Uri.EscapeDataString("2023-07-05"),
DimensionParam = "PARAMWEBREPORT_dimension=" & Uri.EscapeDataString("DIMCTC"),
EmpresaParam = "PARAMEmpresa=" & Uri.EscapeDataString("EMPRE01"),
QueryString = AccessToken & "&" & FechaDesdeParam & "&" & FechaHastaParam & "&" & DimensionParam & "&" & EmpresaParam,
FullUrl = UrlBase & RelativePathValue & "?" & QueryString,
Origen = Json.Document(Web.Contents(FullUrl)),
DatosOriginales = Table.FromList(Origen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Se expandió Column1" = Table.ExpandRecordColumn(DatosOriginales, "Column1", {"TRANSACCIONSUBTIPOID", "IDDOCUMENTO", "CELULA", "FECHA", "FECHACOMPROBANTE", "TRANSACCIONTIPONOMBRE", "TIPODOCUMENTO", "TRANSACCIONID", "DOCNROINT", "COMPROBANTE", "COMPROBANTEADICIONAL", "NUMEROCONTRATO", "DIMENSIONVALOR", "TOTALBRUTO", "TOTALCONCEPTOS", "TOTAL", "CLIENTE", "CUIT", "DESCRIPCION", "CONDICIONPAGO", "MONEDA", "COTIZACION", "LISTAPRECIO", "VENDEDOR", "PRODUCTO", "MARCA", "DESCITEM", "CANTIDAD", "CANTIDADSTOCK2", "UNIDADVENTA", "UNIDADCOMPRA", "UNIDADSTOCK", "UNIDADSTOCK2", "PRECIO", "PRECIOMONPRINCIPAL", "PRECIOMONSECUNDARIA", "IMPORTEMONPRINCIPAL", "IMPORTEMONSECUNDARIA", "DEPOSITOORIGEN", "DEPOSITODESTINO", "PRECIOSOBRE", "IMPORTE", "GRAVADO", "NO GRAVADO", "PROVEEDOR", "PARTIDA", "ESTADO", "CODIGOPROD", "PENDIENTEORIGEN", "PENDIENTEDESTINO", "IMPORTEPENDIENTEORIGEN", "IMPORTEPENDIENTEDESTINO", "ORGANIZACION", "CUENTA", "EMPRESA", "ANO", "ANO-MES", "PRODUCTORAMA1", "PRODUCTORAMA2", "PRODUCTORAMA3", "PRODUCTORAMAN", "PORCENTAJEIMPOSITIVO", "CONTROLIMPOSITIVO3", "GRAVADOPORTASAIMPOSITIVA", "GRAVADOPORTASAIMPOSITIVAMONEDAPRINCIPAL", "@@CLASEVO", "FECHAPROXIMOPASO", "SEMANACARGADESDE", "SEMANACARGAHASTA", "PROVINCIADESTINO", "PROVINCIAORIGEN", "COORDENADAS", "CORREDOR", "SUCURSAL", "CAI/CAE", "NIVEL1DIMENSION", "NIVEL2DIMENSION", "NIVEL1CLIENTE", "NIVEL2CLIENTE", "PROVINCIADESTINOITEM", "PERCEPCIONES", "SUBFAMILIA", "FAMILIA", "RUBRO", "ACTIVIDADIVA", "WORKFLOW", "IDENTIFICACIONEXTERNA", "ETAPAS DE OBRA"}, {"TRANSACCIONSUBTIPOID", "IDDOCUMENTO", "CELULA", "FECHA", "FECHACOMPROBANTE", "TRANSACCIONTIPONOMBRE", "TIPODOCUMENTO", "TRANSACCIONID", "DOCNROINT", "COMPROBANTE", "COMPROBANTEADICIONAL", "NUMEROCONTRATO", "DIMENSIONVALOR", "TOTALBRUTO", "TOTALCONCEPTOS", "TOTAL", "CLIENTE", "CUIT", "DESCRIPCION", "CONDICIONPAGO", "MONEDA", "COTIZACION", "LISTAPRECIO", "VENDEDOR", "PRODUCTO", "MARCA", "DESCITEM", "CANTIDAD", "CANTIDADSTOCK2", "UNIDADVENTA", "UNIDADCOMPRA", "UNIDADSTOCK", "UNIDADSTOCK2", "PRECIO", "PRECIOMONPRINCIPAL", "PRECIOMONSECUNDARIA", "IMPORTEMONPRINCIPAL", "IMPORTEMONSECUNDARIA", "DEPOSITOORIGEN", "DEPOSITODESTINO", "PRECIOSOBRE", "IMPORTE", "GRAVADO", "NO GRAVADO", "PROVEEDOR", "PARTIDA", "ESTADO", "CODIGOPROD", "PENDIENTEORIGEN", "PENDIENTEDESTINO", "IMPORTEPENDIENTEORIGEN", "IMPORTEPENDIENTEDESTINO", "ORGANIZACION", "CUENTA", "EMPRESA", "ANO", "ANO-MES", "PRODUCTORAMA1", "PRODUCTORAMA2", "PRODUCTORAMA3", "PRODUCTORAMAN", "PORCENTAJEIMPOSITIVO", "CONTROLIMPOSITIVO3", "GRAVADOPORTASAIMPOSITIVA", "GRAVADOPORTASAIMPOSITIVAMONEDAPRINCIPAL", "@@CLASEVO", "FECHAPROXIMOPASO", "SEMANACARGADESDE", "SEMANACARGAHASTA", "PROVINCIADESTINO", "PROVINCIAORIGEN", "COORDENADAS", "CORREDOR", "SUCURSAL", "CAI/CAE", "NIVEL1DIMENSION", "NIVEL2DIMENSION", "NIVEL1CLIENTE", "NIVEL2CLIENTE", "PROVINCIADESTINOITEM", "PERCEPCIONES", "SUBFAMILIA", "FAMILIA", "RUBRO", "ACTIVIDADIVA", "WORKFLOW", "IDENTIFICACIONEXTERNA", "ETAPAS DE OBRA"}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Se expandió Column1",{{"FECHA", type date}, {"FECHACOMPROBANTE", type date}}),
#"Columnas quitadas" = Table.RemoveColumns(#"Tipo cambiado",{"COMPROBANTEADICIONAL", "NUMEROCONTRATO"}),
#"Tipo cambiado1" = Table.TransformColumnTypes(#"Columnas quitadas",{{"TOTALBRUTO", Currency.Type}, {"TOTALCONCEPTOS", Currency.Type}, {"TOTAL", Currency.Type}, {"COTIZACION", Currency.Type}}),
#"Columnas quitadas1" = Table.RemoveColumns(#"Tipo cambiado1",{"MARCA"}),
#"Tipo cambiado2" = Table.TransformColumnTypes(#"Columnas quitadas1",{{"CANTIDAD", type number}, {"CANTIDADSTOCK2", type number}, {"PRECIO", Currency.Type}, {"PRECIOMONPRINCIPAL", Currency.Type}, {"PRECIOMONSECUNDARIA", Currency.Type}, {"IMPORTEMONPRINCIPAL", Currency.Type}, {"IMPORTEMONSECUNDARIA", Currency.Type}, {"IMPORTE", Currency.Type}, {"GRAVADO", Currency.Type}, {"NO GRAVADO", Currency.Type}, {"PENDIENTEORIGEN", type number}, {"PENDIENTEDESTINO", type number}, {"IMPORTEPENDIENTEORIGEN", Currency.Type}, {"IMPORTEPENDIENTEDESTINO", Currency.Type}}),
#"Columnas quitadas2" = Table.RemoveColumns(#"Tipo cambiado2",{"PRODUCTORAMA1", "PRODUCTORAMA2", "PRODUCTORAMA3", "PRODUCTORAMAN", "CONTROLIMPOSITIVO3"}),
#"Tipo cambiado3" = Table.TransformColumnTypes(#"Columnas quitadas2",{{"PORCENTAJEIMPOSITIVO", Currency.Type}}),
#"Columnas quitadas3" = Table.RemoveColumns(#"Tipo cambiado3",{"ACTIVIDADIVA"}),
#"Tipo cambiado4" = Table.TransformColumnTypes(#"Columnas quitadas3",{{"GRAVADOPORTASAIMPOSITIVAMONEDAPRINCIPAL", Currency.Type}, {"GRAVADOPORTASAIMPOSITIVA", Currency.Type}}),
#"Columnas quitadas4" = Table.RemoveColumns(#"Tipo cambiado4",{"NIVEL1DIMENSION", "NIVEL2DIMENSION", "NIVEL1CLIENTE", "NIVEL2CLIENTE"}),
#"Tipo cambiado5" = Table.TransformColumnTypes(#"Columnas quitadas4",{{"PERCEPCIONES", Currency.Type}})
in
#"Tipo cambiado5";

// Data Source Kind description
Finnegans = [
Authentication = [
// Key = [],
// UsernamePassword = [],
// Windows = [],
Anonymous = []
],
Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
Finnegans.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = Finnegans.Icons,
SourceTypeImage = Finnegans.Icons
];

Finnegans.Icons = [
Icon16 = { Extension.Contents("Finnegans16.png"), Extension.Contents("Finnegans20.png"), Extension.Contents("Finnegans24.png"), Extension.Contents("Finnegans32.png") },
Icon32 = { Extension.Contents("Finnegans32.png"), Extension.Contents("Finnegans40.png"), Extension.Contents("Finnegans48.png"), Extension.Contents("Finnegans64.png") }
];

-----------

When executed in Visual Studio without specifying the connection method, the following error appears:

After manually setting CREDENTIAL TYPE to Anonymous, the code functions correctly and returns the desired table:

When attempting to use the connector in Power BI Desktop, the following error pops up:

We believe that there is an issue in the connector development that is preventing us from using it in Power BI Desktop, and presumably it has something to do with the error that pops up in Visual and made us manually set CREDENTIAL TYPE to Anonymous or the fact that it needs to first bring the Token and then the data set.

topic Custom connector for Power BI source rest API with Oauth2 authentication in Developer

Custom connector for Power BI source rest API with Oauth2 authentication