cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ercwebdev
Frequent Visitor

URL escaping to get PWA subsites

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 

 

1 ACCEPTED SOLUTION
Sergiy
Resolver I
Resolver I

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

View solution in original post

3 REPLIES 3
dax
Community Support
Community Support

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>

556.PNG

 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
Resolver I
Resolver I

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

View solution in original post

ercwebdev
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors