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.
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
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.
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)
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.
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.
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?
@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.
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 ??
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!!
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
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
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.
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.
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.
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
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.
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)
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.
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?
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.
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!
Just what I needed, Thank you Patrick!
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?
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. 😇
Hi Patrick! This is exactly what I am looking for, do you have the getTheEntireImage code posted anywhere?
Could we add music to any page...so that while accessing or viewing could be with some professional background music
@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.
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 ??
Thank you Patrick! good explanation. Regards from Peru
Great presentation. What software are you using to create?
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!!
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
How can i show the pictures in large scale and not in thumbling size? i need to display one large picture on a page
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
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?
It works for JPEG file but not with BMP file any suggestion.
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.
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.
In Power BI Report Builder?
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
Good, but frankly - it should be much easier to Store and Display Images, for the average user.
Great video dude
great solution!!!
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
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.
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