powerbiblackonyellow250x98

When the data you “Get” in Power BI doesn’t come in nicely organized rows and columns, sometimes you need to do more with “Edit Queries” than just using the functions available on the ribbon. And that’s when you need bust out some “M” code to get the job done. In what follows, I’ll describe one such situation that I’ve run into a couple of times now.

Let’s say you’ve received a data file from another source system that is a two-column table, consisting of “name/value” pairs where the Name values repeat, like so:

pivot1

Your objective is to pivot the repeating Names into individual columns with their corresponding Values now appearing as individual rows underneath them, so it looks like this in a Report View table when done:

pivot2

How will you do this using Power Query? Seems like a classic “we need to pivot” situation.

If you select the Name column and then “Pivot Column” from the Transform tab, you get this:

pivot3

That’s not awesome…

If you select the Value column and pivot, it looks even worse, and doesn’t really make sense to do.

Maybe you’re not sure if you should be doing a “pivot” or “unpivot” at this point, so you try to unpivot:

pivot4

OK, no… we want to pivot, not unpivot.

Next you try Googling “power query pivot table function” and come upon the Table.Pivot function in the Power Query Function Reference.

It would seem that the key (pun intended) to getting your two-column name/value pairs into the desired format is to have another, new column playing the role of the “key” column from the Table.Pivot examples.

So how do we create this new “key” column in a way that allows us to pivot on Name?

If your data is like my sample data, and the Name values consistently repeat at a specific interval (in this case, groups of 4), one way to do it is by adding an Index column and then manipulating the Index values with the Table.TransformColumns and Number.IntegerDivide functions, as follows:

 =Table.TransformColumns(#”Reordered Columns”,{“Index”, each Number.IntegerDivide(_,4)}),

Now your data looks like this:

pivot5

Much mo’ better.

With your Index column setup just like the “key” column from the Table.Pivot examples, you can select the Name column and, from the Transform tab, choose “Pivot Column”.

Et voila!

pivot6

Your last step is to remove the Index column, then Close & Apply to load your table into the Power BI data model.

Learn more from Blogs, Samples and Videos by subscribing to our mailing list

* indicates required