Forum Discussion
Custom Query in Power BI Service
Hello,
I have a data set that is available at a web URL where the date is encoded in a Uri GET variable. I have the PowerQuery functioning perfectly on the desktop, but the "refresh" option isn't available on the Power BI service. Can anyone offer insights as to how to help with this? My URL looks something like this:
"https://website.com?CDST=VariableValueHere
&DateFrom=2%2f19%2f2020+5%3a00%3a00+AM
&DateTo="&DateTimeZone.ToText(DateTimeZone.UtcNow(), "M'/'d'/'yyyy+h:mm:ss+tt")&"
&Format=CSV
&IncludeHeaders=True
&AppendDate=False"
you can see DateTimeZone.ToText(DateTimeZone.UtcNow(), "M'/'d'/'yyyy+h:mm:ss+tt") in the middle indicating that I'm pulling to the current date/time. I only need to refresh it once daily, however, it won't let me do this.
Thank you in advance for your help,
4 Replies
Hi,
Try to use DAX instead of M code in Power Query,
Last updated at = UTCNOW()Check out my report, the data source is a URL and I added a visual of the UTC time using DAX. Bottom right of the Home page:The refresh is through a personal Gateway 8 times a day and works just fine.Hope you like it.First of all, that is a very cool dashboard! I'm impressed.
With that said, here's my problem. The data source URL changes by day, so I need to find a way to query a web CSV that is available at a changing address. An example is this:
Today my CSV is available at www.website.com/handler.aspx?from=may1&to=may30
Tomorrow my CSV is available at www.website.com/handler.aspx?from=may1&to=may31
I can accomplish this by using some M to reference the date dynamically. I can't have the data source refresh automatically though. Any options? Would the personal version of the gateway help in this instance?
Hi,
Thank you for the compliment on my dahsboard.
I am not sure that Power BI Gateway(on premises or personal) supports the change of the data source URL every day automatically and dynamically!
Maybe trying the 'Parameter' in Power Query can help you solve the problem,https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters
Please let me know if this will work out for you!
