The biggest Excel event ever to hit Perth is coming on the 8th & 9th of August 2019. If you love Excel or want to make the most of the technology your organisation is already paying for then you cannot miss out on this.
Multiple MVP’s and Microsoft staff will be in attendance. Bringing together some of the greatest Excel minds including Oz du Soleil, Liam Bastick and from Perth Ian Huitson and our own Wyn Hopkins.
Be sure not to miss this one. Book your tickets here.
Access Analytic is once again happy to be involved in SQLSaturday. This event brings together Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence and Analytics.
Power Pivot basics plus a Power Query technique for handling awkward data
Something for everyone in this video, showing how to take awkward source data, transform it using Power Query and produce an interactive variance report in Power Pivot.
Power Pivot Basics plus Handling Multi Row Headings with Power Query - YouTube
The pre-built template we used is available for free from our website along other cool downloads and templates. They are all 100% free, no missing features & no time-expiry. Check out our free downloads and templates.
Power Query is an amazing tool for cleaning and transforming your data to make it ready for analysis in Power BI or Excel. Here are 5 useful hints to help you use Power Query more effectively:
Hint #1 – Use the Formula Bar
The Formula Bar helps you quickly understand which step you have previously referenced, which Power Query formula you are using, and whether certain information has been hard-coded into an applied step.
When you use Power Query for the first time, your Formula Bar may not be switched on by default. Go to the View menu and tick the “Formula Bar” box to make it appear.
Hint #2 – Rename Your Applied Steps
The default names that appear in the “Applied Steps” box often don’t tell the full story about what transformations you have done to the data. An applied steps box full of steps with names such as “Changed Type3”, “Renamed Columns2” and “Removed Other Columns4” can be difficult to understand and debug.
A better practice when performing query transformations is to double click each step and rename it as you go. This not only helps you identify extra steps that can be eliminated, but also tells a clearer story of what you have done to the data to assist you with performing more complex transformations.
Hint #3 – Add Comments in Your Power Query Code
You can add a comment to a line of Power Query code by right-clicking the applied step and selecting “Properties”. Add your comment to the “Description” box and then click OK to save your code. You can then view the code by going into the Advanced Editor.
Adding comments to your code is especially helpful when documenting complex transformations you are performing to a dataset, and can help other users understand what you did.
Hint #4 – Remove and/or Combine Steps
Power Query sometimes adds extra steps to your code automatically after creating a new column or splitting an existing column. These steps should be removed when necessary to simplify your code.
For example, when adding a new column, there is no need to have a separate step for creating the column, renaming the column, and then changing the column’s type.
Instead, create the column via “Custom Column” in the Add Column Menu, then edit the resulting code in the formula bar to change the name, calculation and column type. You can add a column type to this step by typing a comma and then the column type syntax before the last parenthesis in the formula bar.
The syntaxes for many common column types are below:
Hint #5 – Check Query Dependencies
To view relationships between your queries, as well as the different data sources that your queries rely on, go to View/Query Dependencies.
This window is very helpful for understanding complex data models featuring multiple queries that reference other queries, or that are merged with or appended to other queries.
If you have a bunch of Excel files that all have the same structure, you can easily pick a table name, sheet name or range name that exists in all of them and read that one item from all workbooks into a single table using Power Query.
It’s an incredibly useful tool for situations such as:
Regular dumps of data into files from other systems (e.g. ask your IT staff to run a script each night that dumps the day’s transactions into a file).
Data files that are regularly sent to you by your partners, customers, suppliers etc.
Excel templates that need to be completed by multiple people in different locations, then consolidated.
To get started, go to Data > Get Data > From File > From Folder and choose your folder as normal.
This works best if all the files in the folder you select have exactly the same format (i.e. the same columns).
Next, go to Edit (or “Transform Data” depending on your version).
You can then click the double arrow in the Content column and bingo!
Now you see a list of all your tables, sheets, and (if you keep scrolling down) all your range names too!
Choose the one you want, wait for a few seconds while Power Query does its magic, and then a table showing the values from all your files appears!
This works with all tables, sheets (provided they aren’t hidden), and range names (except dynamic range names that are generated using a formula).
The screenshots above were taken from Power Query in Excel but it works exactly the same way in Power BI Desktop.
We believe it’s important that business users of Excel understand what it’s truly capable of.
Only then can they make informed decisions about if/where to invest in new software, what staff training is required and how the business can be more efficient.
Excel is capable of dramatically more powerful things than most users are aware of.
Understanding Modern Excel
The term Modern Excel generally refers to the Power Query and Power Pivot functionality of Excel, but we like to think of it as everything that’s happened to Excel in the last 10 years, including Tables, new formulas, and cool features such as 3D Maps.
The video below aims to raise that awareness bar a little higher, plus flags an important concept when it comes to loading data into Power Pivot.
That concept is one of only importing the columns of data you need, especially those with a high level of uniqueness such as time, or database ID keys etc. These bloat your model and slow things down. The golden rule when building a Power Pivot model is only bring in what you know you need. It’s pretty easy to include extra data later when you discover you need it.
10 Million Rows of Data Loaded into Excel - YouTube
Want to learn more
Take the next step and access your data seamlessly today with Microsoft Excel in Perth. Take a course or contact us on
+61 8 6210 8500 or by email or leave your details below.
Financial Modelers spend most of their lives thinking about the future and imagining different scenarios for their clients.
But what about the future of financial modelling?
Can Artificial Intelligence Replace the Modeler?
There are some modelers who predict that artificial intelligence (AI) and machine learning will eventually become so clever that it will be able to produce a fully-functional financial model plus provide advice and analysis based on this.
Others share a less optimistic view of the abilities of technology, questioning whether a computer will be able to read a business plan, understand it, ask additional clarifying questions, translate all of this into a financial model that accurately represents the variables and their interrelationships, then produce scenarios and meaningful analysis.
Current AI and machine learning techniques have been quite successful in producing very clever predictive models when there’s a large amount of historical data to train and build the models. AI has also been successful in developing prescriptive models where it analyzes many potential outcomes and finds the optimal solution.
However, can a computer produce the kind of driver-based three-way financial model that modelers regularly produce, with multiple inputs, outputs and an analysis of the results under different scenarios, for both short and long-term timeframes with varying levels of detail and complexity?
Yet, unlike fossil fuels, data isn’t depleted when you use it and has a miniscule environmental footprint.
In fact, knowledge derived from data has enabled us to develop clean, renewable energy sources, which are inexhaustible and significantly healthier for us all.
The Foundation is Currently Missing
The first step towards greater AI involvement in financial modelling is to develop a language that can be used to describe financial modelling problems in a systematic way that a computer can understand and a human can verify.
To build a language that represents the knowledge and skill contained in financial modelers’ heads, and has sufficient flexibility and robustness to allow for all the different permutations, functionalities and flexibilities required in financial modelling is undoubtedly a difficult process that has not been achieved to date.
Only a few years ago, tasks such as this would have seemed impossible, but who would have thought AI could diagnose patients, drive a car, or recognize faces in photos!
What is unachievable today might be achievable tomorrow.
If this occurs, AI-based financial modelling could be developed in a similar way to other expert systems that ask a series of intelligent, structured questions, then produce their results.
Building on the Foundation
If the foundation was in place, some of the AI-based financial modelling solutions we might see could include the following:
Goodbye Spreadsheet Drudgery
As a first step, financial modelling process automation is already available to assist the financial modeler to develop their models much faster. Rather than the modeler constructing all the various formulas required for a financial model, software does the bulk of this work.
Software such as Modano, automates the creation and maintenance of the basic model framework, allowing the modeler to focus on customizing the model and other value-added activities.
As these systems develop, they could evolve from process automation into AI-based financial modelling, where the AI “understands” what it is modelling and provides suggestions, asks clarifying questions, and interprets the results based on its knowledge of previous models.
When aided by AI, the modeler becomes even more productive and efficient because the combination of Human + AI is much more powerful than either on its own. AI removing the tediousness of model construction is like when spreadsheets removed the tediousness of having to do hundreds or thousands of manual paper-based calculations.
If this occurs, the financial modeler’s role shifts from an emphasis on constructing financial models to primarily focusing on advising and interpreting these – even more than occurs today.
For relatively simple financial models, an accountant, CFO or other business user may be able to use an AI-based system to produce the models they need without the assistance of a specialist financial modeler, using something like a more intuitive version of Castaway or Invest for Excel.
AI may also be able to create millions of models, then iterate and optimize these to see which ones produce the best results.
It could explore far more options than what a human modeler could consider and may then potentially produce some interesting and useful insights that would not have been discovered otherwise.
Looking even further ahead, the AI model could also be setup to continually absorb new sources of data and thereby provide continuously optimized analysis.
There could well be many other ways AI could assist or automate financial modelling.
What could an AI-based Model look like?
An AI-based financial modelling system need not be restricted to using spreadsheets. It could use another system entirely that is better suited to modeling and is more easily generated by the expert system. Of course, basing it on another platform is likely to cause significant resistance with existing modelers as virtually every financial modeler currently uses spreadsheets.
Regardless of the platform, the model produced would still need to be transparent and auditable so someone could review it and verify the logic. A “black box” approach is unlikely to be successful because the user may wish to understand how the results are obtained.
We are probably still some way off developing a suitably structured language for describing financial modelling problems, and even further away from building an AI-based financial modelling expert system using this language.
However, if (or when?) these are developed and become widespread, financial models are likely to change significantly. Existing financial modelling process automation systems may form the foundation for these.
The future financial modeler will need to have even greater skills in business analysis and advice, but perhaps there will be less requirement for them to possess a comprehensive knowledge of Excel formulas.
Financial modelers today should embrace AI as it has the potential to make them more productive and to make the financial models they produce more reliable, more valuable and better-structured.
Like financial models, there are many potential future scenarios!
Quite simply Microsoft Power Query is an amazing part of Excel and Power BI that everyone should know about.
Plain Speaking: Power Query is the worlds’ greatest washing machine! Get all of your “dirty” data from any location, clean it up via a user friendly interface and then load it all neat and folded to a destination of your choice (Excel or Power Pivot)
In Excel 2010 and Excel 2013 Power Query was a an add-in. In Excel 2016 Power Query was built in to the Data tab in Excel and re-named Get & Transform.
Technical Talk: Power Query is a powerful ETL utility built into Excel 2016 designed to Extract data from multiple sources, Transform the data into a layout suitable for analysis and then Load it into Excel Tables or Power Pivot.
Want to learn more
Take the next step and access your data seamlessly today with Microsoft Power Query in Perth. Take a course or
contact us on +61 8 6210 8500 or by email or leave your details below.