Power BI Infographic Designer

I was about to start badgering our custom dev team to create a plugin for Power BI, to enable in the card visualisation background images and some text formatting for a project I was on, but then a new custom visualisation for Power BI was released.


Looks great and I’ve been playing with it for the last few days and for some instances will work very well, but its not (in my opinion) production ready for some of the visualisations. Why? two main things are an issue.

1 – It doesn’t preserve the formatting of the measures. So anything i set a ‘,’ with two decimal places, or format it as a percentage, it will not show it. I get 0.3874634793290000000000. Well that’s a bit pants

2 – Fill percentage doesn’t work in a intuitive way. For a range of values it takes the highest value and then fills the rest with a ratio to the highest value. For example


I’ve set some values as 75, 55, 40, 33, and then added a bit of the internal art to show a fill percentage. I was expecting the 75 value to only show a fill of 75%, but it takes the highest value, fills it to 100%, than based on that ratio will fill the rest, so a value of 37.5 (half of 75) will fill the next image as 50%. I’ve tried setting the values as 75%, 0.75, 75, but nothing seems to work. I’ve emailed the team that make it with some suggestions.

1 – A tick box or other option that can indicate that the value is a percentage and should fill accordingly

2 – Be able to set the unfilled area via a measure, so you can a target, set a filled (80) and unfilled value (20) and it will work out the correct ratio to fill it.

There might be a work around, where you have two layers. I’ll experiment and update the article when I can! But it’s will be a valuable addition to the visuals if the team can tidy it up in a few places.

Importing data into Power BI from Azure Marketplace

There are a number of articles on the net about importing data into Power BI from the Azure Market place, but most don’t look at the difference in importing directly from the table or using the filtered URL to drive the report. One of my wonderful colleagues had an issue with getting data into Power BI to do a mash up between footfall data and weather for a client.

So using the example UK Met Office data set in Azure Data Marketplace, you can connect to the table exposed in the Met Office set. Continue reading

Pygame install headaches

Been a bit of a while since the last post, busy doing the day job.

I had a few issues installing Pygame, the Python plugin. I was using the PIP command to install the package but it wasn’t liking the path, the command or anything. After a bit of Googling, and a Bing search (I know, but it is getting better) nothing seemed to match. However I found a solution

Put the Pygame ‘whl’ file in the Pyhton folder, then run the PIP command. That worked! I’m not sure if this a windows config issue with Python or just my PC. Don’t forget run the CMD in as an administrator.

PIP Command

SSDT and SSIS backwards compatibility

If you are a consultant, and work with SQL Server, you typically have a number of clients that have varying versions of SQL Server. Sometimes it’s nice to have a greenfield piece of work as you can setup the server and software to a recommended specification. When you don’t and are developing a solution, you may have a number of versions of SQL Server Data Tools on your laptop. I have at the moment the following:

  • Business Intelligence Development Studio For SQL Server 2008 projects
  • SSDT 2012 for SQL Server 2012 Projects
  • Visual Studio 2013 with SSDT 2013 for SQL Server 2014 Projects

Why do you need this, well for each version of SQL Server Integration Services (SSIS) things changed and the new version isn’t compatible with the old ones, which is a bit of a pain. So I was surprised with the announcement of SQL Server 2016 which promised a backwards compatible SSIS. But how far back? Microsoft didn’t say. In the December 2015 SSDT 2016 preview update I saw the line:

Please note that SSIS designer support for SSIS 2012 and 2014 is not yet included in this update.

Brilliant. I was sort of hoping for 2008 to be included, but as there was quite a lot of change with the movement to SSIS Catalog and Project deployment types, I can live with out it. We’re seeing less and less 2008(R2) projects and most people are on 2012. Another bit of news is that finally, SSDT will have Database and SSIS, SSAS, SSRS project templates as default, no installing the Visual Studio Shell then SSDT for BI, it’s all (should be) in one nice package.



Power BI Desktop & Mapping using Latitude and Longitude

I was mapping some UK stop and search data for the West Midlands, and was trying to use the Latitude and Longitude items in the data set, and could not get it to map the incidents. I was dragging the fields to the Latitude and Longitude boxes in the visualisation options, but it didn’t seem map the incidents on the map. Confused…. well after a bit of investigation it seems that you need to concatenate the Latitude and Longitude to a new column, in this case I named it location ‘Location = [Latitude]&”,”&[Longitude]‘ together and placed it in the Location option, and it will map the location correctly. A bit counter-intuitive, considering that you have the option of creating a Data Category for the Latitude and Longitude columns and it creates a nice little map icon next to them in the field selector. Not sure what the separate Lat and Long boxes are for then?

Mapping In Power BI