Forum Discussion
Calling two different APIs with different authentication requirements
Create a new function in Power Query to handle the TinyURL API authentication and URL shortening and define the authentication mechanism for the TinyURL API.
// Function to get TinyURL shortened URL
let
TinyURL_API_Key = "YourTinyURLApiKey",
BaseURL = "https://api.tinyurl.com/create",
ShortenURL = (LongURL as text) as text =>
let
Body = "{""url"":""" & LongURL & """, ""domain"":""tiny.one""}",
Options = [
Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & TinyURL_API_Key],
Content = Text.ToBinary(Body)
],
Response = Json.Document(Web.Contents(BaseURL, Options)),
ShortenedURL = Response[short_url]
in
ShortenedURL
in
ShortenURL
Then you call it in your main PQ :
let
Source = Web.Contents("https://your-main-api.com/reports", [Headers = [Authorization = "Bearer " & AccessToken]]),
Data = Json.Document(Source),
Reports = Data[reports],
LongURL = "https://your-main-api.com/reports/details?reportId=1234567890",
ShortenedURL = ShortenURL(LongURL),
FinalData = Table.AddColumn(Reports, "Shortened URL", each ShortenedURL)
in
FinalData
Connecting to an OAuth API Like PayPal With Power Query • My Online Training Hub
How to configure OAuth 2.0 Authentication for Microsoft Power Platform Custom Connectors
Handling authentication for Power Query connectors - Power Query | Microsoft Learn
- CMAltium2 years agoFrequent Visitor
Hi,
I tried your example, but had the same issue as before.
I have written a function very similar to the one that you shared.
However, when I try and run it within my script which uses an API with different credential requirements, it causes an issue in Power BI Desktop:
Here is an example snippet of my code:
// // Data Source definition // [DataSource.Kind="Nexar_PKCESample", Publish="Nexar_PKCESample.Publish"] shared Nexar_PKCESample.Contents = (url) => let accessToken = Extension.CurrentCredential()[access_token], url = "https://api.nexardev.com/graphql", contents = GetReport(accessToken) in contents; // // Data Source Kind definition // Nexar_PKCESample = [ Authentication = [ OAuth = [ // //Authentication for reporting API // StartLogin = StartLogin, FinishLogin = FinishLogin, Refresh = Refresh ] ], Label = Extension.LoadString("DataSourceLabel") ]; //TinyURL URL shortening GetReport = (accessToken) => let //... //Original script to get URL from a different API using the accessToken //... //Long URL from script above longUrl = "https://www.examplelongurl.com", shortUrl = shortenUrl(longUrl) in shortUrl; //Script for URL shortening shortenUrl = (longUrl) => let tinyUrl = "https://api.tinyurl.com/create?api_token=<MYTOKEN>", json_data = Json.FromValue([url = longUrl]), response = Web.Contents( tinyUrl, [ Content = json_data, Headers = [#"Content-Type" = "application/json"] ] ), #"JSON" = Json.Document(response), shortUrl = #"JSON"[data][tiny_url] in shortUrl;Here is the script for the self contained connector to tinyUrl:
// This file contains your Data Connector logic [Version = "1.0.0"] section tinyUrl; [DataSource.Kind="tinyUrl", Publish="tinyUrl.Publish"] shared tinyUrl.Contents = (optional message as text) => let longUrl="https://123testing.com", tinyUrl = "https://api.tinyurl.com/create?api_token=XcBFxMQDD4cmCQRavoSQdpiBjbdlkOElemeym5ObvwbvcAckIng7IyIejAE1", tinyUrlToken="XcBFxMQDD4cmCQRavoSQdpiBjbdlkOElemeym5ObvwbvcAckIng7IyIejAE1", json_data = Json.FromValue([url = longUrl]), response = Web.Contents( tinyUrl, [ Content = json_data, Headers = [#"Content-Type" = "application/json"] ] ), #"JSON" = Json.Document(response), shortUrl = #"JSON"[data][tiny_url] in shortUrl; // Data Source Kind description tinyUrl = [ Authentication = [ // Key = [], // UsernamePassword = [], // Windows = [], Anonymous = [] ], Label = "tinyUrl connector" ]; // Data Source UI publishing description tinyUrl.Publish = [ Beta = true, Category = "Other", ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") }, LearnMoreUrl = "https://powerbi.microsoft.com/", SourceImage = tinyUrl.Icons, SourceTypeImage = tinyUrl.Icons ]; tinyUrl.Icons = [ Icon16 = { Extension.Contents("tinyUrl16.png"), Extension.Contents("tinyUrl20.png"), Extension.Contents("tinyUrl24.png"), Extension.Contents("tinyUrl32.png") }, Icon32 = { Extension.Contents("tinyUrl32.png"), Extension.Contents("tinyUrl40.png"), Extension.Contents("tinyUrl48.png"), Extension.Contents("tinyUrl64.png") } ];When I run only that function in its own, self contained connector, I see this:
I can set the credentials using "Power Query SDK" > "Set Credentials" in the bottom left corner of Visual Studio.
Is there a way to do that programmatically, rather than manually?