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

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

Does anyone know how to fix this code so it receives data.

 

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"),

Terms = #table({"Dato1","Dato2"},
{{(Uri.BuildQueryString(Date)), (Uri.BuildQueryString(Date2))}}),
SearchSuccessful = (Dato1,Dato2) =>
    let
        Source = 
         Csv.Document(
           Web.Contents(
             "http://Yoursite.com/data/glovadata/search/csv?", 
             [Query=[StartTimeLocal=Dato1]&[EndTimeLocal=Dato2]]
            ),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]
           ),
        Success = Source[success]
    in
	Success,

     Output = Table.AddColumn(
       Terms, 
       "Search Successful", 
       each SearchSuccessful([Dato1],[Dato2])
      )
    
in
    Output

At the moment I get 3 columns with (Error) inside.

The Idea is to send an URL: "http://Yoursite.com/data/glovadata/search/csv?StartTimeLocal=08-04-2019%2002%3A12&EndTimeLocal=09-04..."

 

If the url link generated is not the same as above (with changeable date (last 24 hr data) ), then no data will be received. As the the database requires date to output data. 

The reason I use the shows method is so the data is updateable in powerbi.com

see link for reason: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

20 REPLIES 20
ImkeF
Super User
Super User

What does the error-message say?

 

Also, please note this passage from the blogpost you've mentioned: " This technique will only work if the url passed to the first parameter of Web.Contents() is valid in itself, is accessible and does not return an error.  "

Your URL doesn't look like that.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

The Error is shown as follows:

image.png

 

Pressing the Error in Search Successful shows:

Expression.Error: We cannot convert the value "08-04-2019 08:21" to the Record type.
Details:
     Value = 08-04-2019 08:21
     Type = type

Anonymous
Not applicable

I have now reduced the code to a half working state:

 

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

        Source = 
         Csv.Document(
           Web.Contents(
             "http://Yoursite.com/data/glovadata/search/csv?",
             [Query=Terms]
            ),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]
           )

in
    Terms

By running the "in Terms" it is clear that the date dependent urlencoded code generated in Terms is correct and is as it should be.. However, I do not know why it is not merging with the rest of the URL "http://Yoursite.com/data/glovadata/search/csv?", even though the [Query=Terms] is given.

The following Error is displayed when "in Source" is used in the end:

DataSource.Error: Web.Contents failed to retrieve content from 'http://Yoursite.com/data/glovadata/search/csv' (400): Bad Request
Details:
     DataSource Kind = Web
     DataSourcePath = http: //Yoursite.com/data/glovadata/search/csv
     Url = http: //Yoursite.com/data/glovadata/search/csv

You have to adjust your main-URL. See the blogpost you've mentioned:

 

image.png

The query will return values for oranges, although the main URL contains query-parameters for apples. 

So you just have to add a hardcoded query-string to your main URL. That will then be overwritten by the values from the Query-record.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Hi,

I have done as you and the blog mentioned. However, what is written in that blog regarding the first URL will be ignored and the Query read, is wrong. I get data from the URL and not from the Query.

This is a reduced and edited code version which works, but only using the first parameter of the URL already put as suggested. It does not read the Query somehow.

 

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

        Source = 
         Csv.Document(Web.Contents("http://Yoursite.com/data/glovadata/search/csv?StartTimeLocal=31-03-2019%2002%3A12&EndTimeLocal=01-04-2019%2002%3A12",
        [Query=Terms]),
        [Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])

in
    Source

@ImkeF 

Anonymous
Not applicable

I am trying to manage a code that I have been working on for some days now. 

The code runs fine and calls data if I get it out of table. I can update in PowerBi Desktop. However when uploaded to Powerbi.com, it does not update. 

I found a site: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

Chris the blog above showed another way, where you have to wrap part of the code in Table in order for it to work. However after many tries, I came up with this code. However Query is not attached to the URL to finish the URL which looks like: "http://Yoursite.com/data/glovadata/search/csv?StartTimeLocal=31-03-2019%2002%3A12&EndTimeLocal=01-04..."

 

Where the StarteTimeLocal and EndTimeLocal receives new date and time to get newest datas.

This method is supposed to also update in powerbi.com. Yet my code has a problem which it doesnt attach the Query to the URL before it calls the site for data. any suggestion?

 

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

        Source = 
         Csv.Document(
           Web.Contents(
             "http://Yoursite.com/data/glovadata/search/csv?",
             [Query=Terms]
            ),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]
           )

in
    Terms

By running the "in Terms" it is clear that the date dependent urlencoded code generated in Terms is correct and is as it should be.. However, I do not know why it is not merging with the rest of the URL "http://Yoursite.com/data/glovadata/search/csv?", even though the [Query=Terms] is given.

The following Error is displayed when "in Source" is used in the end:

DataSource.Error: Web.Contents failed to retrieve content from 'http://Yoursite.com/data/glovadata/search/csv' (400): Bad Request
Details:
     DataSource Kind = Web
     DataSourcePath = http: //Yoursite.com/data/glovadata/search/csv
     Url = http: //Yoursite.com/data/glovadata/search/csv

Thats because the Terms must be a record and not a table.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF 

 

I have now remade parts of the code so it accepts the URL and can be uploaded to powerbi.com for auto update. However when trying to define "data source credentials" section and defining the Web access method, see picture:

 

If i assign the safety niveau to anything it comes with loading screen like: 

 

and keeps showing loading dots and cant come away from this screen.

 

The Code updates fine in Powerbi desktop.

This code was partially built using:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

 

Please try to read the code in your Powerbi.com and see if you can make it update? Since I seem to not be able to. thanks

The code:

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

SearchSuccessful = (Terms) =>
let
        Source = 
         Csv.Document(Web.Contents("Site.com",
        [RelativePath="search/csv?"&Terms]),
        [Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])
  
in
    Source,
     Output = Table.AddColumn(
       Terms, 
       "Search Successful", 
       each SearchSuccessful([Dato1])
      )
in
    Output

 

Hi @Anonymous ,

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

 

 

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!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@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. 

So have you tried it in a different workspace?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

 

 

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/ 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@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

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@Anonymous , I have edited my answer above.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF 

Thank you for the input. 

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: 
"webside" 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

Not working for me, unfortunately:

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@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

Sorry, but I'm out here. Hope that someone else picks this up (otherwise you might consider opening a new thread)

 

This code isn't working either:

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://xxxxx/search/csv?StartTimeLocal=12-04-2019%2002%3A12&EndTimeLocal=13-04-2019%2002%3A12", 
             [Query=[StartTimeLocal=Date, EndTimeLocal=Date2]]
            )
            
in
    Output

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Greg_Deckler
Super User
Super User

I would place bets on @ImkeF  


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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