Using Images from a Database in Power BI
Вставка
- Опубліковано 21 лип 2024
- 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...
Gerhard's Blog: blog.gbrueckl.at/2018/01/stor...
Chris Webb's Blog: blog.crossjoin.co.uk/2019/05/...
📢 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 - Наука та технологія
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.
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.
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.
That was impressive Patrick!! This is what I am looking for. Thanks bro
Thanks a lot for your presentation and for sharing that I've been searching for a long time. 😇
Just what I needed, Thank you Patrick!
Thank you Patrick! good explanation. Regards from Peru
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
Thanks for this! This comment should be pinned!
I appreciate your attitude, thank you very much. :)
LOL, I just re-typed it from scratch.
@@strongemotion Good thing you did, bro missed an input in their code
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)
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!!
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?
Great presentation. What software are you using to create?
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
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!
Hi Patrick! This is exactly what I am looking for, do you have the getTheEntireImage code posted anywhere?
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 ??
Great video dude
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
Could we add music to any page...so that while accessing or viewing could be with some professional background music
great solution!!!
I am Getting an error that #table is invalid constant value what is the realtion to this #table ?? it the #tabel a parameter
I have similar requirement in paginated reports
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.
It works for JPEG file but not with BMP file any suggestion.
How can i show the pictures in large scale and not in thumbling size? i need to display one large picture on a page
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.
In Power BI Report Builder?
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?
Hi Patrick/Mitchell, I'm getting the same problem trying to view pdfs. Any ideas?
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?
You can try something like Image by CloudScope (search AppSource). My company makes it, full disclosure.
You can just import the image from the insert menu.
Hello Patrick, thanks for sharing the tech. One question, where can we get the function getTheEntireImage code ? Thanks.
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
hi, did you find out where this is?
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.
Hi, Did you find the solution, I have similar requirement but should export to PDF.
Good, but frankly - it should be much easier to Store and Display Images, for the average user.
Great video , but too much for me to digest 😛
Interesting, such a basic functionality requires this amount of tinkering.
makes me think ms doesn't exactly want people to use pbi
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.
What alternative did you use