Month: November 2021

Show “secured” Images in Power BI

Power BI Guy8 Comments

Use Case

From time to time I got asked how you can display images in Power BI. The answer is pretty simple and straight forward (see https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-images-tables) if the image is publicly available. But customers can’t expose their internal pictures to the public and still want to display them in Power BI. The issue is with “secured” images that the Power BI visual can’t log in with an account to be able to display it. So how can you still save your images in a secured environment and still display them in Power BI? Let’s check it out.

I tried different ways and found two that work. One would be to get the binaries of each image, transform it to a text field and display afterwards with a custom visual the image. This solution has a big “but” from my point of view. Because the text field has a 32766 character limit you would either have to resize your image or you have to do some Power Query and DAX magic to split the field into multiple ones and add them afterwards together again. Chris Webb wrote a blog how this could be done here: https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/

As good as this solution might be I would like to find an easy, low-code / no-code way to achieve the same. And luckily there is one!

Power Apps & Power BI – Better Together

Because Power BI is part of the Power Platform and Microsoft offers a seamless integration into the other Services (and vice versa) let’s leverage the benefits of it and try to display our images through a Power Apps app in Power BI.

Prerequisites

To be able to implement the solution you would need a few things:

  • Power BI Desktop
  • Power BI Service Account (optionally)
  • Power Apps Account
  • SharePoint Document Library with pictures

The Power BI Desktop can be downloaded for free through the Power BI website. Power Apps is included in different licensing options like E3 or E5. In our case we’re going to use a SharePoint Connector which is a Standard Power Apps connector meaning no additional licensing is needed. If you don’t have a Power Apps license yet you can sign up for a Developer Plan and use to for development purpose. Keep in mind that you can’t share your apps created with a Dev. Plan nor can you show Power Apps apps in Power BI coming from this specific environment.

How to

We start in Power BI Desktop and connect to our SharePoint list to get a few basic details. I’ll not guide you through how to connect to a SharePoint Document Library. These details can be found here. In my case I have a simple Document Library with some Star Wars picture.

In Power BI I connect to the Document Library and get only the ID, Notes, Created, Modified, and ServerRelativeURL fields. You can of course select more or less fields but make sure you get the ID which will be needed later on in the Power Apps app.

Once loaded I create a simple Table visual with the three fields ID, Notes, and ServerRelativeURL to display some data. As a next step I add the Power Apps Visual to my canvas and position it to the right. Once you added the Power Apps Visual you’ll see the necessary steps how to get started.

So let’s do our first step and add our wished fields to the data section of the visual. This fields can be accessed later on through Power Apps. Make sure to add the Id field and also check that it doesn’t summarize! The behavior (Sum, Count, etc.) will be provided to Power Apps and we don’t want to summarize our Ids. As soon as you add your first field the Power Apps visual will change where you can choose and existing app or create a new one. In our case we’re going to create a new one. A window will pop up asking if it’s ok to open your browser with the Power Apps URL. Hit OK.

Tip: If you’re facing some issues while opening Power Apps or you can’t choose your environment through the Visual, open Power Apps in your default browser, choose your environment in the browser, switch back to Power BI and try to hit the “Create New” button again.

Once Power Apps is loaded you’ll see an object call “PowerBIIntegration” (besides some others). This object has been automatically created through Power BI and makes sure that Power BI and Power Apps can interact with each other. This means for example if you select now a specific image to filter the same filter will also apply in Power Apps. That’s the reason why you have to start from Power BI and create an App from there. Otherwise the “PowerBIIntegration” object will not be created.

Let’s quickly test if the integration really works. I select the Baby Yoda picture in Power BI and the list is automatically filtered in Power Apps – great!

Our next goal is now to show the Images from SharePoint in our Power Apps app and make sure the integration still works (filter on a specific image in Power BI should also filter the Image in our app). Therefore we first have to create a connection to our SharePoint list. To do so go to the Data Tab in Power Apps and add SharePoint as data source.

If you haven’t created a connection yet hit the “Add a connection” button. In my case I can choose an existing one.

Afterwards choose your Site and Document Library in which you stored your pictures. In my case I have a Doc Library called PBI Guy Pictures. I select it and hit “Connect”.

Now that we’re connected to our Doc Library we can display the images out of it. To do so insert a Gallery. I choose a vertical one but the layout can be modified afterwards as well.

Once inserted I adjust the two Galleries so that our freshly inserted one is at the top and at the bottom I display the first Gallery. Per default our new Gallery shows default text and images. We have to connect our Gallery to our SharePoint Doc Library and than decide what we wish to display. So let’s connect it by selecting the Gallery and set the Data source through the Properties pane.

Our next step is to display the right image. Select the first image object in the Gallery and choose the “Items” property either on top left of the screen or find it in the properties pane in the Advanced section.

Replace “SampleImage” with ThisItem.’Link to item’ to create the link to our needed image.

You can also modify the view of the Gallery, add new fields into it, etc. but in my case I just want to display my notes with the ID together. Therefore I select the ID Text Box and change the code in the function window from ThisItem.ID to ThisItem.ID & ” ” & ThisItem.Notes

Our last step is to create a connection between our SharePoint Gallery and the “Power BI Gallery” from the beginning so that filters from Power BI will effect our SharePoint Gallery as well. The best way to do so is to filter the new Gallery by an ID to make sure we got that one specific image we’re looking for. That’s the reason why we need our ID field from the beginning! 🙂

To filter the new Gallery select it and choose the Items property. In my case I see ‘PBI Guy Pictures’. This is the whole table we have to filter and making sure that only the selected ID is showing up. Therefore we wrap our Table with a Filter() statement. Our first argument in the filter statement is the ‘PBI Guy Pictures’ table. Our second argument is the filter condition which should point the ID field from Power BI to the ID field of SharePoint. Therefore we use the PowerBIIntegration object to grab the necessary data. Unfortunately this is a Table Data Type and we can’t match Table with a single Number Data Type. To get a single value I extract just the first value of the whole table with the First() statement followed with the column I’m looking for (ID in this case). Our whole function looks now as following: Filter(‘PBI Guy Pictures’, First([@PowerBIIntegration].Data).Id = ID) And I immediately see that it works! Previously I selected Baby Yoda and now only this picture is showing up.

As a last step I want to make sure the first Gallery is not visible in my app. So I select the first Gallery and set the Visible property to false.

Of course you can now modify your app and make it more shiny but just for the demo purpose I save it as it is now through File, Save. Once done the App is ready to be used in Power BI as well and the cross-filter works!

If you wish to share the report make sure all users have also access to the Power Apps app and the SharePoint Library. Otherwise people won’t be able to see the pictures in their report.

Please let me know if this post was helpful and give me some feedback. Also feel free to contact me if you have any questions.

If you’re interested in the files used in this blog check out my GitHub repo https://github.com/PBI-Guy/blog

Power BI Theme with Background Image

Power BI Guy3 Comments

A few weeks ago I saw a small but very useful hack if you’re working with Power BI Themes. Did you know that you can create a Theme with a background image? Let me show you how!

What is a Theme in Power BI?

Power BI Report Themes helps you to apply design changes to your entire report. With a Theme you can specify what the default color of your visuals should be, changing icon sets, or applying default visual formatting like title text size. Further details can be found here: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-report-themes

Once a Theme is created you can save it as JSON file and distributed it to your whole organization. Like with every other JSON file you can of course modify it as wished. In our case we wish to add a background image because background images are not saved through the Desktop in the JSON file.

How to create a Theme

Once Power BI Desktop is opened select the View Tab, expand Themes, and hit Customize current theme.

A new window will pop up in which you can customize your current Theme. In my case I choose to go with darker colors in general. You can also customize your Text, Visuals, Page, and Filter pane. Once done hit the Apply button.

You’ll see now that the current Theme is used in your Power BI report. Last step is to save the current Theme as JSON.

If you wish you can try to add a background image in your report but this will not be saved in the Theme / JSON file.

Once saved I open the JSON file in Notepad++ and format it as JSON format through the JSTool plugin. As we can see the data colors has been saved in our JSON file (and nothing else).

To be able to add now a background image we need to add a code snippet and convert our background image to base64 format. Let me walk you through the steps. First, we add following code snippet after the dataColors line.

,”visualStyles”: {“page”: {“*”: {“background”: [{“image”: {“name”: “Demo”,”scaling”: “Fit”,”url”: “data:image/jpg;base64, ” },”transparency”: 50}]}}}

Now we can add our base64 formatted image after the comma of base64 but it has to be before the quote. To convert an image into base64 just search for an online service which can do so. In my case I’m using https://onlinejpgtools.com/convert-jpg-to-base64 and simply drag my image in the necessary field. Once done you’ll get the code right away on the right sight. Hit the “Copy to clipboard” button to have it in your clipboard.

The last step now is to paste the code in the needed code area (after the comma of base64, before the quote). I formatted my final code in Notepad++ to make it more readable and it looks like following now.

If your image is a PNG just replace the red marked part of your code to be PNG instead of JPG.

Finally I have to save my JSON file and import it into every Power BI report file I wish to use my new Theme.

As you can see the background image is saved within the Theme with the correct settings. In my case I set the transparency to 50%.

Please let me know if this post was helpful and give me some feedback. Also feel free to contact me if you have any questions.

If you’re interested in the files used in this blog check out my GitHub repo https://github.com/PBI-Guy/blog