Displaying Base64 Image in PBI desktop

[Kafui]

I am using Power Bi preimum. The report is a composite model. For fetching the base64 images from a SQL table  it uses a direct query. Some of these strings are over the 32k characters limit that Power Bi can handle(they go up between 40-45k characters).

Since it is a direct query Chris Webb's solution didnt work for me, as it would only work data that had been imported into PBI.

[Anonymous · Accepted solution]

Hi,@Kafui 

Regarding the issue you raised, my solution is as follows:
 1.You can try using the following functions to convert the data into smaller data:

(Input) =>
let
// Debug function
// Source = LongTable,
   Source = Input,
   JSON = Json.FromValue(Source),
   Compress = Binary.Compress(JSON, Compression.Deflate),
   ToText = Binary.ToText(Compress),
   TextLength = Text.Length(ToText),
   ChunkUp = {0..Number.RoundUp(TextLength/30000)-1},
   ConvertToTable = Table.FromList(ChunkUp, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   StartPosition = Table.AddColumn(ConvertToTable, "Start", each [Column1]*30000),
   Elements = Table.AddColumn(StartPosition , "Custom", each Text.Range(ToText , [Column1]*30000, List.Min({TextLength-[Column1]*30000,30000})))
in
   Elements 

2.Then use this function to recover the data:

let
    Source = (LinkbackTable) =>
let
// Debug function
// LinkbackTable= LinkbackTable,
    Source = Excel.CurrentWorkbook(){[Name=LinkbackTable]}[Content],
    CombineText = Text.Combine(Source[Custom], ""),
    ConvertToBinary = Binary.FromText(CombineText),
    CompressBinary = Binary.Decompress(ConvertToBinary, Compression.Deflate),
    OpenJSON = Json.Document(CompressBinary),
    ConvertToTable = Table.FromList(OpenJSON, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandAllColumns = Table.ExpandRecordColumn(ConvertToTable, "Column1", Record.FieldNames(ConvertToTable[Column1]{0}), Record.FieldNames(ConvertToTable[Column1]{0}))
in
    ExpandAllColumns
in
    Source

3.Here are the links to the relevant documents I found to help you:

Solved: About the character limit when loading a query int... - Microsoft Fabric Community

Chris Webb's BI Blog: Storing Large Images In Power BI Datasets (crossjoin.co.uk)

How to store tables longer than 1,1 Mio rows in an Excel-sheet using Power Query and JSON – (thebicc...

Solved: Re: Image Visualisation not showing the full pictu... - Microsoft Fabric Community

If you have any other findings, you are welcome to share them with us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.