Power Bi : Use Local Images in Power Bi. 3 Different Methods

hey guys this is victor with dapper dash and today the tutorial that i want to share with you is actually something that i've had to learn how to you do recently the topic that we're going to be covering today is how to use local images from your computer and combine them with some sort of data table so that you can use them in power bi for either display or for slicing or for filtering let me explain what i mean by that so traditionally when we i've worked with images in power bi or in my tables i usually use some sort of web url right so i find a logo that i like i usually copy the you can copy the image url and what happens is you usually end up pasting it somewhere like this so here here i have different nba teams and here i have their logos right so if i go to this link here it'll take me to the denver nuggets logo and so that's fine and dandy when you're referencing images that are on the web but what happens if i have local images so if these images are saved on my desktop and they're not on the web anywhere maybe it's custom icons that i've created or pictures of my home how can i insert those into a table so that i can use as maybe a visual for a slicer or i can use as some sort of image on my graph so we're going to learn how to do this using three different methods so method number one is going to be my simple method and that's method number one is convenient when you're working with a small number of files so maybe you only have four images that you want to load and this works best usually when the images are small usually they're icons size images so let me show you how method number one works if you run into any errors don't worry chances are we'll either resolve the errors with method number two or method number three if it works with you for method number one great if you run into problems stay tuned for method number two and method number three and we'll see if we can help resolve those i wanted to give a shout out to gerard brookel on bi and data this blog here which is also in the description is actually where i went to get the base for how to accomplish this and so he has a blog titled storing images and a power bi analysis services data model which is what we're going to be doing today now he doesn't exactly walk through all the three different methods but this is where i took the base for what i'm going to be explaining to you today so definitely go check out this particular blog and follow along in case you get lost in anything that i say but let's go check out method number one first okay so for the simple method that i'm going to show you what i have here is a graph of different crypto coins the high and low market value for the day so you can see that as i switch through the coins right i'm looking at bitcoins market high and low value ethereum litecoin and doge and what i want is as i slice through these filters i want to be able to use these local images that i have here and display them on some sort of picture i've downloaded this simple image view from the marketplace and i'm gonna add it to my workbook so we're gonna bring it in here and what i want is if i go to my data set i have this picture column but it's blank right now if i were to bring this in right there's nothing but as i scroll through these i want this to become one of the icons that i have so here's my data set right the images are blank and we're going to go into google all you're going to do is google image to base64 or follow the link in the description and i'm going to use this website here this base64-image.de what this is going to do is it's going to convert my images into this base64 format that you see here and power bi is able to recognize this as the image that i'm wanting to use now remember i'm working with small images right 256 by 256 pixels and the final image that comes out is only 2.76 kilobytes right so this simple method works best when you're working with small number of files small images typically you're working with icons so what we're going to do is we're going to copy the image right now that has been converted like this we're going to copy it so in this case we're copying ethereum we're going to paste it into our picture column and we're going to go back copy litecoin paste it here and copy bitcoin paste it into the bitcoin and the last one we're going to do is we're going to copy doge and we're going to paste it right there then i'm going to hit save we're going to go back into power bi all right so i'll show you where that is so here's our crypto right we have nothing in the picture we're going to refresh that and now i have the images that i've copied over and keep in mind the other thing you want to do is because these are uncategorized you want to set the image to an image url okay now if we go back there it is so there's bitcoin right if i switch over to ethereum i now have ethereum litecoin and doge and so this is the simple method once again this works best when you're working with a small number of files and the reason i say that is because if you have a hundred images the last thing you want to be doing is going through here and copying a hundred times copy paste copy paste copy paste all the way down you know however many rows just to get it to work like this hope you enjoyed that one we're gonna move into method number two so that was method number one and that one's the most simplest one pretty straightforward where we run into method number two is what if i don't just have a small number of files what if i'm not working with just four or five uh images what if i'm working with 100 images or 30 images or in the example that i gave of the nba teams i need 30 or 32 logos from a football you know league or mba league when you run into something like that it's not quite simple to just you know go to the website drop the images and then start copying and pasting one by one so that's where i'm gonna go into method number two which is how to have power bi automatically convert those images into a base64 format so that you can use it and you don't have to do any of the manipulating yourself the only thing you have to do is set up the function i'll explain and we'll go into method number two so here we are for method number two so for method number two i'm going to be working with nba data so what you'll see here is i have the spreadsheet that has the mba teams it has their rank in terms of points per game for the 2019 season and what i want to do is i want to be able to bring my icons right link them up to my data set where this is different for method number one is the last thing i want to do right now is grab 30 images copy them over right into our base64 converter and then start copying and pasting one by one and finding the right team and pasting it finding the right team and pasting it so that's the time consuming part if you only work with like five or six images then method number one it's perfect it's not going to take you more than a minute to bring these in here and copy them over and you have them right in your display and they're available but for method number two when we're working with a large number of files what we're going to do is we're going to go into transform data and you're going to right click on the query you're going to create a new query it's going to be a blank query you're going to go to advanced editor and you're going to paste this code into the advanced editor now what this code's going to do is we're going to look at the folder where our images are at right it's going to bring this in as a binary column and then we're going to turn that binary column and we're going to convert it to base 64. essentially doing the same thing that this website does but we're going to be doing it within power bi now one potential place where your code may differ than mine is if you look at my images here my images are png files the code that i pasted here coming from the blog you know they convert their images from jpeg i'm going to change mine to png because those are the images that i'm working with if you're working with jpeg images you can leave it as jpeg and that looks good i'm gonna hit done now this code if it's too small to see it is in the description so go down into the description under method number two custom function code and just copy it and paste it so i'm gonna hit done i'm gonna rename this to png image converter now i have my function so the next thing that i'm going to do is i'm going to import my images so i'm going to go to new source you're going to go to more we're going to go to the folder option so here we have folder we're going to hit connect you're going to browse to the folder where you have your images that you're wanting to you know convert in bulk so mine is the mba images i'm going to hit ok hit ok now when you get to this screen the next thing you want to do is so here's our binary data that's that we're going to convert into our base64 so we're going to hit transform data and then you're going to go to add column and you're going to invoke custom function we're going to use the custom png function that we just created or jpeg if that's what you're looking at so our new column name is going to be uh i don't know we can name it just image we're gonna use our png image converter and it's gonna be what binary column in this case it's content it kind of automatically detects it we're gonna hit okay and now if you look over we have our image column and it's in the same format as our method one images would have been so that's great it's gonna create a new a new data set right this isn't tied to our table just yet and so one of the things that you want to do this is going to make it a lot easier is if you notice i named my images the same name as the mascots the mascots on my mba team so if the mascot for the milwaukee bucks here i put bucks my image is also named bucks and that's so that i have essentially a a key column right like a foreign key and a primary key type of column so that i can join these later and be able to use them hit close and apply it's going to bring in our mba data right there and remember our mba data which is our folder of images this name column has our images named as blazers.png or mascot.png so what we're going to do is we're going to create a foreign key here in our mba data this is our primary data okay so we're going to create a new column i'm going to name this image key and it's going to equal our mascot name and dot png now the reason i'm doing this if you haven't if i didn't explain it correctly earlier is in our picture data that we imported the name of our picture and i did this on purpose so that i could join this simpler the name of our picture is our mascot name from our mba data so rockets clippers blazers wizards dot png right so our custom function imports these images and it creates a name column named whatever the the name of the images and i know that the name of the image is i named it the same as the mascot so that i could join it so that's what we're going to do to our mba data what we did is we created an image key where we took the mascot name and we added.png so that i can now join these two on image key to name but essentially in order to link your images to your data try to name your images something simple that might also be found in your data set so that all you have to do to join them is add dot jpeg or dot png or dot gif or whatever it is that your images format is so now i've joined them and now the next step is is to actually bring in the image into my data set so i'm going to create a new column and this is going to be my images now our images and it's going to equal once you have two tables joined you can reference a column from another table and bring it in by using what's called a related function so it returns a related value from another table so because my tables are joined i can i can now go to my mba data set which has my images and bring in the image because that's what we named it using our custom function so now on my images i'm going to make sure this is set to an image url and now when i go to create my slicer i'm going to bring in my image so there they are and now when i bring in my custom simple image from the marketplace i'm going to use my image as well and there it is now this one's going to default to the wizards because i don't have anything selected yet but once i start selecting for example denver there's the denver nuggets here's denver and they're points per game here's the houston rockets here's memphis right here's the thunder and so that's method number two method number two typically also you're working with icon images when you start working with bigger files you're gonna start running into some problems and that's what we're gonna address in method number three okay so finally we arrive at method number three method number three is going to be the most complicated of them so go look in the description i have a link to the blog that explains method number three you're going to be able to find the code you able to copy and paste i'm not going to walk in detail on what all the pieces of the code means so most of this you're just going to have to go read on your own and copy and paste the code but i'm going to show you how it works because method number three is the method we use when your images are too big and what i mean by that is if you have a high resolution image or if you have a fairly large you know maybe even like 300 by 300 pixel image might be too big to be able to use methods number one and number two so i would say use method one first if it's a small number of images if it's a lot of images use method number two but if you start running into problems where your image doesn't render all the way then chances are your image is too big to be able to import it using methods one and methods two so that's where method three comes into play method three is for working with really large or high resolution images when you convert the image into base64 usually it's a really long string that represents that image if that string is greater than 32706 characters that is the max that an excel cell will hold and that's also the max that a power bi column will import so if you're using power bi to import a column that has a value greater than 32 766 characters it'll truncate that column so and that's usually where we get our images that only partially render and that's where we have to use method number three so let me go into method number three chances are if you're having issues it's probably with something that we're gonna learn how to fix using method number three so let's go into that okay so here we are at method number three and before we actually get going on method number three i'm going to show you why methods number one and methods number two do not work or why if you've stuck around this long you may be encountering problems with your own images so here i have real estate data all right so maybe i'm a real estate agent and i have four houses that i'm currently listing two in arizona one in texas one in california right i have the address the price of the house the number of beds and bathrooms and their square footage and i labeled them house number one two three and four and for simplicity i labeled the matching images as houses one two three and four it is a 640 by 426 though so this is much bigger than a normal icon that we've been working with and it's a high resolution image right there's a lot of colors this is an actual image not not just like a vector drawn image this is going to be important as to why we use method number three as opposed to methods number one and two or why you may be experiencing problems with methods number one and two so first we're going to try method number one so we're going to go ahead and create maybe a picture column right method number one remember we're going to copy it we're going to go into here actually we're just going to drop them into here you know copy image number four right the very quick way to do it copy image number one image number two and image number three save my data set all right so we're going to refresh our data and you can see we've brought in the picture remember we want to make sure our data category is an image url so for method number one going to create a slicer here in the middle for both methods where i'm going to filter based on house number okay so we're on house number one two three four so now remember we want to use our image our simple image because we want to be able to look at our picture using method number one so we're going to bring in our picture into the image url and that's what we're getting if you're getting this error here it's not a problem with method number one or method number two it's a problem with how big your file size is and it's a problem with how power bi and microsoft like excel handles really really large data text i guess if i go look in here so this is a really really big text string how big exactly we're gonna go look if i go open up a notepad plus plus and i paste this in here and i go to the very end if you look closely it's gonna go the length of this string is 32 769 characters long power bi and microsoft excel have a limit how many characters you can have in a column so microsoft excel has a cap at thirty two thousand seven 766 that's the max amount of text that you can have in a cell in excel power bi is very similar if you import data and it doesn't matter where you're importing it from if you're importing it from excel if you're importing it from a csv or if you're importing it from sql if your column has more than 32 766 characters it's gonna truncate it so that's what's happening with method number one so this this text string here is actually greater than thirty two thousand hundred 766 characters i mean if we go to like the very end here right it ends with like a m6h5 or something that's not the same ending that we have here and that's because that is being truncated okay if i go back into here and i copy this image and i bring it into here you can see how much greater how much longer this image is than the top one because excel has a limit on the number of characters that you can have in a cell 32 766 power bi is the same way if i were to try to load this into power bi it would also truncate it right around there and the problem with that is you get images that aren't fully developed you only have a portion of the code needed to render this image so okay so what if we go to method number two all right method number two where we use our custom function to try to import the image so let's try that so for method number two we're gonna go transform data and we're gonna do this pretty quick remember we're gonna create a blink query i'm gonna go to advanced editor and we're gonna paste our code and these images are jpeg images i believe yep these are jpeg images so i'm going to leave this as is i'm going to hit done uh image convert i'm going to name that function we're going to go grab our folder that has our images and we're going to let the custom function convert it and we're gonna hit okay remember there's just four images and i and i put the names the same as they appear in my house number here so that if i want it remember to link them together using a key i can link the two tables together but we're not going to do that for right now we're just going to transform data we're gonna go to add column invoke custom function uh image we're just gonna name this image and we're gonna use our image convert on the content hit ok and you'll see that it's converted our image once i hit close and apply this didn't work so we're actually using our second data set which is our folder that has our images that we've converted using our custom function but because power bi has the same limitation that excel has where it'll only pull in a record up to 32 766 characters we're going to see if we encounter the same problem so i'm going to create a simple image place that here i'm gonna bring in another filter for method two where we're gonna bring in the name we're gonna filter just a you know just so we can see image number one or something and we're going to bring in our image but our image has to be image url so if we go to our simple image and we bring in the image we run into the same thing so house number four same issues right this is using method one versus method two it's the same thing we run into the same limitation of the number of characters that we can bring in from a column method number three is a unique way to try to circumvent the problem the limitation of only those 32 000 characters that we can import so here we are at method number three right i have my real estate data um here's my slicer of which house i'm looking at we don't have the image yet but we'll bring it in right here's my house in california seven beds nine baths here's one of my houses in arizona for 11 million five beds twelve baths and then here's the address but now we wanna be able to bring in the image which we realized right that we can't do using in methods one and two so we're gonna go to method number three so for method number three we're gonna go to transform data it's gonna be another custom function it is in the description so go ahead and copy the custom function in the description under method number three and we're going to bring it in so once we get here we're going to go ahead same thing new source blank query advanced editor paste the custom function now this one i'm not going to walk through as much the custom function is in the it's in the description but it's also in the blog which i have the link to under method number three as well and that's for working with larger images the only thing you're really going to have to change is you're going to have to set this to the folder where your images are at right so mine are here and so that's what i've set this folder path to everything else you can kind of read what it's doing if you want to like you know try to understand it yourself but essentially what we're doing is we're going to go through each image and we're only going to grab we're going to convert it into our base 64. but we're only going to grab the first 30 000 characters and then create a new row with the next 30 000 characters and a new row with the next 30 000 characters because so that way we can circumvent the problem that power bi and excel have of only being able to insert the 32 000 uh text characters so in this case we're going to split it into 30 000 characters i'm not gonna explain the rest of this i'm just gonna hit done which is also what you should be doing really you should just be setting this to where your images are at so now it's imported here's image number one and image number one is essentially eight rows long here's the first 30 000 characters then the next 30 000 characters and so on and so on and so on until here the function also adds an index and this index is going to be useful because we're going to concatenate these characters back together using the index order right we want to make sure that this is the first 30 000 characters followed by this one followed by this one followed by this one so the image renders in the correct order that's why this index column is created so here we have our you know image index or something this is good i'm going to go ahead and close and apply so now the next part that we're gonna do is now that we have our image index we're gonna create a measure that's gonna combine all of that together go ahead and hit new measure and go ahead and name your measure uh in this case i already have a column called pick in here i'm going to just name this image not a column right this has to be a measure because we we have to use dax to combine all of our different rows now for our image we're going to write the following function if open parentheses okay if and i'm going to set this to this variable is going to be image equals to it and then has one value so returns true when there's only one value in the specified column and we want to run this against our image index table and this is going to be against the name column if has one value okay then data image forward slash i'm working with jpeg images so i'm going to put jpeg base 64 comma space close that string and then we're going to put and we're going to concatenate now along with this so we're gonna so now that we have the the prefix to our image we're gonna concatenate all the different parts of our picture so we're going to concatenate x because this is the measure of the table it's going to be image index comma image index pick which is all the 30 000 you know string characters that we're going to piece back together using what delimiter in this case is the comma delimited and we're going to order by our index and that's why we have the index so that we make sure that we combine all these pieces in the correct sequence so by our index and it's going to be ascending so comma ascending and we're going to close parentheses on the concatenate x and then we have a closed parenthesis on our if statement and then we're going to return our image now if you don't understand what this is doing don't worry too much about it uh follow the blog they have the code in there i've also put this code in the description there's two parts to method number three one is importing the data and that function is there and then this is the the measure the calculated measure that then combines our image together so i'm going to go ahead and hit enter so now i have this image that it's not going to show here but if we look at our pick remember what the image is doing it's taking this and saying okay is name one for all the name ones concatenate these values in this order okay and it essentially returns that into one row but now what we got to do is now we're going to join this into our real estate and we're going to do it very similar to method number two where i'm going to create a column here that is my image key and image key is going to equal my house number because that's also the way that my is saved so it's gonna be house number and dot jpeg hit enter i'm gonna go to we don't want that i'm going to go to my relationships here and i'm going to combine my image index we don't need this one this one's for method number two using the name into my image key okay now that those are combined any filtering that i do on the real estate column will also filter my image here's another part that's going to differ all right get me out of this thing all right i've i've tested this on method number three and method number three it will not work if you use a simple image so if i use a simple image i am not able to grab my newly created image and bring it in here because for some reason it does not use a it cannot take a measure it has to be a column that it uses in there okay i've tried so i did find and it's also in the blog if you use image pro by cloud scope from the marketplace so this custom visual ah let's go do that here it is so image pro by cloud scope you can use a measure as your image url so if i go to my image here remember make sure that it's an image url and we're going to go ahead and drag that into our image url here bam so there it is now it renders the entire image so if i go to picture two in scottsdale arizona five million dollar house this house is five bedrooms seven baths if i go to four in california here's my california home if i go to three here's my texas home okay and remember we weren't able so image number four we weren't able to do that with methods one and methods two because when you convert it to your base64 the amount of text in that column it's too big for excel it's too big for power bi so that's why there's that blog that explains how to do it using this method number three which is remember splitting it out by you know chunks of thirty thousand and then recombining it using this function here once recombined you then create sort of a foreign key column and remember the foreign key for me is take the house number and add jpeg to it because that's going to match the name of my pictures joining those two together and now using image pro by cloud scope and bringing in that new measure that combines them so that i can now render the entire image and that's how we get a very large image or an image that if you're not able to get it loaded using image methods number one or method number two right if it only shows part of the image it's because that binary conversion to base64 it's too large so you might have to resort to method number three it sounds tedious it sounds annoying but i do have both the functions that you need to do this in the description so that should be a lot easier i also have both of the blog links on how to do this in the description so you can follow along step by step there in case i've gone too quick or i wasn't i didn't explain something correctly please let me know in the comments if you have any questions on any of this i can you can shoot me an email i can post my email and if you're having any problems with loading your images then i can try to help you out that is the three methods on how we can load a local image right an image not on the web into a table using power bi so that we can have it available for visualization so thank you for watching hit like and subscribe if you enjoyed it and we'll catch you guys next time

Channel: Dapper Dash

Views: 53,701

Keywords: PowerBi, Power Bi, svg, BusinessIntelligence, Business Intelligence

Id: W0KyQ6w_-nI

Length: 39min 23sec (2363 seconds)

Published: Fri Apr 16 2021