URL escaping to get PWA subsites

[ercwebdev]

Hi all,

I'm struggling with a quirky little problem in Power BI that is causing a big problem. I came across this tutorial (which uses Power M Query) to access many subsites in SharePoint and returns values from Lists:

https://marque360.com/aggregating-sharepoint-list-data-in-power-bi/ 

However what is happening is in our list of ProjectWorkspaceInternalUrl , some of the URLs have more than one space in them. For example:

https://mysitename.sharepoint.com/sites/pwa/project  -  name

There are 2 spaces either side of the dash.  In Sharepoint, this gets escaped to :

https://mysitename.sharepoint.com/sites/pwa/project%20%20-%20%20name

and this works and shows the subite. However in Power BI, it is replacing all of the white space with a single %20, hence the output looks like this:

https://mysitename.sharepoint.com/sites/pwa/project%20-%20name

In turn, this screws up the sub routine as it returns a 404 as the site cannot be found and the process of getting tyhe data hangs and freezes. 

I've tried 

uri.buildquerystring but this doesn't work (it complains error)

and uri.escapedatastring but escapes everything including '// 'and ':' , and it then complains the url is not valid  i.e. this is how it looks:

https%3A%2F%2Fmysitename.sharepoint.com%2Fsites%2Fpwa%2Fproject%C2%A0%20-%C2%A0%20name

Hopefully I've explained it well enough so that it  makes sense to someone! 

Any help would be most appreciated,

Thanks

Alan 

[dax]

Hi @ercwebdev ， 

You could refer to @Sergiy 'suggestions. In addition , you said that the link don't work in powerbi,  I think this might be related to the path, when you use sharepoint connector, it seems to only recognize path like below 

https://<company>.sharepoint.com/teams/<sub folder>

 You could try to see whether it work or not

https://mysitename.sharepoint.com/sites

Best Regards,
Zoe Zhi

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

[Sergiy · Accepted solution]

@ercwebdev ,

Hi Alan,

Try this code:

let
    Source = "https://mysitename.sharepoint.com/sites/pwa/project  -  name",
    uriPartsRecord = Uri.Parts(Source),
    result = uriPartsRecord[Scheme] & "://" & uriPartsRecord[Host] & uriPartsRecord[Path]
in
    result

the result I get is

https://mysitename.sharepoint.com/sites/pwa/project%20%20-%20%20name

Does it help?

[ercwebdev]

Hi Sergiy,

Thanks for taking the time to provide a solution for this. It works as expected. But still can't seem to get the solution working to will have to investigate other areas.

Thanks,

Alan