You can customize the look and feel of the Power BI reports using the formatting pane for each specific visual or you can speed up the process by using a JSON file.
This feature (the JSON file) has been available for quite a while but on the September 2017 update of Power BI Desktop, they allow us to define even more elements and that is great, but the documentation is poor (sorry Microsoft, it is) and unfortunately, a lot of the elements wont work even when you add them correctly to the file.
To be able to manage this, I have created my own guide for the JSON file and I am sharing it with all of you so you dont have to waste a lot of time finding out how to build it.
This is an iterative process and I am still working with the file, but it is useful, even if it has a few bugs and some missing fields.
Video tutorial on how to create a JSON file
Watch this video if you need help getting started with the creation of a JSON file.
Video tutorial on how to use Curbal’s JSON template
Watch this video if you need help getting started with the file. I will do a tutorial on the JSON file in a later video.
Link to JSON file
And you also get my work-in-progress Power BI JSON template (It is saved as a TXT, just change the extension):
Hopefully you will find this template as useful as I have and it gets you started creating Power BI templates in no time
/Ruth
Thanks a lot for this. it is very helpful. Please is it possible to have access to the power bi file used. Thanks a lot
Sure,
I will post a link shortly!
/Ruth
Thanks for all the help. Are you able to apply a background image using the JSON file?
Unfortunately, not yet, but hopefully soon!
/Ruth
Davi,
a combination of extract after delimiter @ and before delimiter from add column and transform columns does the trick (all buttons, no M code needed).
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1″, type text}}),
#”Inserted Text Before Delimiter” = Table.AddColumn(#”Changed Type”, “Username”, each Text.BeforeDelimiter([Column1], “@”, 0), type text),
#”Extracted Text After Delimiter” = Table.TransformColumns(#”Inserted Text Before Delimiter”, {{“Username”, each Text.AfterDelimiter(_, ” “, {0, RelativePosition.FromEnd}), type text}}),
#”Inserted Text After Delimiter” = Table.AddColumn(#”Extracted Text After Delimiter”, “Domain”, each Text.AfterDelimiter([Column1], “@”, 0), type text),
#”Extracted Text Before Delimiter” = Table.TransformColumns(#”Inserted Text After Delimiter”, {{“Domain”, each Text.BeforeDelimiter(_, ” “, 0), type text}}),
#”Merged Columns” = Table.CombineColumns(#”Extracted Text Before Delimiter”,{“Username”, “Domain”},Combiner.CombineTextByDelimiter(“@”, QuoteStyle.None),”Email”)
in
#”Merged Columns”
It could get tricky at the end of the email address. The only thing I might add would be a way of trimming any punctuation and other non-valid characters from the end of the email address. The above scenario works for your cases which have a space before and after the email address.
Hi Ruth, thanks for making our lives easier 😉 having trouble downloading the file, though. I’m getting a 404. Can you maybe e-mail it to me? Thanks a lot!
Hi Leenke,
Here is the new link:
https://curbal.com/wp-content/uploads/2017/11/Curbal_PowerBI_theme.txt
/Ruth
Another very clever article and supporting videos. Certainly removes repetitious and boring work. Thank you.
Thanks Christopher 🙂
/Ruth
Hi, Ruth
Your explanation is really great and it helped me a lot in understanding power bi theme template files. However I have one task that I am still not able to solve. I wonder if it is possible using Power BI Template or other technic that you may know.
We have multiple visuals that present same metric. We display handled/lost call count on “Column chart” with timeline Y-axis, and on “Pie chart” for general overview.
We would like to have the same data colors (handled – green, lost – red) across all different visuals. However Power BI quite often resets the color setting. It takes color from predefined list from Theme file, but still we can’t predict which exact color will be chosen for specific legend category.
I thought it would be possible to solve using JSON template file, but I do not see “Data Colors” among card names for any visual.
Hi Ivan,
Here is how I think it works:
If you override the default settings of a template, they will be maintained until you choose to reset back to default.
Power BI should respect the order in which you add the colors in the template, again, unless you manually override them.
Hope this helps!
/Ruth
Hi Ruth,
This is really fantastic work and extremely helpful, especially with regard to the styles and cards that are uneditable at the moment. It is incredibly frustrating trying to navigate those on your own and Microsoft have as far as I can tell not provided a single shred of documentation on the matter.
Do you know anything about what we can expect with regard to styles and cards that are uneditable at the moment?
Thanks,
Best,
Michel
Hi Michael, thanks!! 🙂
I have no idea and I wish it was yesterday. It is a try and error process until they decide to fix it. Send them a tweet @mspowerbi to remind them 🙂
/Ruth
Hello, I really like your work and the challenges you bring to the Curbal Channel.
I would like to propose a challenge.
Extract an email from a phrase.
Example 01:
My name is David Martins and this is my email: e-mail@example.com
Example 02:
My email is e-mail@example.com to send the channel files.
That is, extract only the emails in a position-independent column using Power Query.
With VBA it is possible! But what about Power Query? This is the challenge proposed for MVPs.
Hi David,
You mean to extract example.com from the email addresses?
/Ruth
Nice post! I too thought the Documentation for Power BI Themes need some work. Thank you for clearing things up. How do you use Drillthrough feature with a Live Power BI report? Right click doesn’t seem to be doing the trick…
Hi David,
Thanks, really happy it is useful 🙂
What do you mean with a Live report? Publish to web, or in powerbi.com or embedd? It should work on all cases though…
/Ruth
Very, very helpful – thank you!
Thank you Amanda! I will continue updating it as I find time for it.
/Ruth