… and Power BI
Many don’t realize there is an extremely powerful tool for connecting to various data sources and transforming the data before it reaches your workbook. Meet Power Query. This is the same tool that is used in Power BI and we can certainly help with that as well.
Your workbook will be leaner and faster as the calculations done in power query uses about 10 % of your PCs resources compared to ordinary functions or macros. The user friendly interface allows non coders to write fairly complex queries themselves. The interface is comparable to recording a macro as dragging and dropping will create a code in the background (in M-language) but much easier to use. Combining your query with power pivot gives you an extremely powerful analysis tool.
So why would you need us? Well maybe you don’t, give it a try under the ‘Data’ tab and ‘Get & Transform Data’section. You can do a lot just using the query wizard. However, if you need more complex code that the wizard cannot provide or perhaps joining different queries and creating functions becomes too complicated then let us help you. Maybe you simply have more important tasks than learning new Excel functionality.
Perhaps you repeat a time consuming task every day, week or month were you need to source data and make calculations. Wouldn’t it be nice to have a model that does this for you?
A common request is to connect to and combine several sources and join them into one table (like SQL) and then transform the data using different functions and user specified parameters linked to cells in a workbook. The data is finally analyzed with power pivot where additional functionality can be added.