Copyright 2019-2020 Workbook Tools, LLC

Search

Why WBmerge if I have Power Query?

Updated: Jul 20, 2019

Why would I invest in WBmerge if I have access to Power Query in Microsoft Excel?


First off, Power Query (renamed to Get & Transform in Excel 2016 and beyond) is a very powerful tool built into some versions and editions of Microsoft Excel. The Power Query M formula language is optimized for building highly flexible data mashup queries. You can do a lot with M code, but it takes knowledge and expertise.


WBmerge was not designed to provide all the capabilities of Power Query, but was rather created to provide users a powerful, yet easy option for merging multiple Excel workbooks with multiple worksheets.


In our testing with Power Query without using any M code, we could not find an easy way to combine multiple worksheets from multiple Excel workbooks in a single step. Power Query offers the ability to combine all like files within a given folder, but when those files are Excel workbooks you may only pick one worksheet to combine and it pulls across all the columns for the selected worksheet. WBmerge allows you to select any worksheets you require and select only the columns you want from each worksheet. WBmerge allows the user to control if data elements can be null or not.


WBmerge has user defined header row selection between row 1 and 100. Users can easily set header rows at the individual worksheet level or set them to the same value across all worksheets.


The ability to perform partial column and partial worksheet matching is another benefit of WBmerge. Users can also perform a Validate Only on their source workbooks without performing a merge, this is a great feature when source workbooks arrive over a period and you wish to make sure there are no errors in the workbooks as they come in. Once all your source workbooks are in hand you can now perform your merge, and if you validated as workbooks arrived, you know your merge will be successful.


WBmerge also offers the ability to easily create templates that include your worksheet, column and null status selections for easy reuse the next time you run the same merge.


If you are a Power Query expert, then WBmerge may not be for you. But if you are like most Excel users out there who just want to use Excel to filter, graph, pivot and sort data, and you are faced with combining multiple Excel workbooks together with multiple worksheets, then we hope you will look at WBmerge.