Category: Power BI

Matt Allington shows us how to create a collapsable panel in Power BI:

There is nothing worse than having a Power BI report that has 50% of the space taken up with slicers.  When this happens, you only get half the page to visualise the actual data.  But on the flip side, if you don’t have the slicers it can be harder for the report users to filter the data they want to see.  Many users don’t like using the built in filter pane on the right hand side.  All is not lost – there is a great way that you can have the best of both worlds by creating a collapsible slicer pane that you can show and hide on demand.

Now I didn’t invent this concept – I learnt it from looking at what others have done, such as Amanda Cofsky, Miguel Myers, Mike and Seth from http://powerbi.tips and also Adam and Patrick from GuyInACube.  There are lots of great resources out there to learn tricks like this, so you should check those out.

You can see one simple interpretation of this solution below. The user can hide and collapse the slicer pane by using the arrow keys (#1 and #2 below).

Click through for the demo.

Comments closed

Reza Rad looks at a preview feature in Power BI to perform fuzzy matching:

Fuzzy Merge is a way of joining two tables together, but not on exact matching criteria, but on the similarity threshold. If you want to learn what is the Merge operation itself and the difference of that with Append, read my blog post here. If you want to learn more details about what is Merge and the different types of join or merge, read my other blog post here. Merge or Join is simply the act of combining two tables with different structures, but with link/join columns, to access columns from one of the tables in the other one.

To use Merge operation on the “source” query, You can click on the Merge Queries as New option in the Home tab of Power Query Editor window.

This kind of functionality was in SQL Server Integration Services as well but suffered from a huge scaling problem, where the component worked pretty well with small numbers of records, but once you got into the 100K+ range, everything started to fall apart.  I’d be interested to see where that limit is in Power BI.

Comments closed

Paul Turley shows us how to hit secured Web API endpoints with Power BI:

Having recently worked-through numerous issues with API data feeds and deployed report configurations, I’ve learned a few important best practices and caveats – at least for some common use cases.  In one example, we have a client who expose their software-as-a-service (SaaS) customer data through several web API endpoints.  Each SaaS customer has a unique security key which they can use with Power BI, Power Query or Excel and other tools to create reporting solutions.  If we need a list of available products, it is a simple matter to create a long URL string consisting of the web address for the  endpoint, security key and other parameters; an then just pass this to Power Query as a web data source.  However, it’s not quite that easy for non-trivial reporting scenarios.

Thanks to Jamie Mikami from CSG Pro for helping me with the Azure function code for demonstrating this with demo data.  Thanks also to Chris Webb who has meticulously covered several facets of API data sources in great detail on his blog, making this process much easier.

Click through for the instructions.

Comments closed

Imke Feldmann wants to transform text into proper case, but certain words should have their own casing style:

The overall strategy is to convert everything to proper case first and then use a translation table to convert the keywords from the table back to their desired values. The following steps show how to do it:

It’s an interesting approach.

Comments closed

Matt Allington takes a look at a new feature in Power BI:

The data profiling tools look at the first 1,000 rows in the preview data loaded an shows you the big picture of what the data “looks” like.

Currently the profiling tool only works on the top 1000 rows of data.  It also takes some time to prepare the profile of the columns (as could be expected), however the benefits of getting this stuff right before moving on far outweigh the slower load times (IMO).  I would love to see an option to profile the entire set of data for one or more columns.  I am sure this will come.

Teo Lachev shares some thoughts on what it would take to make this a killer feature:

That’s all data profiling you get for now. Here is what it will take to make Power BI data profiling a killer feature:

1. Allow data profiling over all the values (understandably there will be performance impact).

2. Add more aggregates, such as Min/Max/Std/Median.

3. The ability to dynamically filter the preview data for the selected bar in the profile.

As it is, there’s enough here to see the potential of where it could go.

Comments closed

Zach Conroe shows how you can embed an image in Power BI:

The good news is that there are workarounds to this challenge. We are going to reconstruct the above use case and demonstrate how to pull in images from a local database, and then use custom columns in Power Query to reformat the source data in a way we can render graphically.

Note: This same custom column technique can also be used to display images imported using a local folder as a data source. If you have Power BI Desktop installed, you can work through along with this post by downloading the .pbix file with this link.

In our sample database we have a couple of tables containing images stored in a binary format, as well as a few columns of metadata for the images. The images being used here are a JPEG file type, but this technique can also be used for PNG files. We imported the data into Power BI and loaded two tables of images: Examples 1 and 2. For the first example we used three small images in the table, as shown below.

There’s a 32K size limit that Zach mentions, which can be a bit painful to work within.

Comments closed