00 Web Contents Cheat Sheet

let
    P1 = "myParameter1",
    P2 = "myParameter2",
    #"Header" = Binary.ToText(Text.ToBinary(#"UserName"&":" & #"Password")),
    #"BaseURL" = "https://yourwebpage.com/api/3",
#"DaysLogic" = 
        if
            #"JIRA Days" = null
        then
            ""
        else
            "created > startOfDay(""-" & #"JIRA Days" & "d"")",
content =
        "{
            ""firstParameter"" : """& P1 &""",
            ""secondParameter"" : """& P2 &"""
        }"
     ,
Source = Json.Document(
        Web.Contents(
            #"BaseURL",
            [
                RelativePath = "/search",
                Query = 
                    [
                        query1 = Text.From(50),
                        query2 = "50",
                        query3 = #"DaysLogic"
                    ],
                Headers=[
                        #"Authorization" = "Basic " & #"Header",
                        #"X-Api-Key"=#"X-Api-Key",
                        #"content-type" = "application/json"
                ],
                Content=Text.ToBinary(content)
            ]
            )
    )
in
    Source

00 Web Contents Cheat Sheet.pq

let
    P1 = "myParameter1",
    P2 = "myParameter2",
    #"Header" = Binary.ToText(Text.ToBinary(#"UserName"&":" & #"Password")),
    #"BaseURL" = "https://yourwebpage.com/api/3",
#"DaysLogic" = 
        if
            #"JIRA Days" = null
        then
            ""
        else
            "created > startOfDay(""-" & #"JIRA Days" & "d"")",
content =
        "{
            ""firstParameter"" : """& P1 &""",
            ""secondParameter"" : """& P2 &"""
        }"
     ,
Source = Json.Document(
        Web.Contents(
            #"BaseURL",
            [
                RelativePath = "/search",
                Query = 
                    [
                        query1 = Text.From(50),
                        query2 = "50",
                        query3 = #"DaysLogic"
                    ],
                Headers=[
                        #"Authorization" = "Basic " & #"Header",
                        #"X-Api-Key"=#"X-Api-Key",
                        #"content-type" = "application/json"
                ],
                Content=Text.ToBinary(content)
            ]
            )
    )
in
    Source

01

let


let
    Source = Json.Document(
        Web.Contents(
            "https://api.chucknorris.io/jokes/random"
        )
    )
in
    Source

01.00 Chuck Basic.pq

let
    Source = Json.Document(
        Web.Contents(
            "https://api.chucknorris.io/jokes/random"
        )
    )
in
    Source

01.01 Chuck Categories.pq

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "categories"
            ]
        )
    )
in
    Source

01.02 Chuck Extracting a field.pq

let
    Source = Json.Document(
        Web.Contents(
            "https://api.chucknorris.io/jokes/random"
        )
    ),
    joke = Source[value]
in
    joke

01.03 Chuck Specific category.pq

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "random",
                Query =
                    [
                        category = "animal"
                    ]
            ]
        )
    ),
    joke = Source[value]
in
    joke

01.04 Chuck Specific Category 2.pq

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Parameters = 
        [
            category = "animal"
        ],
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "random",
                Query = Parameters
            ]
        )
    ),
    joke = Source[value]
in
    joke

01.05 Chuck First function.pq

(categoryText as text) =>

let
    BaseURL = "https://api.chucknorris.io/jokes/",       
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "random",
                Query = [
                    category = categoryText
                ]
            ]
        )
    ),
    joke = Source[value]
in
    joke

01.06 Using our function.pq

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "categories"
            ]
        )
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "Joke Fx", each #"Joke Fx"([Column1]))
in
    #"Invoked Custom Function"

02

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "categories"
            ]
        )
    )
in
    Source

02.01 Gumroad Sales.pq

/* 
Now that you are here, I invite you to check the most simple yet powerful and FREE
Power BI Theme Generator. https://powerbithemegenerator.bibb.pro 
 */

let

getSalesFx  = 
    /* 

    getSalesFx is the function required that requests Gumroad API the sales. 
    The API only retrieves 10 elements per request, thus there is need  to paginate
    if there is a next page, the response record will contain the field [next_page_key] 
    Gumroad API Documentation. https://app.gumroad.com/api#sales
    */
    (pageKey as text) =>
    let
        getSales = Json.Document(
            Web.Contents(
                "https://api.gumroad.com/v2",
                [
                    RelativePath = "/sales",
                    Query = 
                        [
                            access_token = "{Add your Gumroad Key}", 
                            //Generate Token https://app.gumroad.com/api#api-authentication
                            //Ideally, you should not be hardcoding your keys. https://www.bibb.pro/post/securing-your-api-power-bi-data-with-azure-key-vault
                            page_key=pageKey
                        ]
                ]
            )
        )
    in
        getSales,
Source = List.Generate(
    ()=> [
        pageKey = "",
        getSales = getSalesFx(pageKey)     
    ],
    /* A [success = "FALSE"] record is returned on the iterator when there are no more pages.   
    This is when the pagination will stops */
    each [getSales][success] <> "FALSE", 
    each [
        pageKey = if
            Record.HasFields([getSales],"next_page_key") = false
            then ""
            else [getSales][next_page_key],
        getSales = if  
            /* If there is no [next_page_key] field in the record, then we will create a record [success = "FALSE"] 
               If there is a [next_page_key] field record, then we will use the getSalesFx function with the next_page_key paramenter*/
            Record.HasFields([getSales],"next_page_key") = false
            then [success = "FALSE"] 
            else getSalesFx(pageKey)
    ],
    each [getSales][sales]
)
in
    Source

02.02 WIX Get contacts.pq

/* 
Now that you are here, I invite you to check the most simple yet powerful and FREE
Power BI Theme Generator. https://powerbithemegenerator.bibb.pro 
 */

let
    #"BaseURL" = "https://www.wixapis.com/",
    queryHeaders = [
                #"Authorization" = "{Your WIX API Key}",
                #"wix-account-id" = "{Your WIX Account ID}",
                #"wix-site-id" = "{Your WIX Site ID}"
        ],
    Source = List.Generate(
        ()=> [
            pageOffset = 0,
            getContacts =     Json.Document(
                Web.Contents(
                    #"BaseURL",
                    [
                        RelativePath = "contacts/v4/contacts?",
                        Query = 
                            [
                                paging.limit = "1000",
                                paging.offset = Number.ToText(pageOffset)
                            ],
                        Headers = queryHeaders
                    ]
                )
            )[contacts]
        ],
        each List.IsEmpty([getContacts]) = false,
        each [
            pageOffset = [pageOffset]+1000,
            getContacts =     Json.Document(
                Web.Contents(
                    #"BaseURL",
                    [
                        RelativePath = "contacts/v4/contacts?",
                        Query = 
                            [
                                paging.limit = "1000",
                                paging.offset = Number.ToText(pageOffset)
                            ],
                        Headers = queryHeaders
                    ]
                )
            )[contacts]
        ]
    )
in
    #"Source"

02.03 Wise List balances for a profile.pq

/* 
Now that you are here, I invite you to check the most simple yet powerful and FREE
Power BI Theme Generator. https://powerbithemegenerator.bibb.pro 
 */

let
    //API Documentation. https://docs.wise.com/api-docs/api-reference/balance#list
    Source = Json.Document(Web.Contents(
            "https://api.transferwise.com", 
            [
                //You will need to retrieve your profile ID through Postmant or CURL https://docs.wise.com/api-docs/api-reference/profile#list-profiles
                RelativePath="/v4/profiles/{Your profile id}/balances?types=STANDARD", 
                Headers=[
                        //How to get tokens https://docs.wise.com/api-docs/features/authentication-access/personal-tokens
                        Authorization="Bearer {your token key}", 
                        #"Content-Type"="application/json"
                ]
            ]
    ))
in
    Source

02.04 M Copilot

let
    Source = let
        metaDocumentation = type function (
            message1 as (type text meta [
                Documentation.FieldCaption = "MGPT Prompt",
                Documentation.SampleValues = {"Hello world"},
                Formatting.IsMultiLine = true,
                Formatting.IsCode = true
            ])
        ) as text meta [
        Documentation.Name = "M Copilot by Oscar Martinez https://bibb.pro",
        Documentation.LongDescription = "This Power Query M designed to communicate with the OpenAI API to generate responses based on a given prompt. The function is part of a larger framework aimed at integrating AI-powered text generation into Power Query processes."
        ],
        
        MGPT = (prompt as text) as text =>
        let
            url = "https://api.openai.com/v1/chat/completions",
            body = "{
                ""model"": ""gpt-3.5-turbo"",
                ""messages"": [
                    {
                        ""role"": ""system"",
                        ""content"": ""You are a Power Query expert, your role is to respond with  meaningful explanations and full code""
                    },
                    {
                        ""role"": ""user"",
                        ""content"": """ & prompt & """
                    }
                ]
            }",
            ApiKey = "{Your API Key}",  // Replace this with your actual OpenAI API key
            Headers = [
                #"Content-Type" = "application/json",
                #"Authorization" = "Bearer " & ApiKey
            ],
            Source = Json.Document(Web.Contents(url, [
                Headers = Headers,
                Content = Text.ToBinary(body)
            ])),
            Response = Source[choices]{0}[message][content]
        in
            Response
    in
       Value.ReplaceType( MGPT, metaDocumentation )
in
    Source

02.05 Mailjet full contact list.pq

//This API call was made with help of the M Copilot - https://www.linkedin.com/posts/oscarmartinezv_powerbi-powerquery-misformagic-activity-7163939233764737024-UbqP

let
    // Define the base URL for the API call
    baseUrl = "https://api.mailjet.com/v3/REST/contact/?limit=1000&offset=",
    
    // Helper function to make the API call
    callApi = (url as text) =>
        let
            // Make the API call
            response = Web.Contents(url, [
                Headers = [
                    #"Authorization" = "Basic {Your API Key}"
                ]
            ]),
            
            // Parse the API response as JSON
            json = Json.Document(response),
            
            // Extract the Count field from the response
            count = json[Count],
            
            // Extract the data from the response
            data = json[Data]
        in
            [Count = count, Data = data],
             
    // Generate the list of API call results
    results = List.Generate(
        () => [offset = 0, responseCount = 1, responseData = {}],
        each [responseCount] <> 0,
        each [
            offset = [offset] + 1000,
            apiResponse = callApi(baseUrl & Text.From([offset])),
            responseCount = apiResponse[Count],
            responseData = apiResponse[Data]
        ],
        each [responseData]
    ),
    
    // Combine all the paginated data into a single table
    combinedData = List.Combine(results),
    #"Converted to Table" = Table.FromList(combinedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"CreatedAt", "DeliveredCount", "Email", "ExclusionFromCampaignsUpdatedAt", "ID", "IsExcludedFromCampaigns", "IsOptInPending", "IsSpamComplaining", "LastActivityAt", "LastUpdateAt", "Name", "UnsubscribedAt", "UnsubscribedBy"}, {"CreatedAt", "DeliveredCount", "Email", "ExclusionFromCampaignsUpdatedAt", "ID", "IsExcludedFromCampaigns", "IsOptInPending", "IsSpamComplaining", "LastActivityAt", "LastUpdateAt", "Name", "UnsubscribedAt", "UnsubscribedBy"})
in
    #"Expanded Column1"

03

let
    Source = Json.Document(
        Web.Contents(
            "https://api.chucknorris.io/jokes/random"
        )
    ),
    joke = Source[value]
in
    joke

04.01

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "random",
                Query =
                    [
                        category = "animal"
                    ]
            ]
        )
    ),
    joke = Source[value]
in
    joke

04.02

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Parameters = 
        [
            category = "animal"
        ],
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "random",
                Query = Parameters
            ]
        )
    ),
    joke = Source[value]
in
    joke

05

(categoryText as text) =>

let
    BaseURL = "https://api.chucknorris.io/jokes/",       
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "random",
                Query = [
                    category = categoryText
                ]
            ]
        )
    ),
    joke = Source[value]
in
    joke

06

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "categories"
            ]
        )
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Invoked Custom Function" = Table.AddColumn(#"Converted to Table", "Joke Fx", each #"Joke Fx"([Column1]))
in
    #"Invoked Custom Function"

06.01

let
    BaseURL = "https://api.chucknorris.io/jokes/",
    Source = Json.Document(
        Web.Contents(
            BaseURL,
            [
                RelativePath = "categories"
            ]
        )
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Invoked Custom Function" = Table.AddColumn(
        #"Converted to Table", 
        "Joke Fx", 
        Function.InvokeAfter( 
            ()=> each #"Joke Fx"([Column1]),
            #duration(0,0,0,3)
        )
    )
in
    #"Invoked Custom Function"

07

let
    BAseURL = "	https://www.banxico.org.mx/SieAPIRest/service/v1",
    Source = Json.Document(
        Web.Contents(
            BAseURL,
            [
                RelativePath = "/series/SF60653",
                Query = [
                    token =  "0402f58c54b21c2cb35f4ecff9cde5ceb5f790e844906a7734b4947cace83f87"
                ]
            ]
        )
    ),
    bmx = Source[bmx],
    series = bmx[series],
    series1 = series{0}
in
    series1

08

let
    BAseURL = "	https://www.banxico.org.mx/SieAPIRest/service/v1",
    Token = BanxicoToken,
    Source = Json.Document(
        Web.Contents(
            BAseURL,
            [
                RelativePath = "/series/SF60653",
                Headers = [
                   #"Bmx-Token" = Token 
                ]
            ]
        )
    ),
    bmx = Source[bmx],
    series = bmx[series],
    series1 = series{0}
in
    series1

99.01

//https://developers.holded.com/reference/list-documents-1

let

Source = Json.Document(
        Web.Contents(
            "https://api.holded.com" ,
            [
                RelativePath = "/api/invoicing/v1/documents/invoice",
                Headers=[
                        Accept = "application/json",
                        key ="9e33e6761273769e5e33d88c225"
                ]
            ]
        )
    )
in
    Source