Introduction
Power BI is a business analytics service by Microsoft that provides a unified view of critical business data. It allows you to monitor business health using live dashboards, create interactive reports, and access data on the go via mobile apps.
SharePoint is a web-based collaborative platform integrating with Microsoft Office. It serves as a document management and storage system and is highly configurable, with varied uses across organizations.
Using Power BI with SharePoint
Embedding Power BI Reports: You can embed Power BI reports and dashboards in SharePoint Online to give users a comprehensive view of your business metrics without navigating away from SharePoint.
Image Handling: SharePoint can store image files that might be used within Power BI reports. For example, if you have product images in SharePoint, these can be displayed within Power BI reports.
Data Refresh: When Power BI reports are connected to SharePoint data sources, they can be set up to refresh automatically, ensuring that the reports always display the most up-to-date information.
Benefits for Using Power BI with SharePoint
Accessibility: With reports embedded in SharePoint, stakeholders have one central location for all their informational needs, including reports, documents, and collaboration.
Data-Driven Decisions: Power BI provides data visualizations and insights that can drive more informed decision-making.
Collaboration: SharePoint’s collaborative environment means that insights and data can be shared and discussed within the same platform, enhancing team synergy and productivity.
Storing Images in SharePoint
A link to a SharePoint site. URLs like this one are typically used to directly access resources stored on a SharePoint server, such as documents, forms, and images.
Base URL: https://blur.sharepoint.com is the base URL of the SharePoint site. This is the entry point to the SharePoint server and is unique to the organization.
Site Path: /sites/mste_11111/ identifies a specific site or subsite within the SharePoint environment. This path indicates a team site, possibly used for business intelligence purposes given the 'BI' identifier.
Case 1 which only using document library without no subfolder.
Creating a Document Library:
The Document Library is a central feature of SharePoint, used for storing, organizing, and sharing documents.
By selecting Document Library, users can create a new repository for documents, including images, which can be shared with team members or throughout the organization.
The interface for creating a new document library.
After uploading a png file to sharepoint. The result of the sharepoint test1_ımage document.
Case 2 which only using document library WİTH subfolder.
Same steps for case 1, only we have folder inside documents such as Documents -> BI -> Resim_test(created before) and after uploading a png file to sharepoint. The result of the sharepoint Resim_test document.
Connecting Sharepoint via Power BI. Get Date ->Sharepoint Online Lists. Inside Site URL need to write base url + site path.
Implementation 2.0 connector has improved APIs and greater usability, but isn’t backwards compatible with usage of the 1.0 connector version. However, I use 1.0 implementation due to error using 2.0.
For Case 1 Power Query Transform Data (Power Query) Side
= Table.ExpandRecordColumn(#"Removed Other Columns", "File", {"ServerRelativeUrl"}, {"ServerRelativeUrl"})Selecting source that name is what we’ve created before test1_image. ServerRelativeUrl needs to be apperad which that’ll be using. Therefore, inside the file is expanded.
For Case 2 Power Query Transform Data (Power Query) Side
= Table.ExpandRecordColumn(#"Removed Other Columns", "Folder", {"Name", "Files"}, {"Folder.Name", "Folder.Files"})Selecting source that name is what we’ve created before test1_image. Folder is expanded with filter Name and Files.
= Table.ExpandTableColumn(#"Filtered Rows", "Folder.Files", {"ServerRelativeUrl"}, {"Folder.Files.ServerRelativeUrl"})With name will be found document file name and with Files will be expanded and filtered with ServerRelativeURL.
Closed and Apply for Power Query.
Both case for 1 and 2 have ServerRelativeUrl that is the path for PNG.But new column needs to be created for base url.
Case1_ImageUrl =
"https://blur.sharepoint.com/"&table_name[ServerRelativeUrl]
Case2_ImageUrl =
"https://blur.sharepoint.com/"&table_name[Folder.Files.ServerRelativeUrl]Example how to create a new column for case 1 and 2.
Clicking the new column that you created and changing Data Category Web URL or Image URL.
Output when Report is published to Power BI Cloud. When the person clicks with blue marked chrome will be opening a new tab with a picture.
For Case 3 that create with List
In some cases, list is useful method for stakeholders that interactive for PowerApps and it can be used like form style. In this method I’m going to show that how we can use list in SharePoint.
Lists in SharePoint:
Lists can contain items with a set of custom columns that define the item’s data schema, such as names, dates, statuses, and descriptions.
List name is test2_image. Default the output lie the picture. İmage column is created via Add Column and removed Title column.
With +New Button İmage can be added.
Example list output for image list.
Same method to connect SharePoint to Power BI. Thus, PowerQuery side is informed.
İmage is column that is used for Power BI İmage URL or Web URL.
But there is important thing is Image column is Json Format. After Closed & Apply new columns needs to be created.
ımage_link =
VAR jsonString = Sharepoint2_list[Image]
VAR serverUrlStart = (SEARCH("serverUrl",jsonString,1) + LEN("serverUrl"":"""))
VAR serverUrlEnd = (SEARCH(""",""", jsonString, serverUrlStart))
VAR serverUrl = MID(jsonString, serverUrlStart, serverUrlEnd - serverUrlStart)
VAR serverRelativeUrlStart = SEARCH("serverRelativeUrl", jsonString, 1) + LEN("serverRelativeUrl"":""")
VAR serverRelativeUrlEnd = SEARCH(""",""", jsonString, serverRelativeUrlStart)
VAR serverRelativeUrl = MID(jsonString, serverRelativeUrlStart, serverRelativeUrlEnd - serverRelativeUrlStart)
RETURN serverUrl & serverRelativeUrlServerURL + serverRelativeUrl give exactly sharepoint web url. Therefore this is how a new column created. After created Image_link column, data type can be changed Web URL or Image URL.
Output when Report is published to Power BI Cloud. When the person clicks with blue marked chrome will be opening a new tab with a picture.
Super User