Unable to Api connection based on Token not working for passing dynamic parameter to get data

[ravipandey]

Mquery:

let
clientId = "*****",
clientSecret = "*******",
tokenUrl = "https://**********************/connect/token",

tokenBody = [
client_id = clientId,
client_secret = clientSecret,
grant_type = "password",
username = "adminuser@********.com",
password = "Test@123"
],

tokenResponse = Web.Contents(tokenUrl, [
Content = Text.ToBinary(Uri.BuildQueryString(tokenBody)),
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
]),
tokenJson = Json.Document(tokenResponse),
accessToken = tokenJson[access_token],

apiUrl = "https://**********************************/api/**************/***********",
requestBody = Json.FromValue([
StartDate = DateTime.ToText(StartDateParam, "yyyy-MM-ddTHH:mm:ss"),
EndDate = DateTime.ToText(EndDateParam, "yyyy-MM-ddTHH:mm:ss"),
TransmitterID = TransmitterIDParam
]),

response = Web.Contents(apiUrl, [
Headers = [
Authorization = "Bearer " & accessToken,
#"Content-Type" = "application/json"
],
Content = requestBody
]),

// Directly convert JSON List to Table
jsonResponse = Json.Document(response),
resultTable = Table.FromList(jsonResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
finalTable = Table.ExpandRecordColumn(resultTable, "Column1", Record.FieldNames(jsonResponse{0}))
in
finalTable

________________________________________________________________________________________---

 Token should be created dynamic after changing of the parameters and generate report based on parameter pass from Slicer 

[v-priyankata]

Hi @ravipandey 

Thank you for reaching out to the Microsoft Fabric Forum Community.
@grazitti_sapna Thabks for the inputs.

The suggestion from the user was helpful. Have you tried following the steps provided? If not, please give them a try and let us know if you encounter any issues. If you need further assistance, feel free to reach out to the community. We are happy to help you.

Thanks.

[v-priyankata]

Hi @ravipandey 

I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @ravipandey 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. if you have any other issues please reach community.

[grazitti_sapna · Accepted solution]

Hi @ravipandey,

• Power BI Desktop does not allow truly dynamic Web.Contents calls during interactive filtering unless using a function.

• This means we need to turn your query into a parameterized function and then invoke it using a table based on slicer selections.

Follow below steps to achieve this:

Create three parameters:

• StartDateParam (type: Date/DateTime)

• EndDateParam (type: Date/DateTime)

• TransmitterIDParam (type: Text or Number)

You can later bind these to what users select via slicers.

Turn Your Query into a Function

(shared StartDateParam as datetime, EndDateParam as datetime, TransmitterIDParam as text) =>
let
clientId = "*****",
clientSecret = "*******",
tokenUrl = "https://**********************/connect/token",

tokenBody = [
client_id = clientId,
client_secret = clientSecret,
grant_type = "password",
username = "adminuser@********.com",
password = "Test@123"
],

tokenResponse = Web.Contents(tokenUrl, [
Content = Text.ToBinary(Uri.BuildQueryString(tokenBody)),
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
]),
tokenJson = Json.Document(tokenResponse),
accessToken = tokenJson[access_token],

apiUrl = "https://**********************************/api/**************/***********",
requestBody = Json.FromValue([
StartDate = DateTime.ToText(StartDateParam, "yyyy-MM-ddTHH:mm:ss"),
EndDate = DateTime.ToText(EndDateParam, "yyyy-MM-ddTHH:mm:ss"),
TransmitterID = TransmitterIDParam
]),

response = Web.Contents(apiUrl, [
Headers = [
Authorization = "Bearer " & accessToken,
#"Content-Type" = "application/json"
],
Content = requestBody
]),

jsonResponse = Json.Document(response),
resultTable = Table.FromList(jsonResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
finalTable = Table.ExpandRecordColumn(resultTable, "Column1", Record.FieldNames(jsonResponse{0}))
in
finalTable

Create a Parameter Table in Power BI with StartDate, EndDate, TransmitterID

Make sure this table is connected to slicers in your report.

Use fx_GetDataByParams to Fetch the API Data

let
paramRow = #"Parameter Table"{0}, // use first row
StartDate = paramRow[StartDate],
EndDate = paramRow[EndDate],
TransmitterID = paramRow[TransmitterID],
apiData = fx_GetDataByParams(StartDate, EndDate, TransmitterID)
in
apiData

• Data won’t automatically change on slicer click (only works for visuals, not data fetch)

• You must refresh the data manually (Refresh button) to re-trigger the query with new parameters

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!