The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hopefully I'm going to explain this correctly.
I've been playing around with the REST API from Pexels to load images into a Power BI dashboard. I've got that bit working by doing the following
let
Source = Json.Document(Web.Contents(Text.Combine({"https://api.pexels.com/v1/photos/", "2014421"}), [Headers=[Authorization="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded src" = Table.ExpandRecordColumn(#"Converted to Table", "src", {"original", "large2x", "large", "medium", "small", "portrait", "landscape", "tiny"}, {"src.original", "src.large2x", "src.large", "src.medium", "src.small", "src.portrait", "src.landscape", "src.tiny"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded src",{{"id", Int64.Type}, {"width", Int64.Type}, {"height", Int64.Type}, {"url", type text}, {"photographer", type text}, {"photographer_url", type text}, {"photographer_id", Int64.Type}, {"avg_color", type text}, {"src.original", type text}, {"src.large2x", type text}, {"src.large", type text}, {"src.medium", type text}, {"src.small", type text}, {"src.portrait", type text}, {"src.landscape", type text}, {"src.tiny", type text}, {"liked", type logical}, {"alt", type text}})
in
#"Changed Type"
What I want to do is to have a slicer on my dashboard, which allows the user to select a value from 0 to 5 and this value is added to the API call above (specifically where you can see 2014421).
How do I take the slicer value and use it in the above query? Can this even be done? I thought a parameter might be the way to go, but I'm a bit confused TBH so I'd be grateful for any kind soul that can guide me.
Solved! Go to Solution.
Hi @RFJammer
This can't be done in Power BI.
Slicers operate on the report layer, after the data is loaded. Your API call happens in Power Query, which runs before the data reaches the model. There's no connection between a slicer and the query logic that generates or loads the data. You can't use a slicer to change the value in a Web.Contents
call.
If you want to change the number in the API URL (like "2014421"
) based on a user selection, the only option is to use a parameter in Power Query. That parameter must be changed manually in Power BI Desktop or via the dataset settings in the Power BI Service before refresh. It’s not interactive and it doesn’t respond to slicer selections.
Dynamic M Parameters allow some user interaction with queries, but only in DirectQuery mode and only with supported sources like SQL Server – not with web APIs.
This limitation is by design and applies to all import-based queries and API calls in Power BI.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi @RFJammer
As @Ritaf1983 already pointed out, this can't be done with slicers. Changes made in the Query Editor impact the data model, but not the other way around. Instead, consider using a query parameter or loading a table into the model that contains all the possible options, allowing users to make selections from there.
Hi @RFJammer,
Thank you for reaching out to Microsoft Fabric Community.
Thank you @danextian and @Ritaf1983 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Thanks and regards,
Anjan Kumar Chippa
Hi @RFJammer,
We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Thanks and regards,
Anjan Kumar Chippa
Hi @RFJammer,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Thanks and regards,
Anjan Kumar Chippa
Hi @RFJammer
As @Ritaf1983 already pointed out, this can't be done with slicers. Changes made in the Query Editor impact the data model, but not the other way around. Instead, consider using a query parameter or loading a table into the model that contains all the possible options, allowing users to make selections from there.
Hi @RFJammer
This can't be done in Power BI.
Slicers operate on the report layer, after the data is loaded. Your API call happens in Power Query, which runs before the data reaches the model. There's no connection between a slicer and the query logic that generates or loads the data. You can't use a slicer to change the value in a Web.Contents
call.
If you want to change the number in the API URL (like "2014421"
) based on a user selection, the only option is to use a parameter in Power Query. That parameter must be changed manually in Power BI Desktop or via the dataset settings in the Power BI Service before refresh. It’s not interactive and it doesn’t respond to slicer selections.
Dynamic M Parameters allow some user interaction with queries, but only in DirectQuery mode and only with supported sources like SQL Server – not with web APIs.
This limitation is by design and applies to all import-based queries and API calls in Power BI.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.