Combine Excel Files That Contains Photos

[Anonymous]

Can we combine multiple excel files that contains photos? I tried combining two excel files that contains photos yet the column is showing it's empty. 

Thanks!

[Jimmy801]

Hello @Anonymous 

to extract photos from an Excel-file you have to use a different approach. You have to unzip the file and read it's content.

Here a code example to filter all JPG-file of one file

let
    Source = UnzipContents( File.Contents("C:\test\Mappe1.xlsx")),
    UnzipContents = (ZIPFile) =>
    //used from http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html
let
    Header = BinaryFormat.Record([
        MiscHeader = BinaryFormat.Binary(14),
        BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
        ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)    
    ]),
 
    HeaderChoice = BinaryFormat.Choice(
        BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
            then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
            else BinaryFormat.Choice(
                    BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                    each BinaryFormat.Record([
                        IsValid  = true,
                        Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                        Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]),
                        Content  = BinaryFormat.Transform(
                            BinaryFormat.Binary(Header(_)[BinarySize]),
                            (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise x
                        )
                        ]),
                        type binary                   // enable streaming
                )
    ),
 
    ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
 
    Entries = List.Transform(
        List.RemoveLastN( ZipFormat(ZIPFile), 1),
        (e) => [FileName = e[Filename], Content = e[Content] ]
    )
in
    Table.FromRecords(Entries),

    FilterJPG = Table.SelectRows(Source, each ([FileName] = "xl/media/image1.jpg"))
in
    FilterJPG

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans]

Power Query cannot see Excel images in a file. Those images are not in the table but float above it in a graphics layer. Same with all other objects like charts, icons, and any arrows or other graphics you can put in there via the INSERT ribbon in Excel. That is why PQ is showing the columns are empty.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Could you please try to verify if you can see the binary type of your image in Power Query Editor? Power BI cannot show binary data directly, we can only convert it to text to show it based on this similar thread: https://community.powerbi.com/t5/Desktop/How-to-use-Images-Stored-in-a-SQL-Server-Table-with-Power-BI/td-p/383111

Best regards,