Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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?
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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.