How to pull the column data from rest api dynamically.

[AkhilReddyN]

Hello Everyone,

Data :

I am pulling the data from Rest API, and fetching the data from the blank query,

by writing a specific Code (mentioned below). Here I manually given the value for Region, but we have a other table that has list of regions which I want to loop through instead of editing the JSON every time.   Details in the Request Body, for example "Region = Asia" the data is fetching with the Asia details, but I need "Europe", "Africa",..... and other counties also to fetch the entire column data ("Name of column is Region").

Is it possible to declare a variable with region and passing it to the value of Region in the request body?

 Code : 

let
    requestBody = "{
                                    ""currencyCode"": ""USD"",
                                    ""endDate"": ""2024-04-01T00:00:00.000Z"",
            ""Region"": ""Asia"",
                                    ""provider"": ""ICE"",
            ""startDate"": ""2024-01-01T00:00:00.000Z""
}

Any insight would be greatly appreciated. 

[Anonymous]

Hi @AkhilReddyN ,

Please try this:

let
    GetCurrencyData = (region as text) as record =>
        let
            url = "YOUR_API_ENDPOINT",
            headers = [#"Content-Type"="application/json"],
            body = Text.FromBinary(Json.FromValue([
                currencyCode = "USD",
                endDate = "2024-04-01T00:00:00.000Z",
                Region = region,
                provider = "ICE",
                startDate = "2024-01-01T00:00:00.000Z"
            ])),
            Source = Json.Document(Web.Contents(url, [Headers=headers, Content=Text.ToBinary(body)]))
        in
            Source,
    regions = {"Asia", "Europe", "Africa"},  // This list should come from your regions table
    Result = List.Transform(regions, each GetCurrencyData(_))
in
    Result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[AkhilReddyN]

Hi @Anonymous ,

I have 2 points from above code are written below

1.I have tried with the code which you have given above but i am facing error below is the snap. 

2.One question from above code (regions ={"Asia", "Europe", "Africa"} here in the region column if i have more then thousand rows i can not enter manually, is there any dynamic way to update directly region column in it).

Thanks

Akhil

[AkhilReddyN]

Hi @Anonymous 

Thank you for the information but i have a query below,

 How to bring the regions from a different table instead of entering manually, its just an example we have 1000's of records and more, to be read dynamically.

Thanks 

Akhil