Excel · Power Query

Rendering Images In An Excel Worksheet With Power Query Using Cells As Pixels

It’s a general rule of the internet that, whenever you have a cool idea, a few minutes spent on your favourite search engine reveals that someone else has had the idea before you. In my case, when I first saw the functionality in Power Query for working with binary files I wondered whether it was possible to read the contents of a file containing an image and render each pixel as a cell in a worksheet – and of course, it has already been done and done better than I could ever manage. However, it hasn’t been done in Power Query… until now.

First of all, I have to acknowledge the help of Matt Masson whose blog post on working with binary data in Power Query provided a number of useful examples. I also found this article on the bmp file format invaluable.

Second, what I’ve done only works with monochrome bmp files. I could have spent a few more hours coming up with the code to work with other file types but, frankly, I’m too lazy. I have to do real work too, you know.

So let’s see how this works. Here’s a picture of Fountains Abbey that I took on my phone while on holiday last summer:


I opened it in Paint and saved it as a monochrome bmp file:

Here’s the code for the Power Query query that opens the bmp file and renders the contents in Excel:

[sourcecode language=”text” padlinenumbers=”true” light=”false”]
//The picture to load
//Or get the path from the output of a query called FileName
//Load the picture

//First divide the file contents into two chunks:
//the header of the file, always 62 bytes
//and the rest, which contains the pixels

//Define the format as a record
Header = BinaryFormat.Binary(62),
Pixels = BinaryFormat.Binary()
//Load the data into that format
Overall = OverallFormat(SourceFile),
//Get the header data
HeaderData = Overall[Header],

//Extract the total file size and
//width and height of the image
HeaderFormat = BinaryFormat.Record([
Junk1 = BinaryFormat.Binary(2),
FileSize = BinaryFormat.ByteOrder(
Junk2 = BinaryFormat.Binary(12),
Width = BinaryFormat.ByteOrder(
Height = BinaryFormat.ByteOrder(
Junk3 = BinaryFormat.Binary()
HeaderValues = HeaderFormat(HeaderData),
FileSize = HeaderValues[FileSize],
ImageWidth = HeaderValues[Width],
ImageHeight = HeaderValues[Height],

//Each pixel is represented as a bit
//And each line is made up of groups of four bytes
BytesPerLine = Number.RoundUp(ImageWidth/32)*4,
//Read the pixel data into a list
PixelListFormat = BinaryFormat.List(
PixelList = PixelListFormat(Overall[Pixels]),
//Convert each byte to a number
PixelListNumbers = List.Transform(PixelList, each Binary.ToList(_)),

//A function to convert a number into binary
//and return a list containing the bits
GetBinaryNumber = (ValueToConvert as number) as list =>
BitList = List.Generate(
()=>[Counter=1, Value=ValueToConvert],
each [Counter]<9,
each [Counter=[Counter]+1,
each Number.Mod([Value],2)),
BitListReversed = List.Reverse(BitList)

//A function to get all the bits for a single line
//in the image
GetAllBitsOnLine = (NumberList as list) =>
List.Transform(NumberList, each GetBinaryNumber(_)
), ImageWidth),

//Reverse the list – the file contains the pixels
//from the bottom up
PixelBits = List.Reverse(
each GetAllBitsOnLine(_))),

//Output all the pixels in a table
OutputTable = #table(null, PixelBits)


The output of this query is a table containing ones and zeroes and this must be loaded to the worksheet. The final thing to do is to make the table look like a photo by:

  • Hiding the column headers on the table
  • Using the ‘None’ table style so that there is no formatting on the table itself
  • Hiding the values in the table by using the ;;; format (see here for more details)
  • Zooming out as far as you can on the worksheet
  • Resizing the row heights and column widths so the image doesn’t look too squashed
  • Using Excel conditional formatting to make the cells containing 0 black and the cells containing 1 white:


Here’s the photo rendered as cells in the workbook:

And here it is again, zoomed in a bit so you can see the individual cells a bit better:

You can download the workbook (which I’ve modified so you can enter the filename of your bmp file in a cell in the worksheet, so you don’t have to edit the query – but you will have to turn Fast Combine on as a result) from here. Have fun!

4 thoughts on “Rendering Images In An Excel Worksheet With Power Query Using Cells As Pixels

  1. Hey, when testing your excel, i got an error when refreshing the powerquery : Formula.Firewall: Query ‘ReadMonoBMP’ (step ‘Overall’) references other queries or steps and so may not directly access a data source. Please rebuild this data combination ….
    a google search brought me to this (where you were also involved…) https://social.msdn.microsoft.com/Forums/en-US/69153a6d-2205-4456-bcac-3a4689c787cf/parameterising-queries?forum=dataexplorer
    So I changed to ‘Fast Combine – Ignore…’ and then it worked fine.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Thanks Kurt, I forgot to mention that you have to turn Fast Combine on in the post. I’ll update it now…

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.