Dynamic Web URL and On-Premise Gateway

[jimi_81]

Hi there, 

Hoping the following is possible... for scheduled refreshing, using a on-premise gateway.

The particulars: 

I am tasked with pulling SharePoint version history from SharePoint 2013. I am aware that this done with a Web URL. I have a function setup and believe that I have sorted out the Relative Path issue since the scheduled refresh option is no longer greyed out:

a snippet from the function code:

Source = (id as text) => let

Source = Web.Page(

Web.Contents(

"http://sharepointsite/_layouts/15/versions.aspx ",

[RelativePath = "?list=listname&ID=" & id]

)

)

where listname is just a placeholder/simplification of whatever the sharepoint list id name is...

The IT department has the gateway pointed to http://sharepointsite/_layouts/15/versions.aspx  as the URL. When I am in the settings screen on the dataset, I am warned to " Please make sure your data gateway (personal mode) is installed and configured properly ". I'm not sure why it is shown as personal mode... when it should be enterprise. The query fails instantly when I attempt to refresh.

Can anyone shine a light as to why this is happening, and what can be done to fix it?

Very much appreciated

Dan

[v-chenwuz-msft]

Hi @jimi_81 ,

Did you see the same error on the screen of settings?

If yes, you need add a new data source to the gateway via the hyperlink. It is a good idea to add corresponding data source paths to this gateway cluster for all your possible data sources. The gateway is like a bridge, you need to tell it where the data source is located because you are a dynamic data source. You only need to configure it once and you're done.

Refer:

Add or remove a gateway data source - Power BI | Microsoft Docs

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jimi_81]

Just an update, I have reworked the m code, after reading some documentation on Relative Path and Query:

    Source = (pID as text) => let
            Source = Web.Page(
                Web.Contents(
                    "http://servername/sites/sharepointname/",
                    [
                        RelativePath = "_layouts/15/versions.aspx",
                        Query = [list="listname", ID = pID]
                    ]
                )
            )

I don't have access to diagnotics, but will try and have IT enable so that I can see what Power BI resolves the URL as.

After making the changes, the Power BI service will still not refresh the dataset. I am pretty confident that the m code is fine now.

Not having a setup screen to look at, if the gateway is configured for "http://servername/sites/sharepointname/", what settings or options need to be toggled for a standard mode gateway?

[jimi_81 · Accepted solution]

Refresh issue is solved.

In addition to the m code, IT forgot to set the authentication method to anonymous for the web datasource.

[jimi_81]

Hi v-chenwuz-msft

Thank you for the reply

Below is the screenshot

The 1st item is the SharePoint list, the last item is the URL for the sharepoint version history.  

The SharePoint itself is 2013 and I believe uses AD for user permissions.  I had to put in an permission request for the IT power bi user, to access the web URL.  that has apparently taken place, yet the gateway still will not refresh.  I keep getting the same processing error: "Please make sure your data gateway (personal mode) is installed and configured properly."

Is there any guidance with setting up Web datasources?  The URL I have setup in the m code is essentially the URL for the sharepoint site, i.e. http://servername/sites/.../.../sharepointname/  .  If I were to click on the URL, it would bring me to the SharePoint site.  I am now wondering how the Power BI service wants to see the URL, and if I need to change the URL to be http://servername/sites/.../.../sharepointname/_layouts/15/, and then handle the versions.aspx, GUID and ID in the relativepath, as the function i have setup has ~ 180 unique URLs.

Appreciate the help

Dan

[jimi_81]

Hi vojtechsima, thanks for the reply.

Our IT is out sourced, I've requested a screenshot of the settings.

This is all very new to me... i came across an article yesterday that said the original url needs to work - everything else can be tucked into that RelativePath part.  In my case, I did infact need to modify the URL, its now 

"http://sharepointsite/",

[RelativePath = "_layouts/15/versions.aspx?list=listname&ID=" & id]

With the URL fixed, it still doesn't refresh.

It still shows the same error, re: personal mode.  I am hoping to get a screenshot of the settings, and will report back.

Cheers

[vojtechsima]

Hi, @jimi_81 
Did your IT department create a data source in the Gateway and shared it with you?
Can you share a screen of the Dataset settings, please?