I have a requirement where the user can select multiple values (events) from a table in a Power BI report and these multiple values need to be passed as a query parameter string to a web API call from Power Query and then displayed as a new table. New events will be updated intraday, with millions of rows. (There are also a varying number of columns depending on the events selected, so various transformations have been performed so that all columns a then transformed to a single column and all values in another – to then be displayed in a Matrix visualisation.)
At this point I have managed to write a DAX expression to pull all the values together in order to add to the API. I can also create a parameter to add to the M code in Power Query which can be modified and I can view the expected results.
However I cannot pass this value from a DAX expression to Power Query. I thought that it may have been possible to do this via Power Automate, (to send the DAX value to Power Automate – then update a dataset, having populated a datasource (SQL Server). However, have issues in then referring to this new value as a parameter on the fly.
Please let me know if anyone has any thoughts as to how this could be achieved?
