Preserve SharePoint List absolute URL in report

[gemcityzach]

Hey folks,

I'm building a report that brings in various fields from a SharePoint list. One of those fields is the URL to the specific SharePoint list record.

In the list the record is stored correctly: https://sharepoint.company.com/teams/XXXXXX/stuff/record/form.aspx

However, when importing that URL into PowerQuery it converts it to a Relative URL: /teams/XXXXXX/stuff/record/form.aspx

I need the full URL so that I can allow my users to be able to click any record in the PowerBI report and go back to the SharePoint record and form that generated it. Is this possible?

[Anonymous]

Hi @gemcityzach 

We can't affect how data is brought from SharePoint List to PowerBI, but you can transform the data with Power Query. As lbendlin suggested, you can add the base URL part as prefix string to the relative URL column. After that, load the data into Power BI Desktop and set the URL column's data category to Web URL, thus the URLs can be clickable in the report. Here is a blog for your reference: 

Add Prefix and Suffix to a Text in Power BI: Generate Country Images and pages - RADACAD

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[lbendlin]

You can use Power Query to re-inject the missing "https://sharepoint.com" into the string.

[gemcityzach]

This is super helpful. When I use the 'add prefix' it makes for a simple solution but introduces a complexity I'd like to try and resolve.

PowerQuery Wants to Do This

{{"Link_to_record", each "https://sharepoint.companyname.com" & _, type text}}

And it works great except that our URL's are formatted "https://sharepoint.companyname.com/teams/XXXXX" and the relative URL I am getting from SharePoint is "/teams/XXXXX/......".

Not every record will get populated with a value in SharePoint. So, when I do the prefix, it puts a URL "https://sharepoint.companyname.com", which the user will mistakenly click and be sent to an Error 404 page. The same would be true if I tried to add "teams/XXXXX" then while the records with no URL would get a valid URL, then the records that have a URL would get an invalid URL "https://sharepoint.companyname.com/teams/teams/xxxxx/....".

Is there a way to use a custom column approach handling the blank URL fields first? Like:

each if [Link_to_record] = "" then "https://sharepoint.companyname.com/teams/XXXXX" else PREFIX the value correctly

[Anonymous · Accepted solution]

Hi @gemcityzach 

You can use this 

= Table.TransformColumns(#"Changed Type", {{"Link_to_record", each if _="" then "https://sharepoint.companyname.com/teams/XXXXX" else "https://sharepoint.companyname.com" & _, type text}})

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[gemcityzach]

This worked out nicely. Thank you!