Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
After reading https://powerbi.microsoft.com/en-us/blog/50-new-features-in-dataflows-this-month/ , I am attempting to use a custom function which has been authored and working correctly in desktop , to create a dataflow.
The custom function calls a web api using a single text string
(e.g. Source = Json.Document(Web.Contents("http://mywebapi.com/API....&date="&MyDate&"")
and may contain multiple parameters, but for simplicity of testing I have only included one - the date for which data is required. (MyDate as text)
By creating a single table column of dates (in text) and -> add column -> invoke custom fn in desktop I successfully execute the function and return data for all dates from the original.
Creating the exact same original query, parameter, and function in dataflows results in a "dynamic function" error. While it is stated in https://docs.microsoft.com/en-us/power-bi/service-dataflows-create-use limitations that dynamic functions are not supported, it appears from the November 2019 dataflows update this has been at least partially relaxed (e.g. to include combine binary functionality).
Interestingly, the dataflows UI does not provide an -> add column -> invoke custom function feature. However, by cutting and pasting M code from desktop, the function is created and can be invoked directly by entering a parameter in the dialog.
Hoping someone can provide some guidance if this feature is just not available in dataflows, or maybe an alternative workaround. It seems a simple text parameter change in a api call string within a dataflow should otherwise work ?
Thanks
Hi @pat_energetics ,
That should be a workaround. Please refer to the third - party blog. Athought there is an idea already.
https://ssbipolar.com/2018/12/12/custom-functions-in-power-bi-dataflows/
Thanks @v-frfei-msft . I had used the techniques described in Matt's blog to get the MyDate parameter to pass into the custom function. The issue appears to be in using the parameter in the source line to call the web api and limit it to the required date.
While the blog (from 2018) describes how to workaround the lack of a specific "invoke custom function" similar to the one found under "add column" in the desktop power query UI, cutting and pasting the M code into the dataflow still yeilds the dynamic function error in dataflows.
Perhaps the issue is actually what is considered a "custom function" and a "dynamic function" in a dataflow, but I'm unclear if a parameter can be used in a web api call within a dataflow in the first place. Perhaps the author of the latest blog post (Miguel Llopis) could provide some guidance.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.