Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Embed Full Image into power bi

Dear Community

 

i Am Trying to Embed an image to a report, but when i try to make the following query to get the full image i get this error:  

 

"#table is invalid constant value"

 

I am getting the images from my odbc source into the power bi desktop, but the string form my DB exceeds the limit of 32.000 caracters that power bi exepts. so "Patric" from guy in a cube found this solution:

 

https://www.youtube.com/watch?v=Q82yzcfkqAc

 

Allowing the 32.000 caracters per row indstead and by the following query spliting the binary up and putting it back together to display the full image, and not just the top part of it.

I got the code from #Guy in a cube link above and #Chris link below:

https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/

 

I Hope you can help me understand the part i am missing, i am new to PBI.

i have used the spoiler tag in my code where i am getting the error. 

 

My code:

= let
//Input parameter to call the function
billedresultat = (InputTable as table, InputBinaryZBPosistion as number, InputNameZBPosition as number, InputKeyZBPosistion as number) as table =>
let
// get list of pics
Source =(InputTable),
//converts pics to list
ListToInput = Table.ToRows(Source),
//make split function
SplitTextFunction = Splitter.SplitTextByRepeatedLengths(300000),
//Converts binary to text and plits pic up into 30k rows 
ConvertOneFile = (InputRow as list) =>
let
BinaryIn = InputRow(InputBinaryZBPosition),
CprNr = InputRow(InputNameZBPosition),
BilledKey = InputRow(InputKeyZBPosistion),
BinaryText = Binary.ToText(BinaryIn,BinaryEncoding.Base64),
SplitUpText = SplitTextFunction(BinaryText),
AddFileName = List.Transform(SplitUpText, each {CprNr, BilledeKey,_})
in
AddFileName,
//Looper through all pics and calles above function
ConvertsAllRows = List.Transform(ListToInput, each ConvertOneFile(_)),
//combines lists
CombineLists = List.Combine(ConvertsAllRows),

Spoiler
//converts result into tabel
ToTable = #tabel (type, table[CprNr=text, BilledeKey=number, pic=text], Combinelists),
// adds input colonm to output table

AddIndexCloumn = Table.AddIndexColumn(ToTable, "Index", 0,1)

in
AddIndexCloumn
in
picresult

 

 

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    At the first glance, I found some typos in your M codes.

     

    I followed Patrick's video to create a function to get the images. Below are my codes for your reference. Also attach the pbix file at the bottom. Hope this helps.

    let
    //Input parameter to call the function
    picresult = (InputTable as table, InputBinaryZBPosition as number, InputKeyNameZBPosition 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),

    //Function to convert binary of photo to multiple
    //text values
    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, _})
        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[RegionName=text, CountryKey=number, Pic=text],CombineLists),
    //Adds index column to output table
    AddIndexColumn = Table.AddIndexColumn(ToTable, "Index", 0, 1)
in
    AddIndexColumn
in 
    picresult

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Anonymous 

     

    At the first glance, I found some typos in your M codes.

     

    I followed Patrick's video to create a function to get the images. Below are my codes for your reference. Also attach the pbix file at the bottom. Hope this helps.

    let
    //Input parameter to call the function
    picresult = (InputTable as table, InputBinaryZBPosition as number, InputKeyNameZBPosition 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),

    //Function to convert binary of photo to multiple
    //text values
    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, _})
        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[RegionName=text, CountryKey=number, Pic=text],CombineLists),
    //Adds index column to output table
    AddIndexColumn = Table.AddIndexColumn(ToTable, "Index", 0, 1)
in
    AddIndexColumn
in 
    picresult

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.