How to use your Office Theme as Power BI Report Theme

by DominikPetri Regular Visitor on ‎07-12-2017 01:10 PM - last edited on ‎10-24-2017 09:41 AM by Power BI Admin mbenjamin

Converting your Office Theme Colors to a Power BI Report Theme

Since importing the Office Theme Colors from "%appdata%\Microsoft\Templates\Document Themes\Theme Colors" is not possible, I have created an Excel file and some VBA that converts the selected Office Theme Colors into a Power BI Desktop Report Theme. Simply follow these four easy steps:

 

Step 1: Select the Office Theme Color

Select the color palette you want to convert via Page Layout > Themes > Colors

Select the color palette in the Page Layout tabSelect the color palette in the Page Layout tab

 

 

 

 

 

 

 

 

 

 

 

Step 2: Create the hexcode color codes

The Report Theme JSON file needs the color codes in hexadecimal format. Just click the button “Generate Hex-Codes for Colors” and a few lines of VBA will fill the cells D9 to D20. Since the JSON file needs the hexcodes in double quotes and prefixed by a hashtag, the VBA code adds them as well.

 

The Office Theme Colors and their corresponding hexcodesThe Office Theme Colors and their corresponding hexcodes

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 3: Name your theme

Enter a name for your theme in cell F18. The JSON file will be saved (see step 4) under this name so refrain from using special characters.

 

Step 4: Preview and save theme

The formula in B25 uses the hexcodes from cells D9 to D20 to create the text for the JSON file:

 

 Cell B25 shows a preview of your JSON fileCell B25 shows a preview of your JSON file

 

 

 

 

 

 

 

 

 

Now click the “Save as JSON” button and select a folder where you want to save it.

 

If you have any suggestions for improvements, I am more than happy to hear from you and implement them!

 

Dominik Petri

Comments
by gapage Visitor
on ‎07-19-2017 01:58 AM

Hi, just to note that this didn't work for me. The Hex codes generated don't seem to be the correct ones as the colours generated in Power BI using the resulting json don't match the colours I see in the xlsm.

by DominikPetri Regular Visitor
on ‎07-19-2017 07:58 AM

Did you click the button "Generate Hex Codes" before saving the JSON file? If yes, check if the Hex Codes in D9Smiley Very Happy20 are the same as in the JSON preview in B25.

 

I just tried it and it did work perfectly as described above.

 

Best regards

Dominik.

 

by martadeive Visitor
on ‎07-20-2017 02:45 AM

 Thank you for useful information! Everything is working!

Best regards
Marta.

by gapage Visitor
on ‎07-25-2017 01:54 AM

I tried again. Here is the result.

 

Expirebox link

 

When I choose the them from Page Layout the colours in the sheet change to the correct colours. I click Generate Hex Codes and the codes change and are reflected in the JSON box at the bottom. I don't think the Hex codes are right though as they don't tally with the ones I have got from our brand people (and generate the wrong colours in Power BI)

by DominikPetri Regular Visitor
on ‎10-24-2017 11:35 AM

The corrected v3 just got uploaded by the admin. The bug concerning the Hex Codes is now removed :-)

 

Will work on v4 which will include some of the new stuff that's possible in the Report Theme as described in this Power BI blog post.