Using Images from a Database in Power BI

We've looked at images from sources like OneDrive and the web with your Power BI reports. What about from a database? Is that possible? Patrick takes a look at how you can do it.
SQLJason Blog: sqljason.com/2018/01/embedding-images-in-power-bi-using-base64.html
Gerhard's Blog: blog.gbrueckl.at/2018/01/storing-images-powerbi-analysis-services-data-models/
Chris Webb's Blog: blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- guyinacube
-- awsaxton
-- patrickdba
-- guyinacube
-- guyinacube
-- guyinacube.com
***Gear***
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Images #GuyInACube

[@strongemotion]

This is one of the most important blog posts I've found for Power BI. We EXTENSIVELY use images stored in our SQL database. So many of the other applications we use (VB, C#, JavaScript, CrystalReports) all have native or nearly-native support for images in the database. Your blog post will hopefully make my life easier.

[@ChrisWilliams808]

I'll be adding a link to this video on our website, because it's an important design approach and this is a great teaching presentation. Our company makes a variety of both certified and uncertified custom visuals, and because of Microsoft security policy, certified visuals are not allowed to make external or remote calls to resources like image urls. So if customers want to use certified visuals, images have to come from the database, and this is a good resource to show how.

[@AliImran-rf4lo]

Thank you, Patrick. I needed this desperately. I may be missing something but in my case, it only works when relation is created between my main table and table crated by function.

[@eagillum]

1:16- Converting from Binary to Text via concatenating to Base 64 code (but it only gives you half an image if your character limit is over 32,667) 4:12- Getting the whole image (splitting the characters into rows)

[@TanjilulA]

For anyone attempting this, here is the code of the function: //Start of code let //Get list of files in folder picresult = (InputTable as table, InputBinaryZBPosition as number, InputKeyNameZBPosition as number) as table => let //Get list of images from Database Source = (InputTable), //Converts table that contains images to list ListToInput = Table.ToRows(Source), //Creates Splitter function SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000), //THE MAGIC!!!! Converts BINARY TO TEXT and creates and splits image into 30K rows ConvertOneFile = (InputRow as list) => let BinaryIn = InputRow{InputBinaryZBPosition}, RegionName = InputRow{InputKeyNameZBPosition}, //CountryKey = InputRow{InputKeyZBPosition}, BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64), SplitUpText = SplitTextFunction(BinaryText), //AddFileName = List.Transform(SplitUpText, each {RegionName,CountryKey,_}) AddFileName = List.Transform(SplitUpText, each {RegionName,_}) in AddFileName, //Loops over all photos and calls the above function ConvertAllFiles = List.Transform(ListToInput, each ConvertOneFile(_)), //Combines lists together CombineLists = List.Combine(ConvertAllFiles), //Converts results to table ToTable = #table(type table[Name=text,Pic=text],CombineLists), //Adds index column to output table AddIndexColumn = Table.AddIndexColumn(ToTable, "Index", 0, 1) in AddIndexColumn in picresult //End of Code

[@SpaceDino6928]

Thanks for this! This comment should be pinned!

[@afonso0078]

I appreciate your attitude, thank you very much. :)

[@strongemotion]

LOL, I just re-typed it from scratch.

[@MikeHogg-u8j]

@@strongemotion Good thing you did, bro missed an input in their code

[@huqsami]

Great! Btw, Any idea how to import images from local folder? Excel power view seems to do it quite easily so why can’t powerbi?

[@subanark]

Nifty, super powerful, but maybe we could get an easier way of doing this in the future? Just a note for people: If your importing SVGs, don't base64 encode them, it won't work. They work fine as plain SVGs.

[@AndrewMcDonald123]

Just what I needed, Thank you Patrick!

[@MikeHogg-u8j]

I got it working! What a mission. Seems simple after you figure it out! Also damn datatypes confused the shit out of me at first. Good luck everyone!

[@Funcion_de_seguridad]

Thank you Patrick! good explanation. Regards from Peru

[@narasimhapandit5148]

@guyinacube Hi @patrick I am only able to do this for image with smaller size when the size of the image changes it gives error saying maximum allowable length exceeded.

[@abubakaragtamin9676]

That was impressive Patrick!! This is what I am looking for. Thanks bro

[@mitchellcauthron361]

I get no partial or full image when creating DB Image column and setting Data category to Image URL. I am trying to look at pdf documents, not jpeg so swapted the "image/jpeg" to "image/pdf" but still get no image. Any suggestions?

[@DrMk64]

Hi Patrick/Mitchell, I'm getting the same problem trying to view pdfs. Any ideas?

[@z.hpungaungli6913]

Thanks a lot for your presentation and for sharing that I've been searching for a long time. 😇

[@halliegromek1838]

Hi Patrick! This is exactly what I am looking for, do you have the getTheEntireImage code posted anywhere?

[@sharmacreatives255]

Could we add music to any page...so that while accessing or viewing could be with some professional background music

[@faridfarid7070]

this is if the stored data is the URL to the Image ! what if we have a the image data stored in the DB ?? is there any possibility to show on power bi ??

[@barryA669]

How can i show the pictures in large scale and not in thumbling size? i need to display one large picture on a page

[@karir7946]

Great presentation. What software are you using to create?

[@sharmacreatives255]

Could we have animated background in mobile layout in power bi...and when viewed in mobile could the animated background work...pls let me know sir

[@vikashmishra8528]

It works for JPEG file but not with BMP file any suggestion.

[@MJ-cw3ox]

Hello Patrick, thanks for sharing the tech. One question, where can we get the function getTheEntireImage code ? Thanks.

[@MJ-cw3ox]

I got it now. Extracted from Image :) =-=-=-===============-========= getTheEntireImage function =-=-=========================== let //Input parameters provided to invoke function picresult = (InputTable as table, InputBinaryZBPosition as number, InputkeyZBPosition as number) as table => let // Get list of images from Database Source = (InputTable), // Converts table that contains images to list ListToInput = Table.ToRows(Source), //Creates splitter function SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000), //THE MAGIC!!!! Converts Binary to text and creates and splits image into 30K rows ConvertOneFile = (InputRow as list) => let BinaryIn = InputRow{InputBinaryZBPosition}, // RegionName = InputRow{InputKeyNameZBPosition}, Countrykey = InputRow{InputkeyZBPosition}, BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64), SplitUpText = SplitTextFunction(BinaryText), AddFileName = List.Transform(SplitUpText, each {Countrykey,_}) in AddFileName, // Loops over all photos and calls the above function ConvertsAllRows = List.Transform(ListToInput, each ConvertOneFile(_)), ///Combines lists together CombineLists = List.Combine (ConvertsAllRows), //Converts results to table ToTable = #table(type table[CountryKey = number, Pic=text], CombineLists), //Adds index column to output table AddIndexColumn = Table.AddIndexColumn(ToTable, "Index", 0, 1) in AddIndexColumn in picresult

[@halliegromek1838]

hi, did you find out where this is?

[@wasio44]

Hi Patrick, many thanks for that video! I wanted to know : is it possible to have a clickable image ? in my dataset, i have column for image URL, and also for website URL. by playing with slices, i want to show an image and be able to directly click on it to go the website. Any idea? for the moment i've set a table with an image and the link icon close to it, but it is not really instinctive for the final users . thanks!!

[@michellejrgensen2897]

I am Getting an error that #table is invalid constant value what is the realtion to this #table ?? it the #tabel a parameter

[@JorgeOrtizG]

Great video as always. What happen when I have just one image (company logo for example) and I don't need or want to use a table to display it?

[@ChrisWilliams808]

You can try something like Image by CloudScope (search AppSource). My company makes it, full disclosure.

[@GIO200011]

You can just import the image from the insert menu.

[@nathanphillips3628]

Depending on the use case, an image can be downscaled and base64 encoded as a varchar in the DB. Let's say you had 10 client logos to be controlled using RLS, and you wanted them to display in the top right of a page. They don't need to be massive, and can fit within that character limit. If pulled in as base64 from the DB, then one only needs to change the data type to image url. Boom! Done! If the images must be large, this of course will not work. Not as scalable as the solution unpacked here, but simpler maybe.

[@loraynne18071]

In Power BI Report Builder?

[@AutodafeSynths]

great solution!!!

[@mrkakashi_1129]

let //Get list of files in folder Source = (InputTable as table, inputbinaryzbposition as number, inputkeynamezbposition as number, inputkeyzbposition as number) as table => let Source = (InputTable), ListToInput = Table.ToRows(Source), //Creates Splitter function SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000), //Converts table of files to list //Function to convert binary of photo to multiple //text values ConvertOneFile = (InputRow as list) => let BinaryIn = InputRow{inputbinaryzbposition}, ProfileName = InputRow{inputkeynamezbposition}, CountryKey = InputRow{inputkeyzbposition}, BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64), SplitUpText = SplitTextFunction(BinaryText), AddFileName = List.Transform(SplitUpText, each {ProfileName,_}) in AddFileName, //Loops over all photos and calls the above function ConvertAllRows = List.Transform(ListToInput, each ConvertOneFile(_)), //Combines lists together CombineLists = List.Combine(ConvertAllRows), //Converts results to table ToTable = #table(type table[ProfileName=text, CountryKey = number],CombineLists), //Adds index column to output table AddIndexColumn = Table.AddIndexColumn(ToTable, "Index", 0, 1) in AddIndexColumn in Source

[@penchalapratappalukuri7070]

Hi Guys,can anybody tell me ,how to create a custom button,when user clicks this button it will export the report to excel.Thanks in advance.

[@sdeekshita3344]

Hi, Did you find the solution, I have similar requirement but should export to PDF.

[@holahandstrom]

Good, but frankly - it should be much easier to Store and Display Images, for the average user.

[@shofyansky]

Great video dude

[@Babayagaom]

I have similar requirement in paginated reports

[@solidoffortitude]

Yeah. It's a fun solution, but I gave up on this functionality because it puts unnecessary strain on the platform. Just like I can use awesome animated GIFs on buttons, but they blow up my PBIX exponentially.

[@sdeekshita3344]

What alternative did you use

[@notoriousft]

Interesting, such a basic functionality requires this amount of tinkering.

[@djokotriono7787]

makes me think ms doesn't exactly want people to use pbi

[@junaidmalik9593]

Great video , but too much for me to digest 😛