The Order You Select Columns In The Power Query Editor Can Affect The Output Of Some Transformations

Maybe this is obvious to more experienced Power Query users, but something I always point out when I’m training people up on Power Query is that the order that you select columns in the Power Query Editor window (both in Power BI Desktop and Excel) can affect the output of certain transformations. For example, say you have a table with two columns A and B that both contain numbers; if you select A first and then B, and then go to Add Column/Standard/Divide, you’ll get a new column that contains the value of the calculation A/B. However, if you select B first and then A and do the division you’ll get B/A:

PQDivide

The order that you select columns is also significant for some other types of calculation such as Percent Of and Power, and also when you do a Merge Columns.

9 thoughts on “The Order You Select Columns In The Power Query Editor Can Affect The Output Of Some Transformations

  1. Thanks, Chris. It is a great feature. I use it quite a lot when I want to reorder the columns when removing other columns. As you click a column, it will reorder based on your sequence of clicks prior to removing other columns, as dragging columns around is very slow in PQ.

  2. We’ve had real challenges with data / column ordering whilst working with GA. If theres any tips on how to work with GA data in PBI, that would be a great blog 😉

  3. The one I love is the “Remove Other Columns”. Select the columns you want to keep IN THE ORDER you want them to be, then select “Remove Other Columns”. Power Query will not only remove the columns you don’t want, but also put the columns you selected in the order in which you selected them! How cool is that!

  4. Pivot columns is another one.
    Oddly group by seems to ignore the order if you pick a number of columns.

    BTW You example gif seems to be scaled and is not as clear as your other posts unless you click to open it.

    Slightly off topic, but I’ve just noticed that merge can create new columns if you use from the add column menu. The right mouse and transform version removes the original columns which I don’t always want to do.
    Also noticed extract submenu on add column. I do this stuff a lot and have been duplicating columns and splitting. Nice!

    • Thanks – I’ll see what I can do about the gif. Regarding Merge, like a lot of transformations it’s available on the “Transform” and “Add Column” tab; on the former tab everything you do replaces data you already have with something new; on the latter tab you keep your existing data and a new column is added. I guess on the right-click menu you get the Transform functionality (which makes sense, I think) so if you want to keep your original columns you should go to the Add Column tab and click Merge there.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s