PowerPivot allows you to pivot columns of data from different tables and sources against each other. You may recall when creating pivot tables in standard Excel, one would often use Vlookups to pull all the data into one table and then create a pivot table. This is no longer needed with PowerPivot because all the […]
When you see Power Pivot for the first time it can be confusing. It’s a new interface, with new ribbons and views. Before you start working with tables of data and complex models, you need to get comfortable with the user interface. In this article, we will take a look around Power Pivots user interface […]
How to Reconcile Accounts using Power Query! What? Did I hear you right? Reconciling accounts is a common task carried out by accountants on a regular and reoccurring basis. Creditor statements and bank accounts are examples of accounts that need regular reconciliation. But what if you could create a model, within minutes, that you could […]
For years now Accountants and bookkeepers around the globe have used Excel as their Spreadsheet tool of choice. It offers massive flexibility, speed and is as accurate and reliable as the users. But to this day, some Excel users never go beyond the basic totting and cross totting of rows and columns. Excel has come […]
Excel is a powerful tool that allows you to connect to JSON data and read it. However sometimes this data might require a little manipulation to be fully understood and analysed in Excel. In this article you will learn What is JSON Data How to Connect to JSON data from Excel How to Parse simple […]
For some time now I have wanted to explore connecting Power BI with API’s, however the lack of knowledge of M, and time has held me back. But this week I got a chance to dig a little deeper with my idea. I decided to look at Google’s YouTube Data API. First I just want […]
Excel is one of those tools that you just can’t live without. It is used to store information, run calculations, carry out analysis, prepare report, visualizations and much more. What’s just as important is the flexibility it gives to its +750 millions of users. This flexibility allows users create their own calculations and analysis, in […]
I previously published an article and video ‘Excel Power Trick – Save time without VBA’ which seems to have been very popular. In this video we looked at using Power Query to get data from Excel sheets, format that data and then save the query for future use. It’s amazing how much time you can […]
Learn How to Manage Many to Many Relationships in Power Pivot Power Pivot has been around since 2010, and many people still struggle with managing relationships. Even more so most struggle with more complex relationships such as Many to Many relationships. In fact, I have seen many people begin to set up data models in […]
How often have you seen an Excel spreadsheet like the one below? Very often, right? I am sure many of you are even saying ‘ What’s the problem with that spreadsheet?’ But I know that some of you can spot the problems straight away. This matrix style reporting is common in Excel. It contains simple […]