cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shamatix Member
Member

Dynamic API Call

Hello fellow users of Power Bi,

 

I have access to an API with an API key that changes every 60 minutes.

I have successfully managed to extract the key into Column2 as seen below.

Now I need to use the result in this "Column2 (The API Key)" into another query

 

let
apiUrl = "https://api.XXXXXXXX.com/v1/user/organizations",
options = [Headers =[#"Authorization"="ResultInColumn2"]],
result = Web.Contents(apiUrl , options),
#"Imported JSON" = Json.Document(result,1252)
in
#"Imported JSON"

 

I hope some of you can help me outSmiley Happy

1 REPLY 1
Super User
Super User

Re: Dynamic API Call

Create an M function like this:

 

let
    fnGetAuthorDetail = (url) =>
    let
        Source = Web.BrowserContents(url),
       #"Extracted Table From Html" = Html.Table(Source, {{"Column1", ".lia-property-value.dateRegistered"}, {"Column2", ".messagesPosted"}, {"Column3", ".kudosReceived"}, {"Column4", ".lia-panel.custom-component-about-me > DIV.lia-decoration-border:nth-child(1):nth-last-child(1) > .lia-decoration-border-content > DIV:nth-child(1):nth-last-child(1) > .lia-panel-content-wrapper"}}, [RowSelector=".lia-panel.custom-component-about-me > DIV.lia-decoration-border:nth-child(1):nth-last-child(1) > .lia-decoration-border-content > DIV:nth-child(1):nth-last-child(1) > .lia-panel-content-wrapper"]),
        #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column4", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column4.1", "Column4.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type time}, {"Column4.1", type text}, {"Column4.2", type text}}),
        #"Cleaned Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1", Text.Clean, type text}, {"Column4.1", Text.Clean, type text}, {"Column4.2", Text.Clean, type text}}),
        #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column4.2", Text.Trim, type text}, {"Column4.1", Text.Trim, type text}, {"Column1.1", Text.Trim, type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","-","/",Replacer.ReplaceText,{"Column1.1"}),
        #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Column4.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1", "Date"}, {"Column1.2", "Time"}, {"Column2", "Posted"}, {"Column3", "Kudos"}})
    in
        #"Renamed Columns"
in
    fnGetAuthorDetail

Obviously not exactly what you want but see this post on how to construct these sorts of things:

 

https://community.powerbi.com/t5/Community-Blog/Turning-quot-Web-by-Example-quot-into-Power-Query-Fu...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!