H2Eflipdata

How to Flip a Table in Power Query

Do you want to change how your Power Query table looks? In this post, I’ll show you how you can flip your data so that you can turn a table that looks like this:

Table in Power Query that has order numbers going vertically and other fields across.

Into this:

Table in Power Query that has order numbers going across and other fields going vertically.

In the second table, it’s a bit easier to see all your fields vertically and you don’t need to scroll across to see them all. Depending on how you may want to visualize your data, you may find it useful to swap from one view to the other.

How to transform and flip data in Power Query

To transform the first field into the second field, you’ll need to take two steps in Power Query. The first is to unpivot your data. In this example, I want the order numbers to be as my headers going across, and so I will right-click on that header and select the option to Unpivot Other Columns:

Unpivoting other columns in Power Query.

That will result in the table transforming as follows:

Power Query table after being unpivoted.

This isn’t quite what I need yet, but it’s close, as it has the fields going vertically instead of horizontally. The last part is to put the order numbers going across the top. To accomplish this, I will select that column and choose the Pivot Column option to re-pivot the data again.

The Pivot Column option in Power Query.

Then, on the next screen, it’s important to select the correct values option. And, you’ll also want to select Advanced options and choose Don’t Aggregate:

Pivot column options in Power Query.

Now, I end up with a Power Query table that has been flipped and has the order numbers going across and the fields going down vertically:

A table in Power Query that has been flipped from its original layout.

How to flip the data back

Let’s suppose that you start with the above table and you want to flip it the other way (so that the attributes are going across). Here again, we’ll start with unpivoting the data back. The most important consideration is to know which field you want going across. In this case, it’s going to be the attribute field. Right-click on that and click on Unpivot Other Columns. Then, you’ll see this table:

An unpivoted Power Query table.

Now, the next step is to re-pivot the data. Select the Attribute field on the left and click Pivot Column. Again, you’ll need to select the correct value column and choose the option so that you Don’t Aggregate:

The Pivot Column settings in Power Query are displayed.

And now we’re back to having the order numbers going vertically. Although some of the fields have moved around amidst all those changes, the format is back to how it was at the beginning:

Power Query table showing order numbers going vertically and other fields going across.

If you liked this post on How to Flip a Table in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Add a Comment

You must be logged in to post a comment