Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
aj1973
Community Champion
Community Champion

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.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

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?

aj1973
Community Champion
Community Champion

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!

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors