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!
