Remove Other Columns – removes all columns except those selected. Remove Columns – removes the selected columns. The same could be achieved by clicking the mouse on a column header. This is not really a transformation as it selects a column in the preview window without adding any transformation steps into the query. Go To Column – selects a specific column. The Manage Columns section contains two icons, each including two further options.Ĭhoose Columns – selects the columns to be retained in the query We are only looking at transformations in this post therefore, we will only cover the Manage Column, Reduce Rows, Sort, Transform and Combine sections of the Home menu. The Home tab contains query and application settings (e.g., Refresh Preview), along with common transformations. Without further ado, let’s start looking at what is available to us. And, you may have icons that I don’t, and vice versa. But this should provide you a suitable start.Īs Power Query is continuously updated, it is possible the screenshots may appear slightly different from your version of Excel. For example, Use first row as headers is available in the Home > Transform section and the Transform > Table sections of the ribbon.Īs you work with Power Query, you will likely encounter other menus not covered in this post. Therefore, since some transformations could be categorized in various places, the buttons are included in all those locations. This is because the UI designers have tried to group similar items. One confusing part about the menus is that the same action appears in multiple places. For example, if you select a column with a text data type, the date and number related transformations are not available. Depending on the item selected, menu options appear, disappear or be disabled accordingly. Microsoft made Power Query’s menus contextual. However, by the end of this, you will have hundreds of easy Power Query transformations in your toolkit. Also, more complex Power Query transformations are worthy of more focus and included in future posts in this series. Unfortunately, we can’t cover everything in this post there are just too many options available. In this post, we look at the simple Power Query transformations available from the main menus. We’ve looked at a few Power Query transformations in this series, but now we are going much further.

Power query excel 2019