cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

Hi @Alisina ,

in the service, there is still the "short" URL:

image.png

 

If that's because you've published the workbook before in the service, I believe it's a bug and you will not be able to make it working in the same workspace.

 

Try to publish in a different workspace and make sure it has a valid URL!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

@ImkeF 

Thank you for your effort. However, I feel like you have the past 5 times replied me with exactly what is written on the pictures I post for information. I would like to declare, that I know the problem. I am searching for a way to come to solution. There is no reason for you to point out the problem everytime, as I am aware of it. Pointing out the obvious problem only makes the question repeated.. I know that for some reason it doesnt run the full URL but the short version. And that I know it runs the first part of the URL written in Web.content() and doesnt run the rest which is followed by RelativePath  and Query. 

That is my question and how to make it work? 

It seriously does not help to answer a question by pointing out what is written in the picture, as I am already aware of it. 

Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

So have you tried it in a different workspace?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

Your query returns this error for me in Desktop, so it doesn't make any sense to run this in the service I believe:

 

image.png

 

Just recognized now that you've skipped the query-parameter. That's probably not a good idea. Please check this article for further details and ideas: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/ 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

@ImkeF 

I would be glad if you pointed out in the code what is wrong and what is right. Because I have tried with Query and without Query parameters and in both situations I either get it working in Dekstop buy not web (or partially in web) or I get it working in desktop only. 

I have tried every possible method and combination, but it seems the published report cannot work in autorefresh system. However, if you have a piece of code that seems to be working, I wonder if you would be kind to share ? thanks

Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

Edited answer:

Sorry, I cannot test if any of this is working, as non of the URL you've posted so far, returns any result for me.

 

But with regards to the general syntax, I suggest to start small and simply try to fetch a result from the page. You can then parse/transform the returned binary in additional steps.

Either so:

 

let
    Now = DateTime.LocalNow(),
    Yesterday = Date.AddDays(Now, -1),
    Date = DateTime.ToText(Yesterday, "dd-MM-yyyy hh:mm") ,   
    Date2 = DateTime.ToText(Now, "dd-MM-yyyy hh:mm"),

Output =   Web.Contents(
             "http://Yoursite.com/data/glovadata/search/csv?StartTimeLocal=11-04-2019 08:25&EndTimeLocal=12-04-2019 08:25", 
             [Query=[StartTimeLocal=Date, EndTimeLocal=Date2]]
            )
            
in
    Output

or maybe so:

 

let
    Now = DateTime.LocalNow(),
    Yesterday = Date.AddDays(Now, -1),
    Date = DateTime.ToText(Yesterday, "dd-MM-yyyy hh:mm") ,   
    Date2 = DateTime.ToText(Now, "dd-MM-yyyy hh:mm"),

Output =   Web.Contents(
             "https://yoursite.com/data/glovadata/search/csv?StartTimeLocal=11-04-2019%2008:25&EndTimeLocal=12-04-2019%2008:25", 
             [Query=[StartTimeLocal=Date, EndTimeLocal=Date2]]
            )
            
in
    Output

Pasting the URL that currently works for you from the browser to the first function parameter should do the job.

Point is to provide a valid fully hardoced URL with query parameters in the main call and to give the query parameters that should actually be used in the Query-record. This will then overwrite the hardcoded part.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

@Alisina , I have edited my answer above.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

@ImkeF 

Thank you for the input. For the data validation instead of yoursite.com you can put: onboard.dsb.dk and you should be able to operate on the data. 

However, using your code, I get to load data the same way as previous codes on desktop. However when published, I get the option to choose legitimations just like before, however the short url is displayed: 
"http://onboard.dsb.dk/data/glovadata/search/csv" and thus it returns error 400 or or 404. It doesnt return error if you choose a Niveau for security to none or any other. But instead it keeps spinning (as loading) and nothing happens.

please give it a go and test it yourself. 

thank you

Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

Not working for me, unfortunately:

image.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

@ImkeF 

That is because you URL is not correct. 

The produced URL contains simicolons ( : ) , which it shouldn't as URL encode does not use simicolons.

must be something like this: StartTimeLocal=12-04-2019%2002%3A12&EndTimeLocal=13-04-2019%2002%3A12