Forum Discussion

gemcityzach's avatar
gemcityzach
Helper IV
2 years ago
Solved

Preserve SharePoint List absolute URL in report

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?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

5 Replies

    • gemcityzach's avatar
      gemcityzach
      Helper IV
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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!