Forum Discussion

ercwebdev's avatar
ercwebdev
Frequent Visitor
6 years ago
Solved

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 

 

Need Help
  • Sergiy's avatar
    Sergiy
    6 years ago

    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?

3 Replies

  • Sergiy's avatar
    Sergiy
    Resolver II
    6 years ago

    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's avatar
      ercwebdev
      Frequent Visitor
      6 years ago

      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

  • dax's avatar
    dax
    Community Support
    6 years ago

    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.