Episode 2

We’ve actually managed to produce yet another episode of our podcast, and while we were at it, we came up with a name. The podcast is now aptly named “knee-deep in tech” and will (for the forseeable future) be a work in progress. As always we gladly accept tips and criticism, as well as ideas for content for us to cover. Just tweet me (@arcticdba) or Simon (@bindertech)!

Podcast – what could possibly go wrong?

Me and my colleague Simon (@bindertech) has decided to start a podcast. It’s brand spanking new and while we don’t have a clue about how to do a podcast we do have a lot of ideas for content. Our first episode focuses on database backup compression and application testing (a bit of an unholy combo, but, well). Take 20 minutes out of your busy day to listen to our first episode and as always, don’t hesitate to give us feedback!

Combining tables in Power BI and finding two(?) bugs

A student of mine had a question about combining data from multiple Excel sheets and doing calculations on the data. I realized I didn’t have a clear-cut answer so I decided to do some digging. The scenario looked like this:

I created three excel files, each with a column for name and one for value. The idea is to simulate sales persons, sales amounts and having one file per product. I called the first file ProductA, the second ProductB and the third ProductC.




Then it was time to combine the tables. I wanted the result to be a table of all the data for the other tables, but it should also include where the data came from – i.e name, sales amount and product. This can be done in several ways, and one is to add a step in the loading of the excel files to add a column with a static name:

and then appending them together, like this:




The trouble is that this is static. There had to be a better way of doing it, and after spending a few hours trying to find one I asked Reza Rad (@Rad_Reza, radacad.com), one of the most knowledgeable (and kind!) Power BI people on the planet. He showed me the use of the #shared construct. #shared lists all the Power Query (M) functions like this:


This is very useful for very many other applications, but in this case the goal was to remove the need for static table names. The first rows contain the names of the tables we’ve been using, so filtering those rows and expanding the data might give us what we need:


This looks exactly what we were after! With just a slight tweak of the column names we’re good to go. Turns out there’s a slight problem. As soon as I click apply the whole thing breaks:


After some discussion with Reza we came to the conclusion that there is a bug in the Table.FirstN function that filters out the rows. Using Table.Range or Table.RemoveLastN works much better. I’ve logged a bug at the PowerBI.com forums, so hopefully this will be taken care of in the future.

Here is the Power Query code that do what I just did:


Reza did his own blog post on this technique as well as shared another nifty tip: using #section. Take a look at Reza’s blog!


*edit* It seems there might be another bug here, as the data won’t show up back in PBI Desktop, despite everything looking as it should in the query editor. Stay tuned…