Automatically And Dynamically Change Web Data Source Address

[CristianMagna]

Hello.

I'm sort of really new to Power BI, so if my need here is achievable, please give me a step-by-step instructions.

I have a source that comes from a web link (URL) that changes daily based on the current day accessed. The only thing that I really need to be updated in that URL is the date, which in my case would be always the current date in the specific format mm/dd/yyyy inside that link address.

So, as an example the address would be something like: http://www.contoso.com/ReportServer?/SuiteURL=https://www.contoso.com/SPS&UserName=Contoso\john.doe&UserID=983&UserPermission=DSD&Type=036&DateFrom=01/01/2010 00:00:00&DateTo=09/23/2022 00:00:00&rs:ParameterLanguage=&rs:Command=Render&rs:Format=CSV

This is the URL I got when accessing on the September, 23rd of 2022.

I need somehow for my Power BI report to be able to automatically have the "DateTo=09/23/2022" part in that URL changed to "DateTo=09/30/2022" if I access it on the September, 30th of 2022, for example, so that I always get the most up to date information.

The old URL still works, but it will only show me data up until the September, 23rd of 2022 and I need to always have the most recent date information.

Is there a way for me to get this specific part changed automatically based on the day I access the file without having to manually input my current date in any way?

I mean, a way that when I or anyone who opens the file will automatically get the most recent data without the need to manually say what day is today?

Note: The URL provided as an example is a fake and doesn't work. 😅

Thanks in advance for your time.

Cristian

[CristianMagna]

Thanks a lot for your reply @lbendlin! I really appreciate it.

I'm not sure if I understood correctly how to use this RelativePath, but from what I want to achieve, I've come up with the following code:

TodayDay = DateTime.Date( DateTime.LocalNow () ),
Source = Csv.Document(
    Web.Contents(
        "http://www.contoso.com/ReportServer?/SuiteURL=https://www.contoso.com/SPS&UserName=Contoso\john.doe&UserID=983&UserPermission=DSD&Type=036&DateFrom=01/01/2010 00:00:00&DateTo=",
        [
            RelativePath={TodayDate, " 00:00:00&rs:ParameterLanguage=&rs:Command=Render&rs:Format=CSV"
            }
        ]
    )
)

It says to me there are no syntax errors:

But when I click the "Done" button in the "Advanced Editor" I get the following error message:

If I click the "Go To Error" button, it then goes to:

When clicking the "Edit Settings" it opens the connection window:

It is filled with just the first part of the URL and haven't found what I should do to correct this.

Can you help me with it?

Thanks,

Cristian

[lbendlin]

Something along these lines. You don't actually have a relative path, you only have query parameters.

TodayDay = DateTime.Date( DateTime.LocalNow () ),
Source = Web.Contents(
"http://www.contoso.com/ReportServer",[Query=[
SuiteURL="https://www.contoso.com/SPS"
,UserName="Contoso\john.doe"
,UserID="983",
,UserPermission="DSD"
,Type="036"
,DateFrom="01/01/2010 00:00:00"
,DateTo="""" & TodayDay & """"
,Format="CSV"
 ]]  
)

[CristianMagna]

Thanks @lbendlin. You are helping me a lot. 👍🏻😊

After going through a lot of editing, tries and errors, I found that my example URL that I provided might have missed some important parts, so let me give you a more complete example of what I have as URL:

http://domain1.local/ReportServer?/M42/ServiceDesk/MIT - ServiceDesk_All_Activities&UserTimeZoneId=Central Europe Standard Time&SuiteURL=https://domain2.local/SPS&UserName=domain\username&UserID=101d1a25-16d2-ec11-80d3-0050568f1215&UserPermission=DSD&Type=025120f2-7e44-4e0b-8572-f53e76d10826&Type=fe098714-ac94-47f1-9724-df5bac86b3fb&Type=ae98d261-1761-4cdb-8675-9c1947799ad1&Type=173add94-1ff9-e611-e182-60e327035d31&Type=2bd82345-74d9-4fa5-9159-9fd506343cc7&Type=13f950a8-f2f8-e611-e182-60e327035d31&Hyperion=9a0832f8-39c8-e211-ef93-005056956d7a&DateFrom=01/01/2010 00:00:00&DateTo=09/23/2022 00:00:00&RoleGroup=&ResponsibleRole=&Responsible=&Category=&Status=0&Status=1&AffectedCI=&Impact=1&Impact=2&Impact=3&Impact=20000&Urgency=3&Urgency=2&Urgency=1&Urgency=20000&TimeZone=Greenland Standard Time&rs:ParameterLanguage=&rs:Command=Render&rs:Format=CSV

Based on what you provided me and with some Google search I constructed this coding:

let
    TodayDate = DateTime.Date(
		DateTime.LocalNow()
		),
	TodayDatetoText = Date.ToText(TodayDate, [Format="mm/dd/yyyy HH:mm:ss"]),
	Source = Csv.Document(
        Web.Contents(
            "http://domain1.local/ReportServer",
		Query=[
		M42/ServiceDesk/MIT - ServiceDesk_All_Activities,
		UserTimeZoneId="Central Europe Standard Time",
		SuiteURL="https://domain2.local/SPS",
		UserName="domain\username",
		UserID="101d1a25-16d2-ec11-80d3-0050568f1215",
		UserPermission="DSD",
		Type={
			"025120f2-7e44-4e0b-8572-f53e76d10826",
			"fe098714-ac94-47f1-9724-df5bac86b3fb",
			"ae98d261-1761-4cdb-8675-9c1947799ad1",
			"173add94-1ff9-e611-e182-60e327035d31",
			"2bd82345-74d9-4fa5-9159-9fd506343cc7",
			"13f950a8-f2f8-e611-e182-60e327035d31"
		},
		Hyperion="9a0832f8-39c8-e211-ef93-005056956d7a",
		DateFrom="01/01/2010 00:00:00",
		DateTo="""" & TodayDatetoText & """",
		RoleGroup="",
		ResponsibleRole="",
		Responsible="",
		Category="",
		Status={
			"0",
			"1"
		},
		AffectedCI="",
		Impact={
			"1",
			"2",
			"3",
			"20000"
		},
		Urgency={
			"3",
			"2",
			"1",
			"20000"
		},
		TimeZone="Greenland Standard Time",
		ParameterLanguage="",
		Command="Render",
		Format="CSV"
            ]
        )
    )
in
    Source

I'm getting the following error message:

And if I click the "Show error" option, it points to the M42 from the line 

M42/ServiceDesk/MIT - ServiceDesk_All_Activities,

If I remove the M42 it jumps to ServiceDesk and so on.

If I remove the whole line, no errors are found, but the URL doesn't work.

I've searched through Google about this, but I always end up with a URL that has the ? followed by an item=value. Haven't found anything that has an extension of a URL followed by an & and then an item=value.

I also tried to play with RelativePath, but if that is the case and I should use it here, I might be putting it in the wrong place or using it the wrong way. 

Another thing that I don't know if will work is for the rs: at the end of the link. I had to remove these rs: so that Power Query wouldn't complain about syntax error. I'm just hoping this is not an important part of the URL as I couldn't find what it is intended for.

I'd also like to ask if there is a specific reason for you to have removed the Csv.Document at the beginning of Source. If yes, can you tell me why?

Sorry for not providing a more complete URL and if this is taking too much of your time.

Thanks once again for all your help.

Cristian

[lbendlin]

M42/ServiceDesk/MIT - ServiceDesk_All_Activities   needs to equate to something.  (the = sign is missing)

This URL

http://domain1.local/ReportServer?/M42/ServiceDesk/

is highly questionable.  You don't normally see a slash after the query start.  Please check again.

[CristianMagna]

I did double checked and triple checked. 😓

The last URL is the closest thing to the real one I have with the exception of the domains and username. The rest is pretty much as it is in the real URL.

I know what you mean by needing the "=" part, as this is the only thing I've found on my searches on the web, but the URL works formatted that way that I sent you. 😓

I thought that this might be related to some folder structure, so I tried a few things.

I tried access the URL http://domain1.local/ReportServer and got this:

If I click the M42 folder link, I get the following URL in return in my browser:

http://domain1.local/ReportServer?%2fM42&rs:Command=ListChildren

The %2f part in the URL I get is the representation for the /.

And this goes on:

http://domain1.local/ReportServer?%2fM42%2fServiceDesk&rs:Command=ListChildren

But I still don't understand how and why this works like this nor how to set this part of the URL in the Query part.

I noticed now that this URL points to a Microsoft SQL Report, but I don't know if it is related or not to it. 🤷🏻‍

Also don't know what the rs: part means. I haven't found what it is for on my searches.

I'd appreciate if you could help me with this.

Thanks,

Cristian

[CristianMagna]

Hi @lbendlin!

I've searched a little more on the web today, now looking for something related to Microsoft SQL Server Reporting Services, now that I saw that the link was related to it, to see if I could find anything helpful.

I was able to find this link (https://learn.microsoft.com/en-us/sql/reporting-services/url-access-parameter-reference?view=sql-ser...) and it talks about the "rs:" part from the URL I provided in the example, which is a report server command for the SQL Server Reporting Services.

Another thing I noticed in this article is that the URLs generated for this reporting have the "?" followed by a "/" which although is uncommon for most URLs, it seems to be common for the SQL Server Reporting Services.

The only thing that I'm still unable to find is how to edit the whole URL to allow me to automatically change the date part as I need.

I don't have access to the SQL server nor to the ones responsible for it. I only have access to that link as I've showed and I'm able to extract the data from it. I just need to be able to connect to it in a way so that I'm able to change that "DateTo" part to include the current date without the need to somehow manually input it.

With that link I provided, do you think you can help me?

Thanks,

Cristian

[lbendlin]

Think about it more from a philosophical perspective. You are trying to access one reporting tool (RS) from another reporting tool (Power BI).  Wouldn't it be better if you connected to the underlying data source, or utilized the existing RDL instead?

[CristianMagna]

Hmmm... I know what you mean. But the problem is that this link is the only source I'm able to get. 

I'm not able to connect directly to the data source where that link gets the data from.

It is an extremely restrict access and this is the only way I'm able to get the data.

If the data were more friendly like when viewing in Power BI, I'd be glad to use it as it is, but my manager liked the way it is shown in Power BI with the template I created. The only issue is that the data source needs to be manually edited to get the most recent changes as the source link is fixing the from and to dates.

[lbendlin]

Print this one out and memorize it (not kidding).  You will need it a lot in your Power BI journey.

Web.Contents - PowerQuery M | Microsoft Learn

Look at the RelativePath and Query sections specifically.