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…