If you use Excel 2010 and Power Query, there is some good news.
Microsoft just released the new version of Power Query – download and it from here. There are many more features added to this new version… here is a quick review.
This is not a detailed article on using Power Query. It is just a quick blog on the new update. I will cover Power Query in greater detail in the upcoming articles.
This version of Power Query is 2.10.3598.81
There are some nice new things added. I am going to briefly discuss some of the new features.
Insert Tab in Query Editor
This is a new addition. The tab is very useful for adding many things.
Adding custom column
This is not a new feature. It is just more accessible. To add a new column, you can create a formula using this dialog.
This dialog checks formula errors while you edit the formula.
Insert Index Column
This adds a new column with serial numbers starting with zero. When more rows are added as a part of the refresh operation, the index number is automatically incremented.
For deleted rows, the index is recycled. In short, the index will always have serial numbers with no gaps.
Does what is expected – adds a duplicate column based upon the currently selected column. Remember, this is a live copy. That means, if at a later date, the source data changes, the duplicate column data will also change. It is not a snapshot copy.
The M function used is:
= Table.DuplicateColumn( <Table> , <Original Column Name> , <Duplicate Column Name>)
Why would you want to have a duplicate column? Frankly, I could not think of an answer. Tried to search but no results. If you find the answer, please post it as a comment.
Useful feature for text concatenation. Select multiple text type of columns and choose Merge Columns. You can choose the delimiter. Using custom option, you can actually give not one but multiple characters as the delimiter.
M language equivalent : Table.CombineColumns
Combine using Sum or Product
This could have been done using a custom column, but it is a quick way of combining numeric columns. Select multiple columns and choose Sum or Product. Saves you the trouble of typing long formulas especially if you are combining many columns.
Power Query for SAP Business Objects
This is another new addition to the data sources of Power Query – Business Objects Universe. You can download this preview edition from here. The version number is different. Try this if you already SAP Business Objects in your environment. This was released on 3rd March during the SharePoint Conference.
Power Query related feedback
If you have any suggestions or if you find any bugs, you can send a mail to firstname.lastname@example.org. I have interacted with this team before. They are very prompt in responding and extremely helpful.