cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Custom Query in Power BI Service

I think that the parameter query is likely the best way. I discovered that I can omit the "dateto" variable from the URL and it gives me exactly what I need.

View solution in original post

4 REPLIES 4
Highlighted
Post Prodigy
Post Prodigy

Re: Custom Query in Power BI Service

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.
Highlighted
Resolver I
Resolver I

Re: Custom Query in Power BI Service

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?

Highlighted
Post Prodigy
Post Prodigy

Re: Custom Query in Power BI Service

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!

 

 

Highlighted
Resolver I
Resolver I

Re: Custom Query in Power BI Service

I think that the parameter query is likely the best way. I discovered that I can omit the "dateto" variable from the URL and it gives me exactly what I need.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors