Are you looking to create a link to an item in Power BI after connecting it to a SharePoint list? While Power BI doesn’t provide a pre-built “Link to Item” column as Power Automate does, you can still create one yourself. In this article, we will guide you through the process of generating a link manually in Power BI, using Power Query to extract the necessary data from SharePoint and concatenate it to create the link. By the end of this tutorial, you’ll have a dynamic link that automatically generates for each new item added to your list.
To begin, let’s explore how the link is constructed in Power Automate. Understanding this will help us create a similar link in Power BI. Then, we’ll dive into the step-by-step process of generating custom links. By following along, you’ll be able to enhance your Power BI and SharePoint integration seamlessly.
- First, let’s examine the link structure in Power Automate:
- [URL to SharePoint list]/[item ID]
- Now, let’s move on to creating the link manually in Power BI:
- Connect Power BI to your SharePoint list using Power Query.
- Retrieve the necessary data from SharePoint, including the URL and item ID.
- Use Power Query’s transformation capabilities to manipulate the data.
- Concatenate the URL and item ID to generate the link for each item.
- Here are the detailed steps to follow in Power Query:
- Open Power BI and go to the “Home” tab.
- Click on “Get Data” and choose “SharePoint Online List.”
- Provide the SharePoint site URL and select the desired list.
- In the Power Query Editor, select the columns containing the URL and item ID.
- Right-click on the column header and choose “Duplicate Column” to create a copy.
- Rename the duplicated column as “Link to Item.”
- Select the “Link to Item” column and click on the “Transform” tab.
- Choose “Text Column” from the “Data Type” section.
- Click on “Add Column” and select “Custom Column.”
- In the custom column dialog, enter the formula:
=[URL] & "/" & Text.From([item ID])
- Click “OK” to create the custom column.
- Finally, click on “Close & Apply” to load the data into Power BI.
- Once you’ve completed these steps, you’ll have a new column called “Link to Item” in your Power BI dataset. This column will automatically generate the corresponding link for each item in your SharePoint list.
By following this tutorial, you’ll be able to create dynamic links in Power BI that connect directly to items in your SharePoint list. This can be a valuable addition to your reporting and analysis, enabling easy access to the underlying data in SharePoint. Enjoy exploring the enhanced integration between Power BI and SharePoint!