Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shamatix
Post Partisan
Post Partisan

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 out:)

1 REPLY 1
Greg_Deckler
Super User
Super User

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...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.