The post Are Your Dashboards Actually Helping You Make Better Decisions? appeared first on Access Analytic.

The post Access Analytic Becomes a Xero Developer Partner appeared first on Access Analytic.

The post Streamline Your Budgeting & Forecasting in 2026 with Solver appeared first on Access Analytic.

Today, CFOs are expected to do far more than manage numbers. They are strategic leaders, digital innovators, and trusted advisors navigating uncertainty while driving growth. The CFO Access Podcast was created to meet this challenge head-on, offering real conversations with respected CFOs, industry experts, and thought leaders who understand what it takes to thrive in this evolving landscape.

Each 30–45 minute episode dives into the most pressing themes shaping modern finance, including:

• Technology and Finance Transformation (AI, analytics, data-driven finance)
• CFO Leadership Insights (soft skills, leading teams)
• Peer Success Stories and Industry Interviews
• Strategic Planning & Business Growth
• Major Market and Industry Trends
• Shared Insights

Meet Your Host

Jeff Robson Founder and CEO of Access Analytic

With over 25 years of experience working alongside CFOs, CEOs, and executive teams, Jeff is known for his clear, practical approach to financial modelling, data strategy, and analytics. His natural curiosity and conversational style bring out thoughtful, candid discussions that go beyond theory to deliver tangible value.

“The role of the CFO is changing faster than ever,” says Jeff Robson. “This podcast is about creating a trusted space where finance leaders can share what’s really working, learn from each other, and gain insights that help them lead with confidence.”

The CFO Access Podcast reflects the same ethos as the CFO Access community: a peer-driven, invite-only network that prioritises relationships, shared knowledge, and professional growth over self-promotion.

The first episodes are now live, with new episodes released fortnightly to provide finance leaders with ongoing, relevant conversations without overwhelming their schedule.

Ready to Dive In?

Hear from Australia’s top CFOs and finance executives by exploring all episodes as they’re released. With exclusive insights, you can hear from CFO leaders about their experience within their roles, and what we can expect to see in the coming years.

Explore the CFO Access Podcast

The post CFO Access Podcast Officially Launches | Real Conversations for Today’s Finance Leaders appeared first on Access Analytic.

Common roadblocks such as version control issues, inconsistent data, and limited visibility across departments make the process stressful and prone to error. That’s where Solver, a modern Corporate Performance Management (CPM) solution, changes the game.

Common Budgeting and Forecasting Roadblocks

Traditional spreadsheet-based budgeting and forecasting can hold businesses back. The most common challenges we see include:

• Manual data entry across multiple spreadsheets, increasing the risk of errors.
• Limited visibility across different business units, making it difficult to see the bigger picture.
• Slow turnaround times when updating assumptions or responding to new information.
• Lack of confidence in forecasts, leaving leadership teams uncertain about the decisions they’re making.

Heading into a new financial year with these roadblocks in place creates unnecessary pressure. Businesses need a more effective, reliable approach.

What Solver Brings to the Table

Solver is designed to help finance teams work smarter, not harder. By moving away from manual spreadsheets and adopting a unified platform, you can:

• Streamline Budgeting | Automate repetitive processes, eliminate duplication, and consolidate data into a single, consistent source.
• Improve Forecasting Accuracy | Work with real-time data updates and predictive capabilities to strengthen decision-making and reduce surprises.
• Enhance Collaboration | Give both finance teams and business leaders access to one platform, ensuring alignment and visibility across the organisation.
• Gain Deeper Insights | Solver integrates seamlessly with Power BI, delivering dashboards and analytics that highlight trends and opportunities at a glance.

Real-World Impact | Nexus Success Story

When Nexus, a disability services provider in Tasmania, needed to strengthen their reporting, they partnered with Access Analytic to implement Solver.

By consolidating multiple business units into a single, user-friendly platform, automating tedious processes, and delivering real-time dashboards, Nexus gained greater visibility and confidence in their financial data. Today, decision-makers across the organisation are empowered with timely, accurate insights, helping them plan with clarity.

Read More Here

Why Now is the Right Time

As the financial year-end approaches, businesses face critical deadlines. Budgets must be finalised, forecasting processes are pushed to their limits and leaders are preparing strategies for FY25 and beyond.

Adopting Solver now doesn’t just ease the burden of this year’s process, it sets the foundation for long-term success. With scalable processes, accurate insights, and improved collaboration, Solver helps organisations move from reactive to proactive planning.

Transform Your Budgeting & Forecasting for The Year Ahead

Budgeting and forecasting don’t need to be a painful process. Solver can transform the process into something faster, more accurate, and more strategic.

Get in touch with our team at Access Analytic to discuss how Solver can fit into your business and make life easier!

The post Budgeting & Forecasting with Solver | Preparing for 2026 appeared first on Access Analytic.

The findings highlight both the opportunities and challenges finance teams face, and provide a valuable benchmark for CFOs considering their next steps.

How Finance Teams are Using Data & AI

While the potential of data-driven decision-making is widely recognised, 64% of respondents indicated their organisations still have room for improvement in the way their organisation uses data. The top challenges for finance teams include:

• Integrating multiple systems (63%)
• Lack of data skills (37%)
• Fragmented data (32%)

When it comes to AI adoption, mid-sized organisations are leading the way:

• 80% are already using, experimenting with, or planning to adopt AI within the next 1–2 years.
• The biggest barriers include privacy concerns (28%) and a lack of expertise (27%).
• Interestingly, mining companies in WA were among the least likely to adopt, with 67% reporting no plans to implement AI.

For those already using AI, the main benefits are clear, to enhancing productivity (55%), and to generating insights (40%).

Technology Investment & Priorities

Investment in finance technology is strong and growing, with 89% of organisations expecting to increase their finance technology budgets in the coming year. The drivers behind this vary by business size:

• Larger organisations are motivated by security concerns and external pressures (both at 50%).
• Smaller organisations are focused on return on investment (53%) and the increasing ease of use of modern tools (33%).

When asked where automation is most likely to be applied, finance teams pointed to reporting and month-end processes (30%), and budgeting and forecasting (17%). Meanwhile, Environmental, Social and Governance (ESG) reporting remains a developing focus, with 43% of respondents saying it is still not a priority.

Recommendations for CFOs

Based on these findings, there are clear steps CFOs can take to ensure they stay ahead:

• Evaluate and adopt the right tools to improve productivity, provide a competitive advantage, and enhance decision-making.
• Embrace AI in areas like forecasting, reporting, anomaly detection, and risk analysis.
• Overcome perceived barriers such as privacy concerns and lack of skilled resources – solutions and external expertise are available.
• Maintain a focus on ROI, but balance this with a long-term perspective to avoid falling behind.
• Be proactive with ESG reporting – while it may not yet be mandatory for all, it will be a growing requirement in the near future.

Final Thoughts

The 2025 CFO AI & Technology Insights Survey highlights a clear message – CFOs must be proactive in leading digital adoption if they want to remain strategic, data-driven, and commercially focused leaders.

Digital transformation is not just about adopting technology, it’s about aligning investment, mindset, and execution with an organisation’s level of digital maturity.

To explore the findings in more detail, we invite you to download the free Executive Summary. For those seeking a deeper dive, the full report, interactive Power BI dashboard, and advisory services are also available.

Download the Executive Summary

The post Key Insights from the 2025 CFO AI & Technology Survey appeared first on Access Analytic.

In this practical step-by-step video, Wyn shows you exactly how to configure Copilot for Power BI, covering everything from setup to user management.

In this video, you’ll learn how to:

• Monitor usage and billing for Copilot capacity
• Enable Copilot features in Power BI and Fabric
• Purchase and assign Power BI or Fabric Capacity
• Assign users and manage workspace permissions
• Understand the difference between Copilot by workspace vs standalone

This guide is ideal for Power BI admins, IT managers, and teams deploying AI-powered reporting tools across their organisation.

Watch the Full Copilot Setup Tutorial Here

Want help configuring your environment or rolling out Copilot to your team? Contact us to learn more.

The post How to Set Up Copilot Capacity in Power BI & Microsoft Fabric appeared first on Access Analytic.

A new Auto Refresh feature, currently rolling out in select beta versions, automatically updates your Pivot Tables when your data changes. That means no more right-click > refresh every time your source data is updated or extended.

In this quick tutorial, Wyn covers:

• How to enable or disable auto refresh using PivotTable Analyse
• Setting auto refresh at a workbook level
• Understanding pivot caches and how they affect refresh settings
• Why copying pivot tables doesn’t mean you can toggle them independently (unless you use separate data sources)
• Limitations and quirks of the current beta rollout

Whether you’re managing large data sets or building reports for clients, this new functionality is a great way to cut down on repetitive tasks and avoid refresh-related errors.

Watch Here!

The post Excel’s New Auto Refresh Pivot Tables: What You Need to Know appeared first on Access Analytic.

With Copilot, you can:

• Automate internal processes
• Deliver precise, contextual responses
• Guide new employees through onboarding
• Generate real-time reports based on live and historical data

It integrates seamlessly with SharePoint, Teams, and internal systems, turning everyday questions into meaningful action, all without exposing your data to third-party risk.

One of the best parts? – Your data stays safe within your organisation’s environment.

In our latest tutorial, Copilot specialist Matt walks through live examples including how Copilot handles leave requests and real-time report generation—pulling from backend data and contextualising it with past trends to deliver accurate, actionable insights.

Watch the full demo to see how Copilot transforms everyday operations.

Watch the Full Video to See How it Works in Action

The post How Copilot Streamlines Your Business Operations appeared first on Access Analytic.

You can use it to:

• Research information
• Enrich your CRM
• Generate tailored cold emails from your existing data (aka. No more generic outreach!)

This tool turns manual, time-consuming tasks into quick wins. It’s a smarter way to personalise your communications and boost your team’s efficiency, without leaving Excel.

Watch the Full Video to See How it Works in Action

The post How You Can Use Generative AI in Excel to Work Smarter appeared first on Access Analytic.

You could sift through it manually, chasing insights. Or, you could let AI do the heavy lifting.

Enter…The AI Lead Agent

In this quick video, Jeff (our Managing Director) shows how an AI-powered assistant can transform how you work with leads. As soon as a new lead enters your CRM, the AI Lead Agent gets to work. It can:

• Research the lead’s company
• Find out how many employees they have
• Score the strength of the lead
• Recommend exactly how your business can help

The result? Smarter insights. Less guesswork. More time focused on leads that actually matter.

 Watch the Full Video to See How it Works in Action

The post What Happens When You Supercharge Your CRM with AI? appeared first on Access Analytic.

But, how do you know if you’re ready to adopt AI?

Foundation 1 | How’s your Data?

Firstly, it’s essential to assess your organisation’s data infrastructure. AI thrives on data, and having a robust data management system is crucial. This includes ensuring data quality, accessibility, and security. Companies must evaluate whether they have the necessary data to train AI models effectively and if their data governance practices are up to par. Additionally, it’s essential to consider the scalability of your data infrastructure to accommodate the growing demands of AI applications.

Foundation 2 | Culture Eats Data for Breakfast

More importantly, however, your organisation’s culture must be open, and your workforce must be ready. Implementing AI requires a skilled team that understands AI’s technical and strategic aspects. This involves investing in training and development programs to upskill employees and hiring experts who can lead AI initiatives. Moreover, it requires a culture of innovation and openness to change rather than guarding silos and building empires. Employees must be open to embracing AI and exploring its potential benefits, not just fearing it as a job threat.

Only once you have the right foundation are you ready to consider implementing AI initiatives.

So, what kinds of things should you be considering?

Here are some examples:

1. Enhancing Customer Experiences

AI enables businesses to deliver personalised customer interactions by analysing vast datasets to understand individual preferences and behaviours. Machine learning algorithms can predict customer needs, allowing for tailored product recommendations and targeted marketing campaigns. This level of personalisation enhances customer satisfaction and fosters brand loyalty. For instance, AI-driven chatbots and virtual assistants provide real-time support, addressing customer queries efficiently and improving overall service quality.

2. Streamlining Operations through Intelligent Automation

Intelligent automation (IA), which combines AI and robotic process automation (RPA), is revolutionising operational workflows. By automating repetitive and time-consuming tasks, businesses can reduce human error and free up employees to focus on more strategic activities. Applications of IA include data entry, invoice processing, and supply chain management, leading to increased efficiency and cost savings.

3. Predictive Maintenance in Manufacturing

In the manufacturing sector, AI-powered predictive maintenance is a game-changer. By analysing data from equipment sensors, AI systems can predict potential failures before they occur, allowing for timely maintenance and reducing downtime. This proactive approach extends the machinery’s lifespan, optimises production schedules, and reduces maintenance costs.

4. Advanced Fraud Detection

Financial institutions are leveraging AI to enhance fraud detection capabilities. Machine learning models analyse transaction patterns in real time, identifying anomalies that may indicate fraudulent activity. This allows for swift action to prevent fraud, safeguarding assets and maintaining customer trust. Additionally, AI aids in credit scoring by assessing a broader range of data points, leading to more accurate risk assessments.

5. Optimising Asset Management

AI assists businesses in ensuring their assets operate at peak efficiency. By quickly adopting new technologies and aligning operations with corporate goals, companies can maximise the value derived from their assets. This includes optimising resource allocation, improving asset utilisation, and enhancing decision-making processes related to asset management.

6. Transforming Sales Strategies

Sales teams are utilising AI to optimise conversions by personalising interactions and automating administrative tasks. AI analyses customer data to provide insights into buying behaviours, enabling sales professionals to tailor their approaches effectively. Automation of routine tasks allows sales teams to focus on building relationships and closing deals, thereby increasing productivity and revenue.

7. Facilitating Strategic Decision-Making

AI supports strategic decision-making by providing data-driven insights. Advanced analytics and machine learning models process complex datasets to identify trends, forecast outcomes, and recommend optimal strategies. This empowers executives to make informed decisions that align with organisational objectives and market dynamics.

Conclusion 

The integration of AI into business operations is no longer optional but a strategic imperative, providing you have the right foundation in place.

By embracing AI, companies can drive innovation, enhance efficiency, and maintain a competitive edge in the marketplace. As boards continue to challenge their leadership teams to explore AI applications, the focus should be on identifying areas where AI can deliver the most significant impact and implementing solutions that align with the organisation’s goals and values.

At Access Analytic, we specialise in helping businesses unlock the potential of AI using our Access Analytic AI framework. This tailored methodology ensures that AI solutions are aligned with your specific industry challenges, operational needs, and strategic objectives.

If you’d like to explore how AI could be applied in your business, get in touch with us today. Let’s work together to get your data right then identify high-impact opportunities and implement AI-driven solutions that deliver real business value.

The post AI | How do we Know if we’re Ready? appeared first on Access Analytic.

AI Personalisation | Is it Killing Customer Trust?

AI-driven personalisation is often marketed as a way to enhance customer experience, which it does incredibly well in many areas. But it can backfire too. Consumers are becoming increasingly aware of how much companies know about them, and AI’s ability to predict their behaviour can raise concerns about privacy and manipulation.

Take AI-powered recommendations—while they boost short-term engagement, they also contribute to the echo chambers that surround us and limit our choices too. Sometimes, I don’t know what I want until I see someone else’s search results!

More importantly, when AI-driven decision-making leads to biased outcomes, such as unfair credit approvals or discriminatory hiring practices, customers, employees, and/or suppliers are disadvantaged and companies that fail to address these concerns risk damaging their reputation.

Business leaders need to be aware of these issues and ensure their plans address these concerns.

AI and Efficiency | A Cost-Saving Illusion?

Proponents argue that AI optimises operations and improves efficiency, which it undoubtedly does an amazing job of … but have you considered the implementation costs? AI-driven automation isn’t a magic wand—it requires investment, staff training, change management, data infrastructure, and ongoing maintenance.

Moreover, AI is only as good as the data it accesses. Businesses that rely on outdated, incomplete, or biased data could end up making worse decisions, not better ones. Take predictive analytics: while AI can forecast trends, it often struggles with unexpected market shifts, as seen during the COVID-19 pandemic when AI-driven supply chains collapsed due to unforeseen disruptions.

Instead of blindly adopting AI for efficiency, business leaders should ask: Is AI actually solving a problem, or are we just deploying it for the sake of innovation?

AI’s Dilemma | Is it Replacing or Enhancing Employees?

Many executives see AI as a way to cut costs by automating jobs, but what happens if entire industries are disrupted, leaving thousands unemployed? AI-driven automation is already automating many tasks in finance, legal, and HR. Does this simply make employees more productive, or does it make them redundant, raising ethical and social questions that businesses cannot ignore?

Who will buy your products if consumers no longer have jobs? Governments could respond with new regulations or taxation on AI-driven businesses to make widespread automation less attractive but unless this is implemented worldwide (unlikely!), the businesses in that country would be operating at a significant disadvantage.

Rather than focusing solely on cost-cutting, business leaders should ask: What role should AI play in a sustainable workforce strategy? How can we use AI to make our workforce more productive and make roles more interesting and rewarding?

Regulatory Risks | Is There a Ticking Time Bomb?

AI adoption is currently outpacing regulation, but that won’t last forever. As governments around the world introduce stricter AI laws—such as the EU AI Act—companies that rushed into AI without considering compliance may find themselves facing hefty fines or legal battles.

Bias in AI decision-making, data privacy violations, and opaque algorithms could all become major liabilities. Just look at how tech giants like Google and Amazon have faced legal scrutiny for their AI systems—and smaller businesses won’t be immune.

When considering AI initiatives, business leaders must consider: Are we prepared for the regulatory risks that come with AI, or are we setting ourselves up for future lawsuits?

Final Thoughts | To AI, or Not to AI? That is the Question!

AI is not inherently good or bad—it’s a tool. But rushing to adopt AI without fully understanding its limitations, ethical concerns, and long-term consequences is a mistake many companies will regret. CEOs and CFOs need to take a step back and ask tough questions before investing in AI:

• Does AI actually solve a problem, or is it just a buzzword we feel pressured to adopt?
• Have we considered all the costs, risks, and ethical implications?
• How will AI adoption affect our workforce and regulatory exposure?

If you’re looking to implement AI in a way that is both strategic and responsible, we invite you to get in touch. Instead of blindly following the hype, let’s work together using the Access Analytic AI Framework to build a plan that actually works for your business. Get in touch to discuss how AI fits into your specific situation—without the buzzwords and empty promises.

The post AI in Business | The Questions Every Leader Needs to Ask appeared first on Access Analytic.

What is Deneb?

Deneb is a declarative visualisation tool built to integrate with Power BI, allowing you to create sophisticated, custom visuals that go beyond the built-in options. It enables you to leverage the power of Vega and Vega-Lite, two powerful visualisation frameworks, by embedding them directly into Power BI reports. Through Deneb, you can define the appearance and behaviour of your visuals with precise control using declarative JSON code.

With Deneb, you can create dynamic, interactive visuals that are fully customisable, opening up new possibilities for data storytelling and enhancing decision-making processes. Whether you’re looking for a simple chart or a complex, multi-layered visualisation, Deneb gives you the flexibility to tailor your visuals to meet your specific needs.

How Does Deneb Work?

Deneb integrates seamlessly into Power BI, allowing you to embed advanced Vega/Vega-Lite visualisations directly into your reports. By writing declarative JSON code, you can define how your visualisation should look and behave, from its colour scheme to its interactivity features. This process offers a level of customisation that traditional Power BI visuals simply can’t match.

The beauty of Deneb lies in its flexibility. Instead of relying on predefined templates, you have the ability to create visuals from the ground up, making it possible to develop truly unique, insightful representations of complex datasets. Whether you need a simple bar chart or a complex network graph, Deneb ensures your visuals are built exactly as you envision.

Key Features and Benefits of Using Deneb for Power BI

Customisability | Deneb gives you complete control over the appearance of your visualisations, enabling you to create tailored visuals that perfectly align with your organisation’s design language. The ability to control every aspect of the visual means you can ensure consistency across all reports and dashboards, which enhances the overall user experience.

Advanced Charts | Unlike traditional Power BI visuals, Deneb supports complex visualisation types such as network graphs, heatmaps, and custom axis scales. These advanced charts allow you to dive deeper into your data, helping you reveal insights that might otherwise be hidden. With Deneb, you can craft sophisticated visuals that provide a clearer understanding of complex datasets.

Data Transformation Capabilities | Deneb integrates with Power Query, ensuring your visualisations are built on clean, accurate data. The ability to preprocess your data before visualising it allows you to refine your datasets and improve the clarity of the insights you present. With Deneb, data transformation is streamlined, reducing the chances of errors and enhancing the quality of your visuals.

Interactivity | One of Deneb’s standout features is its ability to create interactive, user-driven visualisations. This functionality empowers end users to explore data dynamically, adjusting parameters in real-time to uncover new insights. By enabling interactivity, Deneb enhances decision-making capabilities, allowing users to make more informed choices based on real-time data.

Enhanced Flexibility with Vega-Lite | Vega-Lite offers greater flexibility in designing a wide range of visualisations. With Deneb, you can create highly specialised charts and graphics that go beyond what’s possible with standard Power BI visuals. This flexibility allows you to tell your data’s story in a more impactful way, giving you a competitive edge in how you present complex data insights.

Declarative Code for Greater Control | Deneb’s use of declarative JSON code means you have full control over how your visuals behave. By defining the logic and design in code, you can create precise, high-quality custom visuals that represent your data accurately and consistently. This level of control ensures that your reports communicate insights in a way that standard Power BI visuals simply can’t match.

Cost-Effective and Open-Source | One of the biggest advantages of Deneb is that it’s an open-source tool, meaning it’s available for free. Unlike many advanced visualisation tools that require expensive licenses, Deneb provides access to cutting-edge visualisation capabilities without any additional costs. This makes it an affordable solution for businesses looking to elevate their Power BI reports without breaking the budget.

Getting Started with Deneb in Power BI

Ready to unlock the full potential of your data with Deneb? Getting started is easy, and there are plenty of resources to guide you. We highly recommend checking out Wyn & Matt’s YouTube video for a detailed walkthrough on how to integrate Deneb into your Power BI reports. You can watch it here – How to create your own Power BI visual using Deneb.

If you’d prefer a more hands-off approach or need assistance in implementing Deneb for your organisation, our expert team is here to help. Reach out to our friendly team to discuss how we can help you create custom, interactive, and dynamic Power BI reports that drive better business decisions.

The post Unlock the Power of Data with Deneb in Power BI appeared first on Access Analytic.

1. What is Solver, and how can it help my business?

Solver is a cloud-based financial planning and analysis (FP&A) platform that integrates seamlessly with Microsoft Excel. It allows users to automate budgeting, forecasting, and reporting processes, making it easier to manage financial data and collaborate across teams. Whether you’re a small business or a large enterprise, Solver helps improve accuracy, reduce errors, and save time.

2. How does Solver integrate with Excel?

Solver integrates directly with Excel, making it easy for users to continue working in the familiar environment of Excel while taking advantage of Solver’s powerful features. You can use Solver’s reporting and forecasting tools right within Excel, and all your data is synced in real time, ensuring consistency and accuracy.

3. Are there benefits of using Solver for financial planning?

Absolutely, several benefits of utilising Solver for financial planning include:

• Improved Collaboration | With cloud-based access, multiple users can work on the same financial models and reports simultaneously.
• Error Reduction | Solver’s automation tools help eliminate manual data entry errors and improve the accuracy of your financial statements.
• Dynamic Reporting | Create customisable reports that update in real time as data changes, allowing for better decision making.
• Seamless Integrations | Solver integrates with various ERP and accounting systems to bring in data automatically.

4. Can Solver handle multiple currencies and departments?

Yes! Solver is equipped to handle multi-currency support, making it easy for global businesses to manage financial data in different currencies. You can also set up departments, regions, or other dimensions within Solver to track specific cost centres or business units separately, giving you a clearer view of your organisation’s performance.

5. How does Solver handle running totals and dynamic formulas?

Solver makes it simple to work with running totals and dynamic formulas, especially when using ROWABOVE and other functions that improve the efficiency of financial calculations. For example, using ROWABOVE instead of OFFSET ensures that your formulas remain stable, even when you insert or delete rows in your spreadsheet.

6. How do I set up Solver for my business?

Setting up Solver for your business involves defining your budget categories, linking your data sources (such as ERP or accounting systems), and configuring your reports. Solver’s intuitive interface and customer support team can guide you through the setup process, making it straightforward to get started.

7. Does Solver offer training or support?

Yes, Solver offers a variety of training resources, including online tutorials, webinars, and one-on-one support. Their team is available to assist with anything from initial setup to advanced features, ensuring that you can fully leverage the platform.

8. What’s the difference between Solver and other FP&A tools?

Solver stands out for its Excel integration, which many users prefer because it allows them to work within a familiar environment. Plus, Solver’s cloud-based platform ensures that data is accessible in real-time across teams, providing greater flexibility than traditional on-premise solutions. Solver also offers excellent customer support and customisable templates to fit the needs of various industries.

9. Can Solver help with forecasting beyond budgeting?

Absolutely! Solver’s platform is designed to handle both budgeting and forecasting. By automating data updates and providing real-time visibility into your financials, Solver makes it easy to create accurate forecasts and adjust them dynamically based on new data or changing business conditions.

Ready to Take Your Financial Planning to the Next Level?

If you have more questions about Solver, or if you’re ready to start using the platform, get in touch with us or visit our Solver page. We’re here to help you unlock the full potential of Solver and empower your business to make more informed, data-driven decisions.

The post Solver FAQs | Your Go-To Guide for Budgeting, Forecasting, and Reporting in Excel appeared first on Access Analytic.

5 Excel Tricks They Won’t Believe

Get ready to amaze your coworkers with five next-level Excel tricks that blend creativity, logic, and a touch of holiday fun. These aren’t your standard tips—they’re designed to make you look like a spreadsheet wizard. Not only will I walk you through each trick, but I’ll also share how you can recreate them and pick up some bonus Excel skills along the way.

What’s Coming Up

• AnyImage Function: Create dynamic images that respond to text entries (hint: Christmas trees and reindeer included!).
• GridImage Trick: Make your cells come alive with hidden designs and creative formatting hacks.
• The Wrong Number Trick: Confuse and entertain with custom number displays.
• XLL Hyperlink Lookup: A reimagined XLOOKUP with clickable links to make navigation a breeze.
• The Broken Sheet: Turn your spreadsheet into a broken grid illusion that will stump anyone who sees it.

These tricks are designed to be fun, practical, and just mischievous enough to keep your colleagues guessing.

The accompanying file available HERE so you can follow along step-by-step.

Let’s get started and take your Excel game to the next level!

Join the Community

We’d love your feedback and suggestions. Follow us on YouTube.

The post 5 Excel Tricks to Wow Your Colleagues appeared first on Access Analytic.

Want to keep your Power BI reports up-to-date without waiting for scheduled refreshes? Now, you can manually refresh your report directly from the report interface using Power Automate. Below, Access Analytic team member Simone Palmas has created a step-by-step guide to set it up.

Setting up Manual Refresh in Power BI

Step 1: Build and Publish Your Report and set up the manual refresh

1. Create Your Report: Design and build your report in Power BI Desktop as usual. Once you are satisfied with your report, publish it to the Power BI Service.

2. Set Up the Refresh: After publishing, select the semantic model and configure the refresh (more details at the end, if you don’t have this set up). You may need to enter your data source credentials or set up an on-premises gateway [Click here to learn how to do this].

Step 2: Add a Power Automate Visual

1. Insert the Visual: In Power BI Desktop, go to the report where you want to add the refresh button. Select “Add Visual” and then choose “Power Automate for Power BI”.

2. Edit the Visual: Click on the Power Automate visual and then click the “More options” icon (three dots) in the top right corner of the visual, and select “Edit”. Wait a few seconds for the Power Automate interface to load.

Step 3: Create the Flow

1. Start a New Flow: In the Power Automate panel, select “New” and then “Instant cloud”.

2. Configure the Flow:

Add Action: Click “New step”;, search for “Refresh a dataset”, and
select this action from the list.

3. Set Up the Action:

Workspace: Select the workspace where your dataset is published.
Dataset: Select the dataset you want to refresh. Ensure your report is already published and available online. If the dataset isn’t visible, log in with your account in Power BI Desktop.

4. Save and Apply: Save your flow, return to the previous page in Power Automate, and click “Apply”. The Power Automate visual will now transform into a button with a “play” icon.

Step 4: Customize the Button

1. Edit Appearance: Customise the button to match the style of your report. You can change its text, colour, and size to make it more appealing.

Step 5: Publish and Test

1. Publish the Report: Publish the updated report with the new button to the Power BI service.

2. Test the Button: Click the button to ensure it triggers the dataset refresh.

3. If it doesn’t refresh immediately, try refreshing the web page. If you get an error while trying to refresh it:

check that the Semantic Model is correctly configured and that you have entered your credentials if required:

Important Notes

Power BI has a limit on the number of Refreshes you can perform:

For users with a Power BI Pro license, you can perform up to 8 refreshes per day. If you have a Premium license, this limit increases to 48 refreshes per day. These limits apply to scheduled refreshes and those triggered by Power Automate. If you exceed this limit, you will need to wait until the next day for additional refreshes. However, you can manually refresh the report directly from the workspace as many times as you like by clicking this Manual Refresh button:

If you found this guide helpful, you might also be interested in this video from our official YouTube channel. It demonstrates how to automatically refresh a Power BI report when a new file is added to a folder, using Power Automate. Watch it here.

Happy reporting!

The post Refreshing Power BI Reports with a Button Using Power Automate appeared first on Access Analytic.

Want to filter your Excel data efficiently without using macros? Filtering data can sometimes feel restrictive, especially when you want to search for multiple words independently of their order or the specific columns they might appear in.

Access Analytic team member, Simone Palmas, has created this handy guide will show you how to filter a table by entering any number of words in a Search box, which will then be searched across the specific columns you choose, regardless of the order.

Creating a single multi-criteria search box to return a filtered view of a table:

This is the table I will filter:

when I search “Smith”, my filtered table will return all the rows from the original table which include the word “Smith”:

If I want to filter the table to get all the rows which include “Smith” and “USA”, independently from the order, I can enter those 2 words to filter the table:

Here’s a step-by-step guide to set it up:

Step 1: Prepare Your Table

1. Create or Import Your Table: Ensure your table is ready and formatted correctly as a table. For this example, I have created a simple table with a list of clients and general information:

I might want to filter this table by a certain name, surname or country, for example.

Step 2: Set Up the Search Sheet

1. Create the Search Box: Decide where you want to enter the words to filter the table and format it to make it clear that it is the input Search Box. I added a new sheet and formatted cell C3 to look like a Search box:

2. Trim and Textsplit the Text in the Filter: Use this formula to return a dynamic array with all the words entered in the Search Box:

=IFERROR(TRIM(TEXTSPLIT(C3,’,’,)), ”)

For example, when I type “John, Smith, USA”, the dynamic array will return all these words in separate cells:

After testing it, you can hide or format this row, as you probably do not want to see it.

Note: In this example, words are separated by commas. So, you need to separate each word or phrase by a comma.

3. Assign a Name to the Trimmed Text Dynamic Array: This is an optional step to make the formula easier to understand and improve its Readability.

– Go to Name Manager and create a new name called TextSearch.
– In the “Refers to” field, click on the cell containing the trimmed text (C4 in my case) and add a # at the end, as this is a dynamic array.

Step 3: Add the Search Result Column to Your Table

1. Select the sheet which contains your original table and add a new column to concatenate the Columns You Need to Filter. In my example, I want to filter my table by Name, Surname, and Country, so I used the following formula:

=[@Name] & “ “ & [@Surname] & “ “ & [@Country]

2. Create a new Column to Check if the Searched Words Are Found. Add a new column, I called it: SeachResult.

Then enter the following formula to check if the searched words are in the column you previously created (in my case, ConcatColumns):

=MIN(ISNUMBER(SEARCH(TextSearch, [@ConcatColumns])) * 1) = 1

If you want to understand the logic of this formula, you can read the explanation, or you can go directly to the next step (Step 4).

Explanation of the Formula

The formula performs a multi-step process to determine if all specified words are present in a cell. Here’s how it works:

1. SEARCH: Search for Words:

The SEARCH function returns the position of each word entered in the search cell. If a word is not found, SEARCH generates an error.

For example, let’s consider searching for “Smith” and “USA” within the following rows of data:

In the first row, which contains (1; John; Smith; New York; USA), the ConcactColumn is: “John Smith USA”.

Using the SEARCH function, the results are as follows:

=SEARCH(“Smith”,”John Smith USA”) returns 6 because “Smith” starts at the 6th character, and =SEARCH(“USA”, “John Smith USA”) returns 12 because “USA” starts at the 12th character.

In the second row, which contains:

(2; Jane; Smith; London)
The ConcactColumn is “Jane Smith London”. Here, the SEARCH function results are: =SEARCH(‘Smith”, “Jane Smith London”) returns 7 because “Smith” starts at the 7th
character, and =SEARCH(“USA”, “Jane Smith London”) returns #VALUE! Because “USA” is not found in the text.

2. ISNUMBER: Convert Errors to Logical Values:

The ISNUMBER function is used to convert any errors to FALSE and any other values (positions) to TRUE.

3. _* 1: Convert Logical Values to Numbers:

The formula multiplies the TRUE/FALSE values by 1 to convert them to 1 (for TRUE) or 0 (for FALSE).

4. MIN: Find the Minimum Value:

The MIN function finds the smallest value in the list. If at least one word is not found, the list will contain a 0, making the MIN value 0.

5. _=1 Determine if All Words are Found:

The formula checks if the MIN value is 1. If MIN returns 0, it means at least one word was not found. If MIN returns 1, it means all words were found, and the formula will return TRUE. We want to filter only the rows where all the words are found, hence the ones = TRUE.

In summary, this formula verifies the presence of all specified words in a cell and returns TRUE if all words are found, allowing you to filter and display only those rows.

Step 4: Set Up the Filter Table

1. Filter the Table: Go back to the sheet with your Search Box, and in a cell beneath it, use the FILTER function to get all the rows in your table that include the searched words. The formula is:

=FILTER(MyTable, MyTable[SearchResult] = TRUE)

This will return all the rows in your table that include the searched words. Note that the result will not include the headers, which you can easily copy manually from the original Table.

The order of the words won’t change the final result. This is how the table will look after hiding the dynamic array row (4), copying the headers, hiding unused columns, and applying conditional formatting.

By following these steps, you can set up advanced filters in Excel to efficiently search and filter your data based on multiple criteria across various columns. This method is powerful and flexible, allowing you to filter large datasets without using macros and regardless of column order. The key steps involve preparing your table, setting up a dynamic search array, adding a search result column, and finally filtering your table based on these results.

The post How to Create an Advanced Filter in Excel without Macros appeared first on Access Analytic.

You’ve made it to the final part of our blog series on mastering dynamic measure formatting in Power BI! If you’re joining us now, you can catch up with Part 1 here, Part 2 here, and Part 3 here. In this post, we’ll explore dynamic formatting based on value ranges, a method particularly useful for measures spanning a vast range of values. 

This approach uses the SWITCH() function to apply different formats based on predefined value thresholds, enhancing readability. 

Step-by-Step Guide:

1. Ensure you have downloaded the working file provided in the Part 1 of this series. 
2. Select the “FormattedTotalSales” existing measure. 
3. Use SWITCH() to define formatting thresholds. The following DAX snippet illustrates this concept: 
   1. 1. Select the measure and change Format to Dynamic.
         

    

   1. 1. With “Format” option selected, type the following in the formula bar
         

    

   1. Your visuals should appear as below, showing values not formatted and with conditional formatting applied.

Pros and Cons 

Pros: 

• Straightforward to implement for a single measure 

• Great for dealing with a wide range of values 

Cons:

• Not as scalable for multiple measures 

• Each new measure requires a separate DAX formula 

Applications 

This option is particularly useful for summary measures in dashboards, where values can vary greatly depending on the data slice. 

Concluding Thoughts 

 Thank you for following our 4-part series on dynamic measure formatting in Power BI! We’ve explored various techniques, from static visual-level formats to dynamic value-based approaches. Experiment with these methods to find what works best for your reports. 

 Dynamic measure formatting in Power BI can dramatically improve the usability and clarity of your reports. Whether you choose to use SWITCH functions, individual visual formatting or value range-based formatting, each method has its place in the Power BI toolbox. 

 We encourage you to experiment with these techniques, you can check the tab “End” in the working file provided to see them in action. Note it contains the results from all 4 blog posts. Remember, the best way to learn is by doing, so don’t hesitate to get your hands dirty with.  

 If you missed any parts of this blog series, check out Part 1 here, Part 2 here, and Part 3 here. Keep exploring Power BI to make the most out of your data!

The post Mastering Dynamic Measure Formatting in Power BI | Part 4: Dynamic Formatting Based on Value Ranges appeared first on Access Analytic.

By Melina Louzada

Welcome to the third blog in our series on dynamic measure formatting in Power BI! If you haven’t checked out the first two blogs yet, find Part 1 here and Part 2 here. This post will cover using SWITCH() and SELECTEDVALUE() functions to create more advanced dynamic formatting options. 

The SWITCH() and SELECTEDVALUE() functions in DAX are powerful tools for conditionally formatting measures. By creating a two-column table for measure names and formats, you can apply specific formatting directly within a Matrix visual.

Step-by-Step Guide:​

1. 1. Ensure you have downloaded the working file provided in the Part 1 of this series to follow along. 
   2. Create a new table with columns for ‘Measure Name’ and ‘Format’. 
      1. 1. On the Home Menu, click on Enter data
            
            

       

      1. 1. Give your new table a name. i.e. MyMeasures Formatting
            

       

      1. Enter values on table as per below, ensure to name the columns too (double click to edit the column name). Note you can add different formatting for positive, negative and zero values by separating with a semi-colon.Positive ; Negative ; Zero

1. Create a new measure using the SWITCH() function to assign the formatting based on the measure selected. Here’s an example of what your DAX formula might look like:
   
   
   1. 1. With the measure selected, change the Format to Dynamic and type on the formula bar
         SELECTEDVALUE( MyMeasuresFormatting[Format] )
         

    

   1. Create a new Table Matrix Visual and add the Column “Measure Name” to the rows and your new measure “Selected Measure” to the values section of your visual.
      

Your table should look like this

Pros and Cons 

Pros:

• Flexibility to define custom formats for each measure 

• Simple to implement for a few measures 

Cons:

• Can become complex and unwieldy with a large number of measures 

Applications 

This approach is ideal for reports where a handful of measures require unique formatting that deviates from the standard. 

Next in the Series 

We invite you to try out these techniques yourself. You can view their implementation in the “End” tab of the working file provided, which includes the outcomes from all four blog posts. 

Don’t miss the final part of this series, Part 4: Dynamic Formatting Based on Value Ranges, where we’ll learn how to apply dynamic formatting based on value thresholds. For those just joining, find Part 1 here and Part 2 here.

The post Mastering Dynamic Measure Formatting in Power BI |Part 3: Using SWITCH/SELECTEDVALUE() for Dynamic Formatting appeared first on Access Analytic.

By Melina Louzada

Welcome back to the second part of our series on mastering dynamic measure formatting in Power BI! If you missed Part 1: Flexibility with Visual Level Format String, you can check it out here and download the working file. In this post, we will dive into dynamic cell color formatting, a great technique for enhancing the visual presentation of your data. 

Dynamic cell color formatting uses a DAX measure to change cell colors based on values dynamically. This technique helps in highlighting trends and drawing attention to significant data points. 

To illustrate this, we will enhance the visual presentation of our net profit data, you can use a DAX measure to dynamically format cell colors based on positive or negative values. 

Step-by-Step Guide:

1. 1. Ensure you have downloaded the working file provided in the Part 1 of this series. 
   2. Add a measure to define the Colour based on the value of an expression.
      

1. 1. Select Visual “Net Profit – Add Colour Measure on Variance”, then on the Format Pane, go to “Cell elements”, select “Net Profit Var %” measure as the “Series” and toggle Font Color ON and click on the “FX” symbol
      

1. Select Field Value and for the field, select the new colour measure created on previous step. 
   

Your table should now look like this, by changing the year filter, you can see the colour measure being applied. 

Pros and Cons

Pros

• Enhanced data visualisation making it easier for users to interpret data trends at a glance.  

• Easier to replicate and maintain across different measures than individually selecting the colours based on the measure values using the standard method provided in the visual. 

Cons:

• Can become complex with a large number of measures 

• Limited to visual-level customisation, it doesn’t affect the other visuals without reapplying the same method.  

• Conditional formatting using measures could slow down report performance, particularly with complex or large datasets.  

• Only controls colours formatting.  

• Requires a basic understanding of DAX

Applications

This approach is great to dynamically highlight specific elements of a visual that require further attention. i.e. once a trigger is reached, if values are negative, to categorise values in colour coding.

Next in the Series

Feel free to experiment with these techniques. For a practical example, refer to the “End” tab in the provided working file, where you will find the results from all four blog posts showcased together. 

 Up next is Part 3: Using SWITCH/SELECTEDVALUE() for Dynamic Formatting, where we’ll cover more advanced dynamic formatting techniques using DAX! If you missed Part 1, catch up here.

The post Mastering Dynamic Measure Formatting in Power BI | Part 2: Dynamic Cell Colour Formatting appeared first on Access Analytic.

By Melina Louzada

In the realm of data analysis, the clarity of data presentation is just as crucial as the data itself. Dynamic measure formatting in Power BI enables analysts to tailor data representations to enhance readability and insights. This blog 4-part series will navigate through a few techniques to dynamically change the formatting of values in Power BI based on the selected measure and its magnitude. 

Before diving in, ensure you have the working Power BI file ready; it’s a vital starting point for following this guide step-by-step.

Getting Started  

To follow along, download the starter file here and prepare your Power BI environment. The file contains unformatted data sets that we will transform using dynamic formatting techniques. 

Power BI’s new visual-level format strings feature, now available in preview, although not necessarily dynamic provides users with more flexibility in formatting data visualisations.   

This hierarchical approach means that visual-level format strings can be used to fine-tune data presentation, ensuring the exact formatting needed is applied to each visual component. To use this feature, enable the visual calculations preview in Power BI Desktop.  

Step-by-Step Guide:  

1. Selecting the visual named “Net Profit – Visual Level Format String”, go to the “Format” pane and click on “Properties” and “Data format”.  
2. Select the measure / field you want the specific formatting for the visual to apply and type in the desired formatting options.  

Pros and Cons   

Pros: 

• Flexibility to define custom formats for a specific visual without impacting any other visual where the measure is used. 

• Simple to implement for a few measures 

Cons: 

• This is a static formatting and is not dynamic, which means you have no flexibility in modifying the formatting based on the values or other criteria like you do with the other options presented in this blog series.  

• For now, visual level format strings required you to enter a  .NET format string instead of a VBA format string. This is a temporary issue that will be resolved in a future release. Find more information on this link.

Applications  

This functionality allows you to format visual calculations directly on the visual level, independent of the model, enhancing the customisation of data labels, cards, and slicers.  

Next in the Series 

Follow along and compare with the tab “End” in the working file provided. Note it contains the results from all 4 blog posts. 

Stay tuned for Part 2: Dynamic Cell Colour Formatting, where we’ll explore how to make your data more visually engaging using dynamic cell color formatting! 

The post Mastering Dynamic Measure Formatting in Power BI | Part 1 – Flexibility with Visual Level Format String appeared first on Access Analytic.

What began as a personal challenge soon evolved into a profound exploration of resilience, camaraderie, and the power of giving back. Through every twist and turn of the trail, Jeff’s trek became a powerful symbol of dedication and a reminder of the difference one person can make in the lives of others. In the blog article below, Jeff shares his daily insights as to his adventure in Kokoda and his support of Opportunity International Australia.

Day 1 | Preparing For Kokoda

On Tuesday 12th, I went climbing at City Summit, Malaga and while I was doing a climb, my right foot slipped and I banged the inside of my knee really hard against one of the holds. So hard that it broke the skin!

Read more

Day 2 | Arriving in Kokoda … Eventually!

A journey of 100km, 6,000m elevation change and 221,483 steps starts with a single flight!

Read more

Day 3 | Kokoda to Isurava

We were woken up several times last night by dogs barking, then roosters crowing from 3am onwards. Somehow I managed to get back to sleep after the roosters and woke up just before 5am.

Read more

Day 4 | Isurava to Templeton’s Crossing

There were no roosters or dogs barking last night, which was very welcome, just some gentle rain and the constant gushing of the nearby waterfall.

Read more

Day 5 | Templeton’s Crossing to Digger’s Camp

Unfortunately, my wish wasn’t granted and the wind was blowing a gale all night which meant that the tarpaulins over our tents flapped relentlessly. It was blowing so hard that it’s a miracle all the tarps were still there in the morning!

Read more

Day 6 | Digger’s Camp to Brigade Hill

It rained a little last night but apart from this, we enjoyed a beautiful, quiet night and a nice long sleep, much needed after the trek of the day.

Read more

Day 7 | Brigade Hill to Agulogo

We all got up a bit earlier this morning at 4.45am so we’d be ready for the Dawn Service. It sounds very early, and I guess it is, but we’re also going to sleep very early so it’s really not a problem. Each person had been given a role to play in the service and mine was to read the Fuzzy Wuzzy poem that one of the soldiers had written (I believe it was sent to his mum). The whole service was very moving, especially considering where we were and what had happened there.

Read more

Day 8 | Agulogo to Iriobawa

Getting ourselves organised seems to be getting easier each morning. We should all be really good by the last day!

Read more

Day 9 | Iriobawa to Goldie Creek

Bloody roosters woke us up at 3.15am! OMG, we all want to kill those little bastards!

Read more

Day 10 | Goldie Creek to Ower’s Corner

Thankfully, there were no roosters or other disruptions last night so we all slept pretty well … although we’re all looking forward to a real bed tonight as the blow up mattresses are pretty uncomfortable! Somehow Niall broke the toilet so it was leaning on a precarious angle! Oops! Don’t fall in!

Read more

Reflections & Insights

Learnings

– It’s amazing what you can do if you just keep going and don’t give up: hills, winning a war

– To get the most out of an experience, take every opportunity to volunteer and do every activity you can

– Take the time to listen to the answers fully before you speak

– Just focus on your next step and the journey will take care of itself

– When someone offers to help you, accept

– The people with the least are often the kindest and most generous

– Take time to disconnect and discover the world around you

– Address problems early when they’re small before they turn into big ones

Key Insights

– It’s amazing what you can do if you just keep going and don’t give up: hills, winning a war

– To get the most out of an experience, take every opportunity to volunteer and do every activity you can

– Take the time to listen to the answers fully before you speak

– Just focus on your next step and the journey will take care of itself

– Take time to disconnect and discover the world that’s around you

Feeling: Sense of achievement

Just like to say: Thanks to Trav for his professionalism, guidance, for the stories and for his leadership. Thanks to Rob for initiating and organising the trip.

Final Thought

“If you look at it, you see a dot. That’s here. That’s home. That’s us.

On it, everyone you ever heard of, every human being who ever lived, lived out their lives.

Think of the rivers of blood spilled by all those generals and emperors so that in glory and in triumph they could become the momentary masters of a fraction of a dot.

Think of the endless cruelties visited by the inhabitants of one corner of the dot on scarcely distinguishable inhabitants of some other corner of the dot.

Our planet is a lonely speck in the great enveloping cosmic dark.

In our obscurity — in all this vastness — there is no hint that help will come from elsewhere to save us from ourselves.

It is up to us.”

Carl Sagan “The Pale Blue Dot”

The post Jeff’s Kokoda Experience | A Journey of Determination appeared first on Access Analytic.

Technology is disrupting traditional business models more than ever and as such the role of the Chief Financial Officer (CFO) has evolved significantly. No longer limited to number-crunching, today’s CFOs are expected to play a strategic role in decision-making processes. Enter ChatGPT, a large language model equipped with Advanced Data Analysis capabilities that is revolutionising the finance functions in organisations.

Advanced Data Analysis

If you haven’t used the Advanced Data Analysis (ADA) feature, it’s currently a beta feature that you can turn on in ChatGPT-4.

It allows you to upload files (Excel, text, CSV, zip, images) then interact with this data using natural language. Behind the scenes, it writes the Python code required to translate your query into code.

You can then use the results of this to generate downloadable PDF, CSV or image files. If it discovers errors or problems in this process, it is even able to correct itself, try different approaches and generate a solution.

This enables ChatGPT to perform tasks such as:

1. Generating simple descriptive statistics (e.g., mean, median, mode)
2. Providing basic visualisations like pie charts or bar graphs
3. Offering elementary interpretations of the data you provide
4. Identifying trends or patterns in datasets

Applying ChatGPT to the Finance Function

Here are some practical insights into how CFOs can harness the power of ChatGPT for various finance functions, from budgeting to fraud detection.

Budgeting & Forecasting Analysis

One of the most time-consuming tasks for finance departments is the creation and management of budgets and forecasts. ChatGPT can assist CFOs by recommending forecasting methods and generating forecast numbers based on these methods.

The Advanced Data Analysis capability of ChatGPT allows it to incorporate both historical results as well as insights provided by CFOs about changes in the business, changes in the environment, new priorities, upcoming projects and more.

This not only saves time but also enhances the accuracy of these crucial financial documents since the numbers produced are not subject to human bias.

The numbers produced can then be uploaded into budgeting and forecasting tools such as Solver to allow efficient comparison with actual results using Power BI.

Writing Formulas & Code

For financial modelling or data manipulation, writing complex Excel or DAX formulas or even small chunks of code (VBA macros, PowerQuery code, Python etc) can be daunting. ChatGPT can generate such formulas and code snippets upon request, streamlining the modelling process and minimising the scope for error.

Automate Repetitive Tasks

Tasks like data entry, basic calculations, and routine report generation can be easily automated using ChatGPT. This allows CFOs and their teams to focus more on strategic activities, such as investment decisions and long-term financial planning.

Data Analysis & Pattern Recognition

ChatGPT’s ADA capabilities include natural language processing techniques to identify trends, patterns, and anomalies in large datasets. This makes it an invaluable tool for CFOs aiming to make data-driven decisions.

Fraud Detection

By analysing transactional data and utilising pattern recognition, ChatGPT’s ADA can flag suspicious activities that may warrant further investigation. This makes it a valuable asset in mitigating financial risks associated with fraudulent activities.

Predictive Analytics

Beyond just descriptive analytics, ChatGPT’s ADA can offer predictive insights, helping CFOs to foresee future financial trends and make proactive decisions.

Writing & Communication

Whether it’s generating quarterly reports, stakeholder communications, process documentation, or even policies, ChatGPT can handle the heavy lifting, ensuring that all written materials are coherent, well-structured, and compliant with standards.

Natural Language Interface to Data

ChatGPT’s ADA can serve as an intuitive natural language interface to databases. CFOs can query financial data using simple English phrases, making it easier for even non-technical team members to interact with complex datasets.

Advice on Tax/Accounting Standards & Regulations

Keeping abreast of the ever-changing tax laws and accounting standards is crucial for any CFO. ChatGPT can provide information and advice on these matters, although it should not replace professional legal or accounting counsel.

Conclusion

Incorporating ChatGPT into your finance functions is not just a technological upgrade; it’s a strategic decision that can greatly enhance efficiency, accuracy, and decision-making. For the modern CFO, adopting tools like ChatGPT is becoming less of an option and more of a necessity for staying competitive in today’s rapidly evolving business landscape.

The post Leveraging ChatGPT for Transformative Finance Functions: A Guide for CFOs appeared first on Access Analytic.

Financial modelling has long been a mainstay of the financial industry, essential for everything from risk assessment to capital allocation. Generative Artificial Intelligence (AI) technologies, like ChatGPT, are fundamentally changing financial modelling processes. 

At the risk of sounding like an advertisement for ChatGPT, the premium features (e.g. access to custom GPTs, image generation, greater access to the latest GPT versions as well as a greater ability to upload files for advanced data analysis and more) available in the paid version are worth exploring as they are extending these capabilities further.

The Wolfram Custom GPT

The Wolfram Custom GPT allows ChatGPT to query Wolfram Alpha’s computational engine to provide a broader range of numerical and computational solutions. This enhances ChatGPT’s capabilities in several ways, including but not limited to:

1. Mathematical Calculations: Complex calculations, solving equations and even calculus problems can be handled more effectively.
2. Statistical Analysis: Facilitates advanced statistical computations, including regression analysis, hypothesis testing and data visualisation.
3. Unit Conversions: Whether it’s distance, weight, or more complex units, the plug-in can provide precise conversions.
4. Scientific Data: Users can pull in data on various scientific topics, from elemental properties to celestial bodies. 
5. Financial Calculations: Provides financial calculations for things like compound interest, mortgage calculations, etc.
6. Data Visualisation: The plug-in can create various types of graphs and plots to better illustrate data.
7. Historical Information: Users can access information on historical events, figures or data.
8. Weather and Geography: Retrieve detailed current and historical weather data, as well as geographical data like distances between places.
9. Text and Data Analysis: Natural language text can be analysed for things like word frequency, and more detailed data analysis can be carried out on numerical data sets.

The Wolfram plug-in significantly augments ChatGPT’s capabilities, allowing it to offer a more detailed and nuanced level of response in specific domains.

Advanced Data Analysis (ADA) Feature

While the free version provides limited access to advanced data analysis and file upload functionality, the paid version allows you to do far more with both of these. 

It allows you to upload files (Excel, text, CSV, zip, images) then interact with this data using natural language. Behind the scenes, it writes the Python code required to translate your query into code, you can then use the results of this to generate downloadable PDF, CSV or image files.

If it discovers errors or problems in this process, it is even able to correct itself, try different approaches and generate a solution.

The Advanced Data Analysis feature enables ChatGPT to perform tasks such as:

1. Generate simple descriptive statistics (e.g. mean, median, mode).
2. Provide basic visualisations like pie charts or bar graphs.
3. Offer elementary interpretations of the data you provide.
4. Identify trends or patterns in datasets.

Apply These Components to Financial Modelling

Here’s how these components can be practically applied to financial modelling.

1. Model Creation

One of the biggest benefits of using the ADA and the Wolfram GPT in ChatGPT is that they enable someone with little or no financial modelling skills to create simple models, input their assumptions, produce forecasts, run scenarios and analyse the results. All this can be done just using natural language; no complex Excel formulas or other code is required.

This doesn’t replace the need for financial modellers however as the models produced are relatively simple. Additionally, the model produced can be an unauditable “black box” unless ChatGPT is also asked to show its workings in deriving the results.

The benefit is that it works well for simple situations and it’s quick and it’s very easy for non-technical users, thereby making modelling more accessible.

2. Data Analysis and Preprocessing

Data Cleaning

ChatGPT’s ADA can generate Python code to clean data. This works well for once-off data cleansing operations.

If you need a repeatable process however, you could simply take the Python code and use this within Power Query inside Power BI or Excel. This provides a very visual way to immediately see the results.

Alternatively, if your data is stored within an Excel file, you could take the Python code and use this directly within Excel’s new Python code interpreter to cleanse your data.

Yet another alternative is to use ChatGPT to write the PowerQuery code required to give you the data you need in a specified format.

We’ve also provided some custom GPTs that are specifically tailored to help with writing, cleaning and documenting PowerQuery code, DAX formulas and Excel formulas. Details.

Identify Model Variables

ADA can identify and even create new variables that increase a model’s predictive power. This could be further augmented by Wolfram’s computational algorithms if required.

3. Forecasting

Forecast Method Recommendation

ChatGPT’s ADA can analyse data and recommend a suitable forecasting method. The modeller can then use their judgement to assess whether this is appropriate.

Time-Series Analysis

Advanced forecasting technologies like LSTMs and ARIMA become even more robust when coupled with Wolfram’s computational capabilities. The Python libraries in ADA can also be used to apply forecasting techniques to your data and produce a downloadable dataset.

The dataset produced could then be uploaded into specialist budgeting and forecasting software such as Solver.

Scenario Analysis

Uploading your model’s scenario outputs to ADA allows you to perform richer, more detailed scenario analyses.

4. Simulation

Monte Carlo Simulations

The Wolfram plug-in can be used to perform basic Monte Carlo simulations. If you need more powerful simulations however you would be better to use R or Python libraries then upload the results of these to the ADA for analysis.

If you’re not sure how to do these kinds of things, ChatGPT can provide guidance here also.

5. Decision Support

Insights and Recommendations

For those who aren’t tech-savvy, the ADA makes it easier to analyse large amounts of data to identify trends and relationships. It can also suggest reasons why these trends may have occurred.

Because ChatGPT has such a large base to draw from, the suggestions and recommendations it provides often include many things a single individual wouldn’t come up with on their own.

More accessible, efficient and insightful approaches to data analysis

Using ChatGPT’s premium features, financial modelling becomes easier, more precise and the modeller is able to draw greater insights from a wider database. 

In summary, the premium features of ChatGPT are enabling more accessible, efficient and insightful approaches to data analysis, forecasting and decision-making. As these features continue to evolve, they hold the potential to completely reshape how we navigate the complex world of financial modelling.

The post How ChatGPT’s Premium Features are Revolutionising Financial Modelling appeared first on Access Analytic.

Exciting News: Unlock the Power of AI with our GPTs – Now Available for Free!

An exciting announcement from Open AI recently,  GPTs (Generative Pre-trained Transformers) are now available for free in the store!

These GPTs are simply predefined prompts refined through trial and error to help you achieve more with ease.

The Access Analytic GPTs

Here are some of the GPTs you can now access:

1. DAX Helper
2. Power Query Magician
3. Excel Formula Helper

Why Use our GPTs?

• User-Friendly: Perfect for both beginners and experts.
• Used in real-life: we use these in our day to day work and they help speed up what we’re capable of.
• Free Access: Available for free in the store!

How to Get Started

1. Visit the Store: Explore GPTs (chatgpt.com)
2. Select Your GPT: Search for Wyn Hopkins or Access Analytic
3. Start Using: Enhance your productivity and creativity with AI.

Join the Community

We’d love your feedback and suggestions for future GPTs. Your insights will help us improve and create more useful tools.

Thank you for your support. Here’s to achieving more with AI!

Happy exploring!

How to build your Own GPTs

If you’d like to build your own we have an explainer video here:

GPT4 playlist

Not sure what’s possible check out our playlist:

The post Unlock the Power of AI with our GPTs – Now Available for Free! appeared first on Access Analytic.

In today’s hyper-competitive business landscape, gut instincts and traditional methods are giving way to data-driven insights. Data analytics now serve as the backbone of strategic planning, providing companies with the hindsight, insights and foresight they require to make well-informed decisions. As the world’s leading tool for data visualisation and analytics, Power BI, is contributing to data-driven strategic planning processes in leading organisations in a variety of areas.

Data analytics and Power BI play an integral role in modern strategic planning

1. Identifying Market Trends and Customer Preferences

Data analytics enables companies to recognise new audiences, identify emerging market trends, understand consumer preferences and pain points and thereby fine-tune their strategies to meet customer needs far more proactively.

2. Competitive Analysis

In-depth analysis of competitors’ metrics, market share and customer reviews offers invaluable insights. Companies can identify competitor weaknesses, market gaps and opportunities that can be taken advantage of.

3. Financial Planning and Forecasting

Data-driven models have revolutionised strategic planning processes. Using tools, such as Solver, businesses can allocate budgets more efficiently and effectively, forecast sales accurately and predict cash flows, taking into account a myriad of variables and market trends, then connect Power BI to these models to analyse actual performance against budgets/forecasts.

4. SWOT Analysis

Data enriches the traditional SWOT (Strengths, Weaknesses, Opportunities, Threats) analysis, providing quantitative measures that make the assessment far more actionable.

5. Risk Assessment and Mitigation

Data analytics also plays a pivotal role in identifying potential business risks through historical data and can help in developing robust mitigation strategies.

6. Optimisation of Operations

Data doesn’t just contribute to high-level planning; it’s also vital for operational efficiency. By analysing supply chain data and other operational data, businesses can identify bottlenecks and optimise their operations.

7. Human Resources Planning

The HR department is another beneficiary of data analytics, which can highlight skill gaps, identify diversity or pay disparities, find opportunities to improve performance, benchmark individual and team performance, and even predict turnover rates to help develop better strategies.

8. Innovation and New Product Development

Data can inspire innovation by highlighting customer pain points and gaps in the market, driving the development of new products and services.

9. Stakeholder Engagement

Companies are increasingly relying on data analytics for PR and stakeholder engagement strategies, using tools like social media analytics and sentiment analysis.

10. Decision-making and Execution

Power BI stands out in this arena by offering intuitive data dashboards that amalgamate data from various sources. This provides a real-time overview of the business, aiding quicker and more informed decision-making.

11. Feedback Loops

Post-execution KPIs (Key Performance Indicators) provide a data-driven feedback mechanism, enabling companies to continually refine their strategies based on real-world performance. Power BI’s reporting capabilities are ideally suited for this.

Investment in Data Analytics Tools and Skills is Crucial

As organisations navigate the complexities of modern business, the importance of a data-centric approach in strategic planning becomes increasingly clear. Given the plethora of applications of data in strategic planning, investing in robust data analytics tools like Power BI is no longer optional but a necessity. However, it’s not merely about amassing data. The real value is in converting it into actionable insights.

Data has irrevocably altered the landscape of strategic planning. Power BI serves as an invaluable tool in this by offering a flexible, user-friendly platform for data visualisation and analytics. 

If you’re ready to find out how Microsoft Power BI can empower you effectively navigate the dynamic world of business, book a call with Jeff to discuss your needs.

The post The Integral Role of Data Analytics and Power BI in Modern Strategic Planning appeared first on Access Analytic.

The role of the Chief Financial Officer (CFO) has never been more complex or challenging. Driven by many interrelated factors such as rapid technology change, changing consumer expectations and regulatory shifts, Perth CFOs are turning to data analytics to help them navigate the ever evolving world of business.

Below we highlight the key forces propelling the need for data-driven decision-making and explore ways that Power BI can assist CFOs.

1. Complexity of the Business Environment

• Globalisation: Operating across multiple markets involves navigating diverse currencies, cultures and regulations. Power BI consolidates this data for clearer insights.
• Competition: The crowded market landscape necessitates that CFOs be more strategic and agile, which requires real-time data analytics. Power BI assists with visualising both real-time and historical data.

2. Volume and Variety of Data

• Big Data: The explosion in data volumes and sources offers the ability to generate highly granular insights, however this can only occur if the data is effectively analysed. Tools such as Power BI, when combined with Microsoft Fabric, make this possible.
• Unstructured Data: In addition to traditional metrics, CFOs must also manage unstructured data such as customer reviews and social media sentiment. With Power BI, Artificial Intelligence is able to ingest and summarise this for visualisation.

3. Technological Advancements

• Cloud Computing: The ability to access systems and data from anywhere at any time enables timely decisions. For meaningful insights the data must be analysed using tools such as Power BI.
• Machine Learning/AI: When historical insights are combined with human analysis algorithms that predict future trends have the ability to make planning more accurate. Tools such as Power BI allow for the visualisation of this as well as what-if scenario comparison from solutions such as Solver.

4. Regulatory Changes

• Compliance: Constantly evolving financial regulations require accurate data analytics for compliance. Power BI can be used to provide this information through its automated reporting.
• Transparency: Data analytics in Power BI can facilitate the transparency in financial and ESG reporting demanded by stakeholders.

5. Consumer Expectations

• Personalisation: Understanding consumer spending habits through data analytics is critical to meet expectations for tailored experiences. Once the data has been collected, this can be quickly and easily visualised in Power BI.
• Speed: Quick yet well-informed financial decisions are essential to meet the fast pace of consumer demands. CFOs must have the systems in place to collect accurate, timely, reliable data which can then be rapidly analysed using Power BI.

6. Operational Efficiency

• Cost Management: Analytical tools such as Power BI can highlight inefficiencies and areas for possible cost reductions.
• Resource Allocation: Efficient resource allocation, be it human capital or new technology investments, requires data analysis so that decisions are not just based on “gut feel” or ego.

• Opportunity Analysis: Data analytics can also assist with identifying opportunities and gaps in the market. Power BI’s visualisation tools make it much easier to identify these.

7. Financial Flexibility and Agility

• Real-Time Analysis: Power BI’s real-time data analytics allow CFOs to adapt quickly to market changes.
• Scenario Planning: Combined with budgeting and forecasting tools, such as Solver, advanced analytics facilitate more complex scenario planning, equipping CFOs for various market conditions. Solver allows CFOs to quickly and efficiently generate new scenarios to respond to new challenges and changing market conditions. Connecting Power BI to these allows CFOs to analyse and communicate the potential impact of decisions on the business.

8. Competitive Advantage

• Strategic Initiatives: Data insights can inform strategic decisions, like entering new markets or creating new revenue streams. Power BI allows CFOs and other decision makers to rapidly identify these.
• Innovation: Data can uncover avenues for innovation that can provide a competitive edge.

9. Risk Management

• Predictive Analytics: Power BI can use predictive models developed in tools such as Microsoft Azure ML or Fabric to assess and predict risks.
• Fraud Detection: Real-time fraud detection is possible with advanced analytics and machine learning models. Using these can help protect against significant financial losses.

10. Accountability and Performance Metrics

• KPI Tracking: Dashboards in Power BI help in tracking performance against set objectives. These can be integrated across the organisation to help align employees at all levels to achieve the organisational objectives.
• Benchmarking: Comparing company performance against industry standards enables CFOs to set realistic goals. Power BI provides an excellent way to communicate the organisation’s performance vs benchmarks.

The pressures exerted by these diverse factors make data analytics, supported by Power BI, an essential cornerstone for effective and forward-thinking CFO leadership. By embracing this data-centric approach, CFOs are better equipped to navigate the complexities of today’s rapidly evolving business landscape.

If you’re ready to find out how Microsoft Power BI can empower you effectively navigate the dynamic world of business, book a call with Jeff to discuss your needs.

The post Navigating the New Normal: Why CFOs are embracing Data Analytics and Power BI appeared first on Access Analytic.

Setting up a free Personal Power BI / Fabric Account

Then you can go to any report in My Workspace and click File – Embed – Publish to Web to share you report for free to the world.  This process is unsecure so anyone can potentially see it.

The post How to Get your Free Power BI / Fabric Account appeared first on Access Analytic.

Discerning professionals know that efficiency is the key to business success in 2024. According to Forbes media, efficient processes are vital at every stage of your operation, including budgeting. 

Is your budgeting process slow, exhausting and stressful? Do you wonder why you always find yourself scrambling at the last minute? If you’re answering yes to either of these questions, that’s an indication that your budget process flow is broken and  is likely undercutting the time and effort you put in.

If you’re unsure if your process is really broken, here are our 10 signs of inefficiency within your budgeting flow process:

1. Exclusive Reliance on Excel

Relying solely on Excel for budgeting limits the capacity for automation and collaboration, resulting in a slower and more error-prone process.

2. Lack of a Centralised Database

Without a centralised database, data entry is scattered and disorganised, leading to difficulties in data retrieval, reporting and analysis.

3. Frequently Broken Excel Links

Too many broken Excel links can lead to errors, and user frustration, making it challenging to maintain a reliable budgeting system.

4. Limited Security with Excel Files

Team members freely sharing and editing Excel files can compromise data security by risking unauthorised access and potential data manipulation.

5. Multiple Versions of Budget Forms

Managing multiple versions of budget entry forms introduces confusion, making it difficult to determine the most up-to-date and accurate information.

6. Missed Deadlines

Frequent missed deadlines indicates communication and coordination issues, hindering the timely progression of the budgeting process and disappointing stakeholders. 

7. Lack of Guidance on Goals and Strategies

A deficiency in providing guidance on goals and strategies leaves stakeholders without a clear direction, impacting the alignment of the budget with organisational objectives.

8. Inadequate Input for Supporting Documentation 

The absence of fields for entering support documentation and comments in the input process results in a lack of context, making it challenging to understand the rationale behind budgetary figures.

9. Communication Gaps

Poor communication between departments creates silos, inhibiting the collaborative effort required for a comprehensive and cohesive budget.

10. Suspected Gamesmanship or Sandbagging

A lack of transparency and collaboration may lead to suspicions of department leaders prioritising personal interests, eroding trust and hindering a collective approach to budgeting.

If three or more of these warning signs sound familiar, it’s time for you to fix your process. Cutting-edge Corporate Performance Management (CPM) solutions like Solver provide the  in-depth planning capabilities you need to eliminate these issues and ease your budgeting  process. Solver gets you on track in one day. Fix your budgeting process today, watch our demo video.

Looking for more information on optimising your budget flow process?

Download our 2 FREE Solver white papers on Budgeting Process and Monthly Reporting today for insights and best practice recommendations.

The post 10 Ways to Know If Your Budgeting Process Flow Is Broken appeared first on Access Analytic.

The speed of making decisions matters. Small delays compound and cost money. The right decision at the right time can be a game changer for an organisation, so the pressure for CFOs and Financial Managers to make that decision quickly is immense. In this article, we outline how Solver is the key to making better decisions faster.

Without the right data analytics tools, the process of collating data from various sources is labour-intensive and risks inaccuracies. With Solver, all the necessary information is consolidated in a single cloud-based solution, providing on-demand accessibility in customisable formats.

Four Essentials to Support Better Decisions

1. Solver & Power BI Reporting

Solver’s data warehouse provides a single location from which you can access all your critical information, which is time efficient and supports collaboration. You can use Solver’s cloud-connected Excel add-in and benefit from the familiar Excel interface of Excel for report design, then use any web browser for running and viewing reports.

Alternatively, you can use Power BI’s built-in Solver connector to bring Solver’s data models and measures into Power BI so you can access all the amazing presentation and drill-down interactivity this program provides.

Benefits of Solver’s Central Data Warehouse for Reporting

1. The Data Warehouse allows for reporting based on multiple data sources provided by different users
2. Reporting on General Ledger (GL), sub-ledgers, as well as other data sources and handle special needs such as allocations and reconciliations 
3. Optional reporting live on ERP databases
4. Ad-hoc reporting for quick answers
5. Report individual entities (cost centres, companies, divisions etc) or an entire consolidated entity
6. Automatic currency conversions

Key Benefits of Solver’s Report Designer:

1. A single report designer with easy drop and drag elements and pre-built formulas. No need for manual exports to Excel for formatting, printing, etc.
2. Excel report designer interface provides familiarity and reduces time spent on upskilling
3. Ability to archive reports i.e. store copies of reports with the data they contained when the report was run.
4. Ability to schedule reports to run and be distributed to multiple recipients regularly.

Key Benefits of Using Power BI for Solver Reporting

1. Produce beautiful, flexible, interactive reports that users are familiar with
2. Utilise existing Power BI skillets and allow users to produce their own reports
3. Access all Power BI’s interactivity features for powerful filtering and drill-down
4. Integrates with Microsoft PowerPoint

Solver Reporting is a report writer, report viewer and report analysis solution for any data in your Solver Data Warehouse. With these extensive reporting capabilities, better faster decisions are at your fingertips.

2. Solver Planning

Solver Planning streamlines budgeting, forecasting, planning and modelling efficient access to meaningful data for making critical decisions. All important data and planning processes are managed in one platform, making Solver stand out from other cloud-based financial products. Their modern, user-friendly solution is designed for anything from simple to highly complex input forms and has a Web Portal that includes key features such as end-user data entry, workflow and reporting. 

Solver Planning can be used for the following:

1. Multiple different workbooks, each with a single budget form.
2. Single workbook with multiple budget forms with the same layout.
3. Multiple similar workbooks with multiple different budget forms in each workbook.

Solver Planning can also be used as a collection tool:

1. Modelling
2. Break-back analysis
3. Allocations

1. Electronic data entry of: 
   1. Statistics
   2. Key Performance Indicators (KPIs)  
   3. Other organisational information
2. Commentary input for: 
   1. Monthly actual/budget variance reports 
   2. Scorecard KPIs 
   3. Quarterly/Annual reports 

With Solver’s planning and analysis tools, you can plan and prepare for any possibility at the click of a button, allowing the focus to be on business development and growth. 

3. Solver Data Warehouse

Consolidate all the data needed for reporting, dashboards and planning in one place. Together with the Web Portal, Reporting and Planning, the Data Warehouse enables access to immediate reporting capabilities as well as fully customisable budgeting, forecasting and data entry features. 

Using simple actions, the Solver Data Warehouse provides easy import and export options from multiple data sources. Pre-configured modules make reporting and analysis straightforward. 

Solver Data Warehouse features include:

1. Intuitive interface with easy configuration, including pre-configured modules
2. Microsoft SQL Server/SQL Azure based
3. Automated data loading with Solver’s ETL tool
4. Combine financial and operational data
5. Unlimited trees in any dimension
6. Easily add modules and dimension

The significance of having easy access to accurate and relevant data cannot be overstated when making informed decisions swiftly; Solver Data Warehouse is that solution.

In an environment, where every moment counts, the capability to access crucial financial insights promptly allows leaders to drive their organisation towards sustainable growth and financial resilience. The Solver Suite delivers the right data in the right format and at the right time so CFOs and Financial Managers are empowered to make better decisions faster. 

If you’re ready to find out how Solver can empower you to make better decisions, book a call with Jeff to discuss your needs.

The post Complete guide to making faster, better decisions with Solver appeared first on Access Analytic.

The post Unleash the Power of Cloud-Based Budgeting & Forecasting Software appeared first on Access Analytic.

Welcome to the world of streamlined efficiency with Excel Office Scripts! In this blog, we dive into the transformative power of automating your Excel workflows, specifically focusing on a scenario many of us encounter: managing and organizing current and completed records in an Excel file.

Picture this…

You have a list of Purchase Orders in Excel which lists all the essential records, you then manually change the order status to Fully Supplied, and then filter, cut and paste into another sheet where your Completed Orders are stored, and then return to the Current Orders sheet and clear the filters. It’s a dynamic space where orders come and go, but over time, this list grows longer and more unmanageable. It may not seem like a lot of work, but when you have to do this over and over again, it is not only a very tedious task, but the room for accidental errors also increases.

How do you make this process more efficient and keep it clean, organized, and focused solely on the pending orders? The answer lies in Excel Office Scripts!

About Office Scripts

Office Scripts in Excel is designed to bring the power of automation to your spreadsheets. It’s a platform that allows users to record, write, and run scripts in Excel Desktop and for the web, leveraging JavaScript or TypeScript (TypeScript is a superset of JavaScript, meaning that any valid JavaScript code is also valid TypeScript code). This technology is particularly beneficial for automating repetitive tasks, integrating with other Office services, and streamlining complex workflows. With Office Scripts, users can create scripts that manipulate data, format cells, and even communicate with other Microsoft 365 applications.

Benefits

One of the main benefits of Office Scripts is its ability to save time and reduce errors in data management. By automating tasks that were previously done manually, users can ensure consistency and accuracy in their data handling. It’s particularly useful for businesses and individuals who regularly work with large datasets or complex Excel functions. The integration with Power Automate also opens possibilities for more advanced workflows and automated processes across various Microsoft 365 applications.

Limitations

However, there are some limitations to Office Scripts. Once created, the script is saved to the user OneDrive, therefore, it lives in the organisation tenancy and differently from VBA, it cannot be easily shared with external parties. The script can be shared with users from the same organisation by making it available in the file where the script is to be run, a button can be added to make the process more obvious and easier to access. The script is also available to be used in Power Automate.
Additionally, the scripting requires a basic understanding of JavaScript or TypeScript, which might pose a challenge for users without a programming background, although the “Record Actions” feature can make this learning process a little easier.
It is important to note that the Automate tab (Office Scripts) is only available to certain versions of Excel, typically available for:

• Microsoft 365 Business Plans: This includes Business Premium and higher plans.
• Microsoft 365 Enterprise Plans: Generally available in E3 and E5 plans.
• Educational Plans: Certain educational plans may include access to Office Scripts

It is not yet available in Excel for Microsoft 365 Family or Personal plans.

To find out more about subscriptions click HERE.

*It’s important to note that Microsoft continuously updates and changes its service offerings, so it’s always a good idea to check the latest information from Microsoft’s official resources or your subscription details to confirm the availability of Office Scripts in your specific plan.

Scenario

You have a lists of orders and as you fulfill them, you change the order status to Fully Supplied, the list is getting longer and you just want to see the current orders pending action in this list and want to move the completed orders to another sheet for record keeping purposes.

Seeing Office Scripts in action

Click on the download below to view the base file and a copy of the code.

In the file provided, the original orders data is saved in a separate sheet for backup purposes only.

There are two code options to use, one with and one without sheet protection.

It is recommended to keep a copy of the original data when building the code and playing with different scenarios as when the code runs, Excel is unable to undo the actions performed by the script.

Building the code from scratch

If you wish to follow along, open the file “Excel Office Scripts_Start” available for download in the link above. The final version of this file is also available for comparison.

This script uses helper cells to assist with the location of the cells we want to manipulate, it makes the code a lot simpler and easier to understand, so before diving into the details of the script, setup the helper cells as follows:

Type the formulas into the cell locations as per below:

Define a named range for the startingRow value and endingRow values.

Now, to start writing your script, click on the “Automate” tab in your Excel and then on “New Script”.

A code editor will open on the side of the screen. Once code is completed, click on save and it will be saved to your OneDrive.

Here you may choose to copy the entire code provided in the download files or follow the instructions and type it directly in the code editor.

Give your script a name by clicking on “Script”, you can then edit the name and the location where the script will be saved.

The basic fundament of a script is that we need to be very specific in telling the script on where to look for the information we want it to manipulate, then we give instructions on what we want it to do with this information. And it is also important to note that the code is case sensitive.

Each script must contain a “main” function with the “ExcelScript.Workbook” type as its first parameter, this already comes pre-populated when creating a new script.

In our first line of code, we are going to use “let” to define variables being used in the code. The entire code is typed between the curly brackets { } after the first function statement.

Here we define a variable named “sourceSheet” which is looking for the sheet named “CurrentOrders” in the file. In the following row, instead of looking for a sheet, we are looking for a table named “tbl_Orders”.

Whilst it is not necessary to add ; at the end of each statement, it is considered a good practice to clearly tell the reader that particular line of the code has ended.

Script: Get Data and Paste to a different sheet in Excel

Now that we have told the code where to look, we need to provide more information. When pointing the code to a cell or a range, we need to use the method “getRange()” and “getValue()” to get the value of the range.

In this example, we are using a named range as it is recommended rather than hard coding a specific cell address (i.e. cell A1) in the code. Hard coding a specific cell is not recommended as the code does not update dynamically if the value of a cell is moved (i.e. other columns / rows added or deleted).

We then sort the table by Order status to have all the Fully Supplied orders grouped. Then using the helper cells, we identify in which row the first and last Fully Supplier orders are located. A named range has been linked to the cell where the formula is (see above)

Note that the code will not run if there are no Fully Supplied Orders, this code could be improved to handle errors and show certain messages depending on outcome of actions performed, to keep it simple, it is not included in this example.

After applying a filter to select only “Fully Supplied” orders, we then copy the visible range to paste into destination sheet.

Now we need to explain what to do with the source data and define location of target sheet.

First, define the destination sheet location, and we are then looking for the last row with values, assuming the column B has values. Note this is not error proof, ideally the sheet would need to be protected to avoid data being entered at the bottom of the completed orders table. We can tweak the code to unprotect and then protect sheet while performing the actions (see below for more info on this option).

Once the last used cell is located, look for the next row and paste values copied from source sheet.

Then, delete the copied items from the original sheet, clear the filters and sort it in Date order (1^st column – index 0).

Adding and Removing Protection on Worksheet

Optionally, we can also apply worksheet protection to run in the code. In this case, it is a good idea as it is looking for the last used cell in the CompletedOrders sheet, so to avoid accidental values being entered, it is recommended to protect the sheet.

Add the “Unprotect” part after defining the Target Sheet location step and add the “Protect” part after pasting data into Target Sheet step. The highlighted “demo” can be replaced with the desired password to protect and unprotect the sheet. For no password, use “” .

The code will still run if the Target Sheet has no password.

Add a button to run the script in the file

Office scripts must be triggered to run, one way of doing this inside the file is to add a button. To do this, click on + Add in workbook, in the Script access area inside the Code Editor panel. Note it will add the button directly on the cell you are in.

To move the button after it has been added, right click and then drag to move. The button can also be renamed and customised as a regular Shape to suit your sheet style. Right click and then click on Text to rename.

If the code has run successfully, a message will appear above the formula bar.

Note: that by adding a button to the file, you are automatically sharing the script within people inside your organisation, it is may not be available to external parties, depending on where the file is saved. i.e. if the file is saved in the organisation SharePoint, the script will not be shared with people from outside. Sharing a copy of the script file, which automatically saves on your OneDrive would be a way around this.

Conclusion

Office Scripts in Excel stands out as a powerful tool for enhancing productivity, fostering a more efficient approach to data management, and opening new avenues for automation within the Microsoft 365 suite.

Learn more about Office Scripts HERE.

The post Excel Office Scripts for Effortless Data Movement appeared first on Access Analytic.

The Challenge…

In this month’s challenge, you need to combine any non duplicate items into a single cell for each client ID#.

e.g. Client ID1 has 2 rows. All entries need to be combined to return 1 row for Client ID 1 so for example the two Phone Numbers: Phone 1 and Phone 2, these need to be combined into Phone1, Phone 2 in a single cell.

Submissions close 31st October 2023

Download the file

Sending us your solutions

If you’d like to submit your solution then please save you file as:

Your Name – Power Query Challenge – Group Uniques.xlsx (or PBIX if using Power BI) and send to info@accessanalytic.com.au

Please don’t send just M code.

Ideally make your solution flexible so that if more rows or columns are added your solution still works!

We’ll add your submission to our Submissions Folder.

Keep an eye out for Wyn’s solution on our YouTube channel.

The post Power Query Challenge – A Unique Problem appeared first on Access Analytic.

The Challenge…

Download the file

Thanks for taking part and here’s our solution

Submissions can still be viewed here.

The post Power Query Challenge – Bananas! Split Challenge appeared first on Access Analytic.

Instant Analysis with Code Interpreter and ChatGPT

Take 8 Excel files, write 2 instructions ( 25 words in total ), done…data summarized and charts built!

Go to OpenAI.com GPT -4, enable Code Interpreter

Write 1 sentence and upload folder of data (do not use sensitive data)

Here’s the content of the zipped folder (featured in image above)

Then Code Interpreter does it’s thing..

30 seconds later…  I add one more sentence to get the look I want

Export image and data

Check out the related video for amazingly quick analysis…

The post Code Interpreter GPT-4: Is it cocktails on the beach time? appeared first on Access Analytic.

Streamlining Power Query Code Documentation with GPT-4: A Game Changer

Watch my explanation or read on if you prefer…

Introduction

As Power BI and Excel developers, we often find ourselves knee-deep in lines of code. Writing, cleaning, documenting, and renaming steps – it can all be quite time-consuming. The game has changed with the introduction of GPT-4, a powerful tool from OpenAI that can help streamline the process significantly.

We’ve all been there. Our code is handling a variety of tasks, cleaning up data, filtering rows, capitalizing, trimming text, and merging columns – typical power query stuff. Documenting and renaming these functions with meaningful names without spaces used to be a chore, but not anymore.

How to Use GPT-4 for Code Documentation

Setting Up Your OpenAI Account

Setting up your OpenAI account is straightforward. Head to the Open AI website, where you can sign up and access both GPT-3.5 (free) and GPT-4. Although the free version is available, GPT-4’s superior performance justifies its $20 per month cost.

To get the most out of GPT-4, you will need to provide a well-constructed prompt. A good prompt includes instructions about how you would like your code to be laid out.

You can download the prompt I used here:

Once you have your prompt ready, paste it into the “Send a message box” and click the send button.

Then simply copy your code from the advanced editor of your Power Query and paste it into the GPT-4 chat and click Send again.

The Power of GPT-4

The result? Cleaned up code, detailed descriptions of the overall process, line-by-line comments, flagged filtered steps, and meaningful codes like ‘TrimFirstName’ or ‘CapitalizeFirstName’. This tool will turn a chaotic codebase into a beautifully organised and easy-to-read script.

You can simply click copy and paste into your advanced editor.

Constructing the Right Prompt

The key to getting the best out of GPT-4 lies in the prompt. It’s crucial to instruct GPT-4 to act as an expert in the relevant field (in our case, a Power Query expert). Providing an overview of the task, specific instructions, and examples of the desired outcome will result in a much higher quality output.

For example, if you want GPT-4 to rename each line of M code to something more meaningful and use CamelCase, explicitly state this in your instructions. Similarly, if you want GPT-4 to prefix filtered steps with ‘Filter_’, make sure to include this in your prompt.

Conclusion: Embrace the Future

GPT-4 is truly a game-changer for coding, making tasks like documentation and code cleaning so much easier and more efficient. If you’re looking for ways to streamline your workflow, it’s time to consider leveraging this powerful tool from OpenAI.

Let us know your thoughts and experiences using GPT-4 for code documentation. We would love to hear your feedback and any tips you might have discovered along the way.

*Stay tuned for future posts on other exciting ways to use GPT-4 and other AI tools to boost your productivity!*

The post Streamlining Power Query Code Documentation with GPT-4: A Game Changer appeared first on Access Analytic.

For multi-row multi-level drop downs you can check out our blog post and video HERE

Check out our YouTube channel with weekly videos dedicated to Excel, Power Query and Power BI

If you’re looking for a multi row multi-level dependent drop down then check out our amazing popular solution HERE

Why is Excel returning an error message? Why doesn’t my formula work? What did I do wrong?

The answer to these common questions may surprise you.

Many spreadsheets I’ve come across have a common problem – inconsistent data entry. If someone enters “United States of America” in a cell, “United  States of America ” (extra space after “United” and “America”) in another cell, and then wishes to use “United States of America” as a formula criteria, functions such as SUMIFS and VLOOKUP won’t work properly as “United States of America” was not entered consistently throughout each area of the workbook.

A great way of preventing this problem is to restrict the values that can be inputted into a cell via Data Validation. For example, if your company is currently selling to Australia, Thailand, New Zealand and China, you can enter these values as a list into one section of your workbook, and then use Data Validation to prevent misspelled and other variants of these country names from being entered into a cell.

Data Validation is a great tool, but what happens next month if your company starts selling to Indonesia and Russia? How do you automatically extend data validation into subsequent rows of your data entry table to avoid errors? The best way to solve this problem is with dynamic ranges and tables.

Here’s how to do this (file available via the link below to follow along):

Step 1: Transform Your Data into Tables

If you’ve never used the Excel table feature before, you’re missing out! Excel tables are essential for dealing with large, complicated spreadsheets and help tremendously when dealing with Excel add-ins such as Power Query and Power Pivot.

Begin by selecting your data set (Ctrl + A) and then press Ctrl + T to turn the data into an Excel table. Then click on the Table Name box and give your table a sensible name with no spaces i.e. tblSalesData (“tbl” for Table). Repeat this process for your data validation list.

Step 2: Create a Defined Name

Go to your data validation table and highlight the column that will contain your data validation values (Ctrl + Spacebar). Go to Formulas/Name Manager or Ctrl + F3 to open the Name Manager.

Click the “New” button. Enter an appropriate name, such as ddCountries (dd for “dropdown”). Note that the “Refers to:” box is referring to every item in the data validation column with table formula nomenclature (table name followed by column name in square brackets). Click OK.

Step 3: Add Data Validation

Select the column in your data entry table that you wish to add data validation to. Go to Data/Data Validation or Alt + D + L to open the Data Validation window. Select “List” from the “Allow” dropdown menu.

In the “Source” box, hit the F3 key and select your defined name from the “Paste Name” box. Click OK twice to return to the main screen.

Now every cell in the column will contain data validation that is restricted to the values in the “Data Validation List” column. If you add another row to the Data Validation table, this will automatically appear in the drop down menus you have just created. New rows added to the revenue table will also include data validation for these values by default.

For additional fun, you can add additional tables with data validation to cycle among formula values.

If you found this interesting you may want to explore how to create a dependent validation drop down. Find out how here.

The post Dynamic Data Validation with Tables in Excel appeared first on Access Analytic.

The post Unleash ChatGPT’s Power in Office 365 appeared first on Access Analytic.

If you want to create one-off dependent drop-down lists in data validation, you can use a simple technique with XLOOKUP. We did a video on it available here.

But what if you want to create multiple dependent drop-down lists in a table?

You will need a different approach which many people have done videos on but the solutions were always quite complex or required INDIRECT or many named ranges.

With this technique there’s just 3 formuals

Here are the steps and formulas:

1.Set up the data table with the options for each level of the drop-down list. In this example, I have a table named tblOptions with three columns: List1, List2, and List3. Each column contains the options for each level of the drop-down list.

2.Create some named ranges to store the formulas for each level of the drop-down list. In this example, I have created 2 named ranges: Level1Choice (column G), Level2Result (Column H). You can use any names you like, but make sure they are descriptive and consistent.

3.Enter the formulas for each named range. Here are the formulas I used:

– In G5 of the Level1Choice column: =UNIQUE(tblOptions[List1])

This formula returns the unique values from the List1 column of tblOptions using the UNIQUE function.

– In H5 of the Level2Result colummn: =TOROW(UNIQUE(FILTER(tblOptions[List2],tblOptions[List1]=G5,”Empty List”)))

This formula returns the unique values from the List2 column of tblOptions that match the value in cell G5 (the first level drop-down list) using the FILTER function. If there is no match, it returns “Empty List”. The TOROW function converts the vertical array to a horizontal array for data validation.

4.Then the magic function is this:

=XLOOKUP( Whatever Cell is to the left of this formula, Level1Choice,Level2Result)#

This formula returns the corresponding value from Level2Result based on the value in the cell to the left (in the screenshot above that’s AB5 matching the level 1 selection \ Level2Result is is the named range that contains an array of values and the # at the end allows you to reference that.

I then repeated this process for the next level of dependent drop-downs.

Then set up your selection table and highlight the first column.

Go to Data à Data Validation à List and reference the Unique drop down list in the Level1Choice column – in our case G5# Repeat by highlighting the 2nd column and going to list again and this time enter the XLOOKUP formula in the data validation list source box.

Repeat for Sub-level (note the change to I7 and Leve2Choice and Level3Result.

For the conditional formatting I used this formula

=COUNTIFS( XLOOKUP(H7, Level1Choice, Level2Result )#, I7)=0

Which simply checks if the selected value matches an item in the list generated by the XLOOKUP, and then applied a red fill and white font.

Download the file

The post Easiest Excel Multiple Row Dependent Drop Down List appeared first on Access Analytic.

The Challenge…

School’s almost out for summer (well in Australia anyway)… how has everyone done in their exams?

Who had the highest average score for each day?

How can we tell?   We need to sprinkle on some Power Query Magic dust!

*Don’t forget to make your solution flexible so that if more rows or columns are added your solution still works!

Download the file

Sending us your solutions

If you would like to submit your solution please save your file as:

Your Name – Power Query Challenge – Best in class.xlsx (or PBIX if using Power BI) and send to info@accessanalytic.com.au

Please don’t send just M code.

We’ll add your submission to our submissions folder here.

Keep an eye out for Wyn’s solution on our YouTube channel.

The post Power Query Challenge – Best in Class appeared first on Access Analytic.

The Challenge…

Our Solution…

Convert Range of Values to a List – Power Query Challenge youtu.be

Your Solutions…

Some of the solutions we received can be viewed here along with the original challenge: Power Query Challenge Tickets to Rows

The post Power Query Challenge – Ticket List appeared first on Access Analytic.

Creating a Merge Duplicates Alert

• This challenge is how to warn the end user that duplicates exist

The Challenge…

Take the challenge with our free download

And if you would like us to have a look at your solution e-mail it to info@accessanalytic.com.au

The post Power Query Challenge – Merge Duplicates Alert appeared first on Access Analytic.

The Challenge: Hours from Start and End

• Assume Start Time and Stop Time are always whole hours
• Solution must handle new rows of data being added to the source with new staff names
• Remove any records marked as “exclude” in the Status column (handle upper case / lower case typos)

Our approach…

Your submissions…

Here are some of the submissions we received – August 2022 Power Query Challenge Submissions

The post Power Query Stop Start Challenge appeared first on Access Analytic.

The Challenge: Survey Response

• Remove any current AND FUTURE columns that begin with Sys, but allow for other future additional (non Sys) columns to be automatically included
• Replace all but 1 space between the names in the first column
• Add a TRUE / FALSE flag for the 3 response options in 3 separate columns (there will only ever be a maximum of 3 answers)

And here’s how we did it…

Thanks for all the submissions, a record number so far from all the challenges we’ve posted. If you would like to check out some of the answers here they are:

June 2022 Power Query Challenge Submissions

The post Power Query Survey Response Challenge appeared first on Access Analytic.

Challenge: Change columns to a single row

Inspired by a real-life scenario where nicely structure data needed to be “Pivoted”, but in a specific way, and as always with Power Query it needs to work when new data is added. The challenge is to:

• present each person as a single row with all their qualifications in columns
• names must be displayed alphabetically
• qualifications for each person to be sorted alphabetically
• must automatically handle new people and totally new qualifications

Take the challenge with our free download

Our solution uses GroupBy, List Functions and a little hack

We love looking at the solutions that come our way so thanks to these folks for participating. Solutions can be found in the same folder as the challenge file here:

Alexander Sorokopyt
Andrew Shepley
Bohdan Duda
Chandeep Chhabra
Christopher Hazel
Daniil Maslyuk
Daryl Lynch
Diana Maria Iordachescu
Dyota
Glyn Willis
Guido
Henriette
Ivan Dumalovski Janjušić
Jan Berny
Kolyu
Leon Dijkers
Nick Bonner
Nizam
Peter Tholstrup
Philip Treacy
Quadri Atharu
Sham F
Steve Bateman

The post Power Query Challenge – Columns to Rows appeared first on Access Analytic.

Wordle for Power BI Fans

We’ve updated the Wordle Game for all our Power BI Fans out there. All the words are related to Power BI in some way.

Click on the file below to play along! *Word of caution though, the file won’t open on iPhones.

Download a copy of the file we used with our free download:

Here’s a link to the real Wordle Game.

Our video below shows the use of Dynamic Array formulas # references, XLOOKUP, SEQUENCE, MID, TODAY and some conditional formatting to build your own Wordle Game.

Check out more of our Power BI  videos on our YouTube Channel.

The post Wordle for Power BI Fans appeared first on Access Analytic.

This month’s challenge is about dates and times

There’s a surprising issue hidden in here too! The goals are as follows:

• extract the time without the seconds
• extract the year (watch out for the trap)
• extract the number of days between the last Friday of the Year and the last day of the year

Take the challenge with our free download

Our solution…

And some of the submissions we received…

• Alexander Sorokopyt
• Bhavya Gupta
• Dyota Tanuwibawa
• Egor Vaulin
• Giannis Kiokpas
• Khaled Chowdhury
• Melissa Stanton
• Munir Airmarzoug
• Peter Gulstad
• Peter Tholstrup
• Quadri Atharu
• Samuel Vanderslink

Thanks for taking the challenge! Don’t miss an update and the latest in Power Query and Power BI by following us on our YouTube Channel

The post Power Query Dates and Time Challenge appeared first on Access Analytic.

Flexible Consolidation of Tables

These 2 tables exist on their own tabs in this file, goal is to combine them in a flexible way to produce the output required :

1. Must replace nulls with 0s
2. Must be able to handle a completely new table for a new Region being added
3. Must be able to handle changes to the Zone Names, or new Zone Names being added to any region Table
4. Output to be Sorted by Date then Zone then Region

Output Required – 80 Rows initially (but must be refreshable and handle scenarios mentioned above)

And here’s how we’ve done it…

Download the solution file here.

Some of the solutions sent our way

• Benedicte Demas
• Egor Vaulin
• Ken Cromb
• Maribel Medina
• Melissa de Korte
• Michiel Schoonderbeek
• Peter Tholstrup
• Victor Momoh
• Vladimir Shamsutdinov

The post Power on with this Power Query Challenge appeared first on Access Analytic.

Learning Dynamic Array formulas in Excel by building Wordle

This video shows the use of Dynamic Array formulas # references, XLOOKUP, SEQUENCE, MID, TODAY and some conditional formatting.

Click on the file below to play along!

Download a copy of the file we used below:

The post Build Wordle using Dynamic Array Functions appeared first on Access Analytic.

Select Range and Convert

To kick off 2022 we thought we would put this Power Query challenge to you:

• Select Range and Convert to required result
• Solution should work when dates, codes, colours wording or numbers in Value column change
• Note the colours in the Required Result are for guidance purposes only and are not part of the challenge

If you’d like us to look at your answer send us a copy to info@accessanalytic.com.au and we’ll also add a link to your solution at the bottom of this page unless you’d prefer us not to.

Flex your Power Query skills with our free download.

Our solution…

The new year challenge certainly got some traction and we have quite a few solutions to share. Thanks as always for taking the challenge and sharing your thoughts:

• Konrad Schaefers
• Tim Linke
• Mohammed Fowmy
• Jerry Ball
• Brian Julius
• Peter Tholstrup
• Melissa Dekorte
• Egor Vaulin
• Emil Jayaputra
• Bénédicte Démas
• Ata Biabani
• Munir Aimarzoug
• Osiel Herrera
• Quadri Atharu

Until the next one! Check out our YouTube channel for weekly videos dedicated to Excel, Power Query and Power BI.

The post New Year Power Query Challenge appeared first on Access Analytic.

Columns to Rows and Rows to Columns Challenge

In this month’s challenge we have a couple of key challenges to face:

• We have a double row heading, the top row of which is merged
• These merged “Department” headings need to become row items and we need to filter out variance
• We have a Year and Month number column that we need to convert into an “end of month” date (assume Month 1 is January )
• We have Base Case and Worst Case that we need to extract, add a space and then split into 2 separate columns
• Remove the Total

If you’d like us to look at your answer send us a copy, and we’ll also add a link to your solution at the bottom of this page unless you’d prefer us not to.

Accept the challenge below with our free download.

And here’s our solution…

We also have these solutions to share if you want to check out some of the different approaches:

• Ivan Zhelyazkov
• Ata Biabani
• Sam Bhide
• Wayan Wijesinghe
• Egor Vaulin
• Abdullah Gulabi
• Gary Letts
• Christopher Majka

Thanks for taking on the challenge and sharing your insights!

The post November Power Query Challenge appeared first on Access Analytic.

Insert Blank Rows with Power Query

Power Query in Excel can be used for so many purposes and converting data ready for upload into another system is just one.

In this scenario we show you a couple of techniques to insert a row for each change in a group of items.

Did you know a Query can be appended with itself!?

The post Insert Blank Rows with Power Query appeared first on Access Analytic.

Challenge of the month – Flex your Power Query Skills

From time to time, we post fun, technical challenges in Excel & Power BI.

For this one, take the source data in the blue table and turn it into the format shown in the green table.

Your solution needs to automatically handle extra Projects & Allocation columns being added in future.

Assume they’re added with the same format as shown here.

If there is more than one project, the Final GL Code needs to show [Code]|A, [Code]|B etc.

If 100% is allocated to just one project, the Final GL Code column should just show the GL code.

Accept the challenge below with our free download.

Our solution…

The pick of the bunch

Here are some of the approaches people took including Wyn Hopkins’ solution and our top pick by Peter Tholstrup:

• Peter Tholstrup
• Jerry Ball
• Phil Whyman
• Pavel Adam
• Ivan Zhelyazkov
• Owen Auger
• Bill Szysz

Extra Kudos to Pavel who did the entire process in 1 applied step! following a comment Wyn made on LinkedIn about the number of steps.

Thanks to everyone for participating & stay tuned for the next one!

The post October Power Query Challenge appeared first on Access Analytic.

SquareWars – A Fun Way to Learn Excel 365

Here’s a fun way to learn a few Excel 365 functions and challenge a colleague to a friendly game at the same time..

Aim

Leave your opponent with the last square and you win!

Rules

Take turns to enter your sequence of numbers using the SEQUENCE function
=SEQUENCE( Row, Col )
If your formula spills over the edge or into another range you lose

New Game

Click New Game then press Delete to clear the board
Watch the video to see it in action…

Functions Used

SEQUENCE

FORMULATEXT

ISEVEN

COUNTBLANK

IFERROR

ISERROR

Other Techniques

Dynamic Arrays

Linked Images

Conditional Formatting

Named Ranges

Hyperlinked Images

See how it works and challenge your colleague to a game!

SquareWars for Desktop

SquareWars for Web (the Excel for web version only differs in that it currently can’t display the linked Images nor page background)

Happy gaming!

The post SquareWars – A Fun Way to Learn Excel 365 appeared first on Access Analytic.

by Jeff Robson

Say my name, say my name

When you’re working with data from Excel in Power Query, column names can often change, new columns can be added and columns can be deleted.

Excel users don’t think twice about doing any of these things as they all seem quite normal and logical however they can all cause problems for Power Query since, by default, it hardcoded column names into its M code.

If you’re doing an Unpivot Other Columns, you might want to choose the first two columns, then always unpivot all the rest.

But how do you make your code robust if the first two column names are known to change?

Am I just a Number to You?

In Power Query, yes! I’d prefer it if you were!

One way you can do this is to use a list of column names, then reference the column names using the column number, rather than the column name.

Steps:

Use the user interface to record your Unpivot as normal

If your system is setup to add an automatic Change Type, delete this step as it will also cause problems.

Go into the Advanced Editor and insert a line of code to place all the column names into a List

Edit your Unpivot step and replace the column names with List elements

Your unpivot will now always unpivot the first two columns regardless of their names.

The post Power Query: Unpivot using Column Numbers rather than Names appeared first on Access Analytic.

The post Excel Skills Assessment appeared first on Access Analytic.

Challenge of the month – Flex your Power Query Skills

From time to time, we post fun, technical challenges in Excel & Power BI.

In this one, you start with a dataset where every second row is data and the rows in between are column headings. Can you turn this into two columns of data: dates and data?

Accept the challenge below with our free download.

Winners and Solutions Announced!

Congratulations to all who participated in this challenge! We received some excellent entries & a very wide variety of approaches, which illustrates the range of different ways Power Query can be applied to achieve the same result.

Some of the entries used multiple tables, some extracted alternate rows, some used various pivot combinations & indexes to match up everything in a single table.

If you’d like to see some of the approaches people took, take a look at the files below.

Because of the quality of the entries, we decided to accept the top 12 submissions this time so huge congratulations to our winners (in no particular order):

• Cristian Angyal
• Jerry Ball
• Pathe Bah
• Tze Sheng Yeo
• Konrad Schafers
• Kevin Phan
• Peter Bartholomew: Power Query Solution, Excel formulas Solution 
• Gary Letts
• Neale Blackwood
• Morten Ostergaard Lauritsen
• Chris King
• Frik Lloyd

*We even received a solution that just used Excel formulas with a mixture of dynamic arrays, LAMBDA & LET (thanks Peter!)

Thanks to everyone for participating & stay tuned for the next one!

The post Power Query Challenge appeared first on Access Analytic.

Fixed vs Variable Loan Products … which is best?

It can be difficult to compare fixed vs variable loan products.

Banks often market loans with fixed interest rates using the selling point of certainty: “you’ll always know what your repayments will be”.

Check the fine print!

While certainty can be somewhat attractive, there’s a price to pay!

Fixed interest rate loans usually carry a significantly higher rate & usually have break fees that apply if you want to repay the loan early.

This means you’re exchanging flexibility for certainty because most variable rate loans have useful features such as:

• A 100% offset savings account
• The ability to redraw surplus funds
• The ability to pay the loan off sooner by increasing your repayments

Are Fixed Rate Loans Really Worth it?

Banks have teams of specialist experts forecasting the interest rate movements so they’re highly unlikely to lose when providing fixed interest rate loans. That means you’re highly unlikely to come out ahead!

Before you take out a fixed-rate loan, use our free calculator to compare your options.

You can input what you think might happen with interest rates in future and calculate the total amount of interest you’ll pay under both types of loans.

You can then estimate which will be better for you and make an informed decision.

How it works

The calculator uses dynamic array formulas to create a loan schedule based on your inputs.

Important: the calculator requires the use of iterative calculations to avoid circular references so if you get a warning about the file containing circular references, please ensure this is enabled before you use the model (see the Intructions sheet for details).

Take a look at the formulas to see some interesting examples of how to apply dynamic array formulas to situations like this. If you have any additional questions, just get in touch.

Pre-requisites

• Office 365 with dynamic array formulas

Read more: Loan Structure – Understanding Fixed & Variable Interest Rates

Note: Access Analytic Solutions Pty Ltd is not a financial adviser. Please seek advice from a professional financial adviser before making any decisions about financial products.

The post Fixed vs Variable Loan Interest Calculator appeared first on Access Analytic.

Teams Meeting Recordings

A quick bonus video explaining where you now find your Microsoft Teams meeting recordings.

The post Where is my Teams recording saved? appeared first on Access Analytic.

Combining Multiple Files from a Folder

How to use Power Query for Excel and Power BI to consolidate multiple files into a single table of data, whether you’re using OneDrive , SharePoint or a traditional network folder.

As well as showing the basic steps, this video explains the inner workings of the Power Query helper functions so you fully understand how it works

And here’s how you connect to a file on Sharepoint and OneDrive

The post Using Power Query to Consolidate Multiple Files from a Folder appeared first on Access Analytic.

Simple documentation tips when using Power Query

Whether it’s Power BI, Excel or Dataflows, adding some helpful indicators, documenting and renaming applied steps can be really helpful.

Be kind to your future self!

The post Power Query Simple Documentation Tips appeared first on Access Analytic.

Tips & Tricks to using Power BI

There are several tricks to using Power BI that aren’t always obvious. In this video we show you some of the hidden gems:

• Create a Measures Table
• Create Folders for your Measures
• Drag Measures between Tables (and Folders)
• Add brackets to negative numbers
• Consolidate multiple Excel sheets
• Bonus Data Types tip

The post Power BI Hidden Gems appeared first on Access Analytic.

Consolidate multiple Excel worksheets using Power Query

A common request on forums and in the training classes we run is “how do I consolidated multiple sheets into a summary?”

Power Query can do it, I step you through how I would approach it.

Techniques covered include:

• Creating a custom function (via the User Interface)
• Unpivotting
• Load destinations (sheet v data model)

Tip: Make sure your Power Query formula bar is on via the View menu in the Power Query Editor.

Note: This process can be easier when you are consolidating sheets from an external workbook (you can actually get a list of the sheet, table and range names rather than having to manually create one)

The post Consolidate Excel worksheets with Power Query appeared first on Access Analytic.

Excel Hash 2021

A bit of fun to be had and a challenge set by fellow Excel MVP Oz du Soleil. Finding a solution to the spare seat assigner. Our solution incorporates the following items:

1. Cutout person
2. Pick at least 2 from the following list: LET, LAMBDA, Dynamic Arrays, Power Query New Data Types
3. The EVEN function
4. Everything has to be integrated and justified–even the cutout person.
5. Everything in Excel is open for use, except VBA. So, go ahead and use Power Query, Power Pivot, Solver, Form Controls … whatever.

There’s no voting for this one but feel free to to give this a like!

The post The Spare Seat Assigner appeared first on Access Analytic.

Granting access to reports

Adding a Power BI report to Teams is straight forward, but granting access can be a challenge.

For more on this topic check out our blog article Securely sharing Power BI in Teams

The post Power BI & Teams – granting access appeared first on Access Analytic.

Brief guide for Power BI users

A brief guide for users of Power BI reports.

This is a brief guide for users of Power BI reports and not a guide on how to build Power BI reports. This is aimed to help users of Power BI dashboards and reports to understand how to interact with and navigate around Power BI content.

Power BI Live Demo

Click on anything & watch it update!  For the best experience click on the expand button in the bottom right corner.

For more Power BI, Power Query and Excel videos check out our YouTube Channel 

The post Brief guide for Power BI users appeared first on Access Analytic.

Assigning Data Types

Assigning data types is an essential step when working with Power Query in Excel or Transform Data in Power BI Desktop. Here are some useful tips on how to change data types while maximising the efficiency of your models:

Tip #1: Don’t Change Types Until the End of a Query

• A best practice when working with Power Query is to delay changing column types until the very last step of a query.

• Each time data types are changed, Power Query hard-codes one or more column names into the M code. This can cause your query to break if the columns in your changed type step are removed or renamed outside of Power Query or in a later step.
• Pushing this step to the end of your query thus makes it easier to debug your query later if columns are renamed or removed.

Tip #2: Change DateTime data to the Date Type

• When working with datetime data, consider whether your report needs to analyse data at the time level.
• If you don’t need to analyse data at the time level, change your datetime columns to the date data type. This reduces the amount of information stored in your date columns, which reduces the size of your model and makes it run more efficiently.
• If you do need to analyse data at the time level, split your datetime column into a date column and a time column to store the data more efficiently.

Tip #3: Turn Off Automatically Detect Column Types

• By default, Power Query automatically adds query steps that promote headers and change data types in your query after certain transformations are performed.
• You can modify the Power Query options settings to prevent this from happening. In Excel’s Power Query window, go to File/Options & Settings/Query Options/Current Workbook/Data Load and turn off “Automatically detect column types and headers for unstructured sources”:

• In Power BI, go to File/Options & Settings/Options/Current File/Data Load and turn off “Automatically detect column types and headers for unstructured sources”:

Want to learn more? Jump on to one of our courses

We’ve trained over 1,000 people from all types of organisations and departments in Australia and all around the world. See what attendees are saying about our courses:

The post Top tips for Power Query Data Types appeared first on Access Analytic.

by Wyn Hopkins

How to handle teams when you have multiple different logins

Often people are required to be part of “Teams” in organisations other than their own.

The desktop app is OK at allowing you to switch between different organisations if you have the same email address for all teams.

The trouble starts when you have multiple different email addresses.

This issue crops up all the time for consultants and other cross-company workers.

Here’s how I handle it, using Edge Chromium (similar steps apply to Google Chrome)

Once it’s set up you can have multiple Teams instances open, and with Edge Chromium you can even set up a desktop “App” for each instance…

Create a user profile for each organisation you’re a member of

Name your profile the same as the organisation

Go to Teams and open in Web App

http://teams.microsoft.com/

Add a Teams shortcut to your Favorites Bar

Or have Teams automatically launch on opening the browser…

Repeat for each organisation

Then it’s easy to toggle between Teams and even have multiple Teams going at once

Alternatively the ultimate shortcut is to create a Teams App

Then give it a name

It then shows up if you press the Windows Key (see top item under Recently added), and you can Right Click it and pin to your task bar or Start Menu

Hope that’s useful

For Power BI, Power Query and Excel videos (and a few things like this article) check out our YouTube Channel 

The post Multiple Teams logins with Edge Chromium appeared first on Access Analytic.

by Jeff Robson

Tricking non-dynamic array formulas into spilling isn’t something you’d naturally think of doing.

Dynamic Arrays are great but sometimes the formulas that refer to them behave in unexpected ways, and sometimes these don’t spill … without any obvious reason.

For example, the DATE function spills, but the EOMONTH function doesn’t.  The AND, OR and NOT functions don’t spill, yet the formula =(A1>100)*(A2<50) does spill.

This can be very frustrating!

Dynamic Tricks

Some functions that don’t spill however can be “tricked” into spilling!

In the example below, the DATE function is spilling no problems, however, the EOMONTH function is currently showing the #VALUE! error and isn’t spilling. Grrrrr! Why doesn’t it behave like the DATE function???

If we modify our EOMONTH formula slightly, however, we can get our EOMONTH formula to also spill.  All we need to do is add zero onto our spill cell and it starts spilling!

Bizarre, unexpected, stupid … I hear you say?  Yes, I agree with you!

Even more bizarre is that all you actually need to do is put a plus in front of your spill cell and this will cause the formula to spill!

Why is it so?

Charles Williams, Excel MVP, provided this explanation:

Any time you add an operator to a function parameter Excel has to evaluate the resulting expression before passing it to the function.

So when you add the + to the C7# EOMONTH sees the result of evaluating +C7# – which is an array of values

Without the + EOMONTH sees a range reference.

In VBA UDF terms, think of the parameter being defined as a variant which can contain any type of data including an array of values or a range and so your VBA has to either check which type it actually got, or always convert using Param.Value2=Param.Value2 and then check if it got a scalar or an array.

So inside EOMONTH, there is code that detects whether the parameter has been passed an array of values or a range reference, and it looks like when it is passed a range, it does not expand it.

But not Always!

If you apply this technique to an AND function however it still refuses to spill:

The only way to get AND-like functionality (that I’m aware of!) is to use the method on row 11 above.

Conclusion

If none of this makes any sense to you, just remember to try putting a + in front of your spill cell reference next time you have a non-spilling formula and you might be pleasantly surprised … or not!

EndNote

After publishing this article, Peter Bartholomew also provided an example of using MMULT to achieve AND functionality (though it’s a bit complicated) and mentioned Charles Williams’ AND.COLS custom function (which is much simpler), available as part of his FastExcel SpeedTools add-in. So, if you’re looking for a simpler, more logical solution that won’t require multiplying spilled ranges together, take a look at Charles’ functions (note: there is a licensing cost for the add-in).

The post Tricking Non-Dynamic Array Formulas into Spilling appeared first on Access Analytic.

Create Power BI themes using the Adobe colour wheel

You can create your own Power BI Themes but knowing what colours to use is a challenge.

Adobe colour wheel can help.

Want to learn more? Jump on to one of our courses

We’ve trained over 1,000 people from all types of organisations and departments in Australia and all around the world. See what attendees are saying about our courses:

The post Create Power BI Themes appeared first on Access Analytic.

Boost your productivity with these shortcuts

With over 20 years’ experience in Excel we’ve picked up a few tips and tricks. We’ll show you the shortcuts, features and formulas that will speed up your daily use of Excel. From SWITCH to Dependent Drop downs,  Grouping to Conditional Formatting,  clever validation lists and even a few XLOOKUP tricks there’s something for everyone in this video.

Download the demo file HERE

The post Excel tips & tricks to boost productivity appeared first on Access Analytic.

Power BI – Prepare for the new Ribbon

Per the 2020 Power BI planned release notes the new ribbon, that has been in preview for several months, will be switched on by April 2020 for everyone. Check out the update from Microsoft here.

You can turn it on now by going to File > Options and Setting > Options > Preview Features

While you’re there turn on the Decomposition tree and Customise themes. Both great options.

Home

The Home Tab is now split into Home and Insert. And our old friend Edit Queries is moved across a little and renamed Transform data

[FLIP YOUR MOBILE TO LANDSCAPE VIEW]

Modelling

Where’s all the good stuff gone you may ask?

Where’s my sort by column, or my number formatting?

The good news is that they’ve now added a number of “context sensitive menus”

So when you click on a measure, or a column or a table in you right hand field pane you get new relevant menus…..

Measure Tools

This is where you’ll find your formatting

Column Tools

This where you’ll find Sort by Column

Table Tools

And finally….

View

Where you can choose and then customise your themes and export them for your other reports or for your team mates to use.

The Power BI user interface still needs improving. The experience of formatting visuals for new and experienced users alike is not intuitive and quite fiddly, but this is a step in the right direction.

Trying to be consistent and provide a familiar interface to Office 365 users is a sensible move.

The post The New Power BI Ribbon appeared first on Access Analytic.

by Wyn Hopkins

How to use Power Query to connect to a file on OneDrive or SharePoint

A growing number of organisations are starting to store their files on OneDrive for Business and SharePoint. Users will begin to discover that connecting to these files with Power Query is not as straight forward as clicking the button for Get Data from Excel Workbook, or Text / CSV.

There are a couple of options which I’ll demonstrate in the video:

1. From Web
2. From SharePoint Folder

I’m open to suggestions on improvements. In fact I include an improvement on option 2 that I picked up from Miguel Escobar.

Neither option 1 or 2 is great. It’s fraught with traps and as you’ll see I mess up and have to restart… and I’d classify myself as an “experienced user” !

Please vote for a better experience on the Excel UserVoice site page.

Plus it’s about time there was a single Power Query User Voice location.

I hope you find it useful.

The previous video I did on this topic is by far the most popular on our YouTube channel, and I felt it needed expanding and more explanation to include the SharePoint Folder approach.

Hope you found this helpful!

The post Connect to files stored on OneDrive and SharePoint appeared first on Access Analytic.

by Wyn Hopkins

Securely sharing Power BI in Teams

Embedding a Power BI report inside your Teams channel is a great way of sharing reports and discussing them during meetings.

Adding a Report to Teams

To add a report, just click the + sign at the top of your Teams Channel window and then choose the Power BI tile

That then shows this report picker window where you navigate to a workspace of your choosing and select the report that you want to display

And your interactive report appears

Will all Team members immediately see this report?

No.

2 conditions need to be satisfied

1. The users will still need a Power BI licence to view this report. Or the report must be shared from a “Premium” workspace.
2. The user will need to have been given access to the report via one of the many Power BI sharing options, more on this below

Giving access to Power BI reports to the whole Team.

( so that reports can be viewed in Teams and access is controlled via Team membership )

Before getting into this any further, if a report is shared directly with a person via their email address or active directory group then it will show up for them in Teams.

If you have existing reports and workspaces then you can give access to the Team by adding the team name to either your workspace access, or by sharing it to the Team via an App. More about that further on.

There is also a “feature” of Teams that when you create a Team it creates a Power BI workspace.

Not only is this annoying when all these “non-workspaces” start appearing in Power BI, the workspace that is created is a “V1” workspace (this is the old workspace structure).

You cannot publish a Power BI report to a V1 workspace anymore, it simply doesn’t show up in your list of workspaces after you click the Publish button. Upgrade it to a V2 workspace and the choice appears in your Publish list. I’m fairly sure that this is different behaviour to a few months ago.

Example…

Here I’ve created a Team called Blog Demo

Now if I open up Power BI I will see the same workspace name there, and it’s a V1 workspace.

How can you tell it’s V1?

Note the V2 workspace has a Settings and Access buttons where a V1 workspace does not

Note: I have the “New Look” switched on, but regardless of that, V1 and V2 have different ways of getting to Access and Settings.

WARNING! If you delete this V1 workspace in Power BI, it will delete the entire Team, all conversation and all SharePoint files saved to that Team!!!

So it probably makes sense convert this to a V2 workspace so you can start saving Power BI Reports for this Team here, or maybe even delete it (check my warning at the end).

How to “Upgrade” a V1 workspace

In the screenshot above, you will see the words “Edit this workspace” circled in red for the V1 workspace. Click that.

Then click Advanced, followed by Upgrade now

It takes a few seconds after you click Upgrade, then click the Access Button

Caution: You’ll see that the entire Team has been given Member access. You probably don’t want this, since Row Level Security does not apply to someone with Member Access.

Either remove this member access completely (and share a different way – see below), or change it to Viewer Role.

If you chose Viewer Role, then everyone in the Team will be able to consume your report and Row Level security is also respected. They’ll also have viewer access to all of the the Power BI workspace reports and dashboards.

Alternative to Viewer Access

The best way to share reports is via Apps

When you share an App, you can give the entire Team permission to the App by typing the Team name into the Permission address box

Ideally you should tick the “Install this app automatically” box. If you don’t then the user MUST go to Apps – Get Apps to get the app, or you can send them a link.

BUT!

In order for them to see the report in Teams, you have to change how you added the report in the Teams window.

When you click the + to add the Power BI tile in Teams, you must choose Apps rather than Workspaces.

As mentioned earlier, unless the user has the App installed in their PowerBI.com view this tab still won’t work and they’ll see something like this

Summary:

If you want to use your workspace that was “auto-generated” by Teams to store and share your reports from then you must upgrade it to a V2 workspace.

Setting workspace access to Viewer for the Team is then the simplest approach. However, the workspace will then show up when that user logs in to Power BI.com. That may not be what you or the user want, but if that’s OK then go with that approach.

Notes:

• You can add any report or any App to a Team (it doesn’t have to come from the Teams channel workspace).
• If a user has access to a report in Power BI.com then they will have access to that report in the Teams view (even if the rest of the Team don’t).

Addendum (with warning)

Another side affect of upgrading to a V2 workspace is that it looks like you can now delete the workspace without deleting the underlying Teams content. PLEASE PLEASE test this out on some dummy Teams first. It looks like it works, but don’t take my word for it!

The post Securely sharing Power BI in Teams appeared first on Access Analytic.

Hard coded data sources in Power BI

Simple, hard-coded data tables can be useful data sources in Power BI (provided your data won’t change!). Here’s how to incorporate hard-coded data into your data model:

• Under the Home ribbon of Power BI Desktop, click “Enter Data”.

• Double click on “Column1” and rename the column as needed. Add additional rows and columns to the table by clicking on the * icons. Delete unwanted rows and columns by highlighting the row or column and pressing the delete key.

• Add data to the table as desired, change the table name in the bottom left-hand corner, then click “Load” to add the data to your data model.

• If you already have a table of data in Excel handy, you can simply copy the table from Excel, click Enter Data in Power BI, select the top-left cell and hit Ctrl + V to paste in the table contents:

• To return to the Create Table window and edit your hard-coded data, you can click on the cog wheel in the Source step:

The post Hard coded data sources in Power BI appeared first on Access Analytic.

We’re 100% still open for business and set up to deliver our highly rated Power BI and Excel “in – person” training via the magic of the internet!

This is great, as not only do Perth clients continue to get access to our training courses, folks around Australia and the world can now sign up for our on demand in-house or scheduled public courses.

For more information check out our Virtual Classroom or Virtual Course Calendar.

The post Power BI & Excel Virtual Classroom appeared first on Access Analytic.

Corkscrew Calculations using Dynamic Array Functions

If you’ve come to this page expecting information about how to open your wine bottles, you’re going to be very disappointed!  Sorry!

The corkscrew calculations we’re talking about here are those you’d use when calculating a balance sheet account in a financial model.

Accounts Receivable Example

The closing balance in one month becomes the opening balance in the next month, so you get a kind of corkscrew logic to your formulas (if you use your imagination!).

Dynamic Array functions are absolutely amazing and if you haven’t heard about or used these, take a look at our previous video & blog “How to build a financial model without copying & pasting a single formula“.  You’ll be blown away too!

Many people have said it’s not possible to do this kind of calculation using Dynamic Array functions and in one sense, they’re right because you can’t tell a dynamic array function to look back one period (at least not in March 2020).  If you do, it treats your formula as a circular reference.

However, we can simulate this behaviour with a little creativity by calculating the net movements in a balance and summing all of these.

To see how this works, take a look at the video and download the sample file.

Note: Office 365 is required to use this file (ideally monthly or insider channel)

Help Improve Dynamic Array Functions

There are some great ideas in Excel UserVoice so please support these and help make dynamic arrays even more useful!

• Add SUMCOLS and SUMROWS functions so we don’t have to rely on the ugly, difficult and non-intuitive MMULT function just to add up a column of numbers
• Copy the formatting when a formula spills (at the moment, you need to do this manually or pre-format the range you think your formula might spill to)
• Array formulas should inherit their number formatting from the source data (at the moment, you need to manually format your dynamic array formulas)
• Add a native corkscrew calculation to dynamic arrays so we don’t have to do all this messing around just simulate a corkscrew

The post Corkscrew Calculations using Dynamic Array Functions appeared first on Access Analytic.

Does your contract Rise and Fall let you down?

When you’re providing products or services in a long term relationship there is usually a contract which will typically include an Escalation (Rise and Fall) formula. The intent of the Escalation Formula is that cost increases or decreases should be passed on so that the contract pricing is fair to all parties (the seller, the buyer and even sub-contractors to the seller).

Excel models are used to track movement of indices but often use flawed thinking. The two most common errors are:

• Fixed factor basis. Many Rise and Fall formulae include fixed factor declaration ..but cost components rarely change at the same rate. For example: If fuel represents 30% of the the total cost and fuel increases by 25% then (if all other costs remain the same) fuel now represents 34% of your costs.
• Discounting fixed costs. Some formulae allow say factors totalling only 90% on the basis that administration or profit are 10% and should be fixed. This is flawed thinking and results in further erosion.

Well ..what is the answer? In my opinion the only fair (fair for all parties) solution is an adjusting factor formulae. Over many years I worked with contract Rise and Fall and converting them to adjusting factor formulae was a “win/win” for all concerned.

Building Excel systems and rise and fall models which keep track of indices and their movements and apply an adjusting factor formulae will ensure your business recovers cost increases, passes on savings and consequently builds trust and relationships through integrity.

If you’re interested in protecting your profits and being fair to customers lets have a discussion?

The post The Rise and Fall of the escalation formula appeared first on Access Analytic.

Power Query and a Key Word search (Episode 1)

This demonstration came about due to a real life scenario where I needed to categorise a transaction listing (like a bank statement for example) so that I could generate a summary report of categories of spend.

All sorts of tips can be picked up from this tutorial:

Table tips, Conditional Column, Merging a query with itself….

And just to be fancy we throw in a cartesian join, Comparer.OrdinalIgnoreCase, and Table.Buffer

It’s not as scary as it sounds!!

We have the demo file here for you to use:

Excel and Power BI are awesome thanks to Power Query. Learn more from Gil Raviv’s book, definitely one to add to your collection.

As I mentioned in the video I’ll do a few more on this topic where I approach the same problem but use:

a) a couple of Excel functions (including the new FILTER function)

b) using a Power Query custom function

Hope you find it useful!

The post Power Query and a Key Word search appeared first on Access Analytic.

Get more out of Power BI Slicers

The slicer visual in Power BI is a simple, yet powerful means of selecting criteria to filter your reports.

Here are two useful tips on how to get more out of this visual:

Tip #1: Turn Off Single Select

By default, Power BI slicers behave slightly different to their table filter counterparts in Excel. When a slicer is created in Power BI, multiple items can only be selected if the user holds the control key down on their keyboard while clicking.

To more easily select multiple criteria from a slicer, select the slicer and then turn off the “Single Select” toggle underneath Format/Selection Controls as per the below:

Tip #2: Turn On Slicer Search

Table filter search capability in Excel is very useful when dealing with lots of items that are appearing in your slicer window.

To replicate this functionality in Power BI, click the “…” icon in the top right hand corner of the slicer and select “Search”. Note that when you click the “…” icon again, a tiny yellow bar will appear on the left to indicate that the search capability has been activated:

To filter the items that appear in the slicer drop-down list, click “All”. Click in the white area to the left of where a new magnifying glass icon has appeared, and then begin typing to filter the slicer accordingly.

To find out more about the Amazing capabilities of Power BI check it out here.

The post Get more out of Power BI Slicers appeared first on Access Analytic.

Use Shared Excel Templates

Smart use of Excel (or other Office applications) should feature use of templates.

In a business those templates should be “Workgroup” templates (common templates available across the business). Workgroup templates offer Efficiency, Consistency, Accuracy, Automation and Professionalism.

The purpose of this article isn’t to expand on the importance of workgroup templates … but if your business isn’t using them then you’re potentially exposed to a number of risks and at the very least some review of processes and training would be beneficial.

This article assumes that you understand the values of workgroup templates but haven’t figured out how to enable them in Excel ..and based on my observations this is a fairly common problem.

The (long standing) established method for setting the workgroup templates (for Office) is to do this under Microsoft WORD options (make sure you have a document open):

Once completed you’ll have the workgroup templates available when you select >File, >New from these Office Applications:

The post Shared Excel Templates appeared first on Access Analytic.

Power Query and a file selector Macro

If you have an Excel file containing Power Query and need to send it to colleagues, read on.

If they need to be able to change where the source data for the Query is being pulled from then read on.

If you want to make the process as simple as possible for them then watch this….

File Selector Macro Download

As promised in the video here’s the method for referencing named cells and using them as parameters in Power Query.

The post Power Query and a file selector Macro appeared first on Access Analytic.

by Wyn Hopkins

Did you know about these hidden Power BI features?

It seems unfortunate that for such an awesome product there are a few user interface features that are very difficult to discover.

I have trained hundreds and hundreds of users in Power BI, and I get to experience people hitting these issues over and over again.

Power Query (5 tips)

1. Connecting to a whole Excel workbook

When you connect to a workbook it’s obvious you can pull data from Tables or sheets as there are nice little check boxes.

What isn’t obvious is that you can Right-Click on the “folder” to then access all the file contents in one go. This can be great if you want to consolidate a whole bunch of sheets or tables in one go.

2. Multi column change Data Type by holding Ctrl or Shift

If you want to change multiple columns to a particular data type (say Text) then after multi selecting the columns using Ctrl or Shift you should keep your finger on Ctrl or Shift when you click the Data Type icon and all the columns will be changed in one hit.

3. Data Profiling 1,000 rows

Power Query can show you the quality and distribution of the data you are importing but by default it’s only evaluating the first 1,000 rows. Toggle to the entire dataset by clicking on the wording at the bottom of the screen.

4. Add a description to a step via Properties

Add a description explaining why you did a certain thing. You’ll thank yourself in 3 months time.

Note: I also add an i to my step name to flag the fact that there is more information

5. Checking for Query Folding

When connecting to a SQL Database (and a few other sources) Power Query can often get the database to do some of the hard work in terms of filtering etc before the data is pulled into Power Query. This is termed Query Folding. To check if this is happening you right click on an Applied Step and check to see if “View Native Query” is lit up.

In this case Native Query is greyed out so query folding isn’t happening (I’m actually getting the data from an Excel file in this image so it’s not surprising)

PBI Desktop (6 tips)

1. Slicers as Big Buttons

When you create a Slicer for Year you may get a slider or tiny check boxes but what you may want are some big buttons

It’s not intuitive how to get there (sometimes the tiny down arrow in step 1 isn’t even visible if your slicer is too shallow)

2. Decimal Places

Changing the decimals shown for a number inside a visual should be easy, and it is if you know how… The decimals box in step 3 sometimes looks “greyed out” and the spinners do nothing! However, just type a 2 in the box and away you go…

3. The mysterious case of the invisible conditional rules dots…

Did you know you can add dynamic titles to visuals? Did you know these 3 vertical dots are hidden everywhere, but they only show up when you hover over them!

This is the result

Note: this was my measure “Selected Year” used in step 3 above

These little dots are everywhere when you start looking and you can add dynamic labels, hyperlinks, and colour formatting to all sorts of things including buttons.

4. Custom Number and Date formatting

You can format your dates and numbers any way you like (almost) but NOT in the formatting area of the Modelling tab…

instead you do it via the Model (relationship view) tab

Note: You can click on the Learn More hyperlink (see just above the number 5 in my screenshot) to find out what formats are possible

5. Dragging Measures

Also in this Model view (and only in this view) you can drag measures between tables

6. Drawing a horizontal line

A simple line can make a big difference to the read-ability of a report. It helps focus the user’s eyes on which direction they are meant to approach the report.

Drawing a vertical line is easy..

But knowing you have to go to the format panel and rotate it 90 degrees to get a horizontal line is not something new users easily discover themselves.

PowerBI.com (also referred to as “the service”)

Testing Roles (Row Level Security) in PowerBI.com

This is possibly my “favourite”

Row Level security in Power BI is a feature where you can dynamically change what data a user can see based on their email login. You give these permissions to different roles and if the user who logs in is assigned to that role (by you) then they only see a restricted, filtered, view of the data. This is really powerful. e.g. the Drilling Team only get to see costs relating to the Drilling.

After you’ve added some Roles to Power BI desktop and publish your report it you can get to a screen like this..

Do you see the dots?

No?

They show up once you hover over them… ????

And there, on it’s own, is a Test as role option.

Summary

I love Power BI, it has amazingly powerful features. The speed of updates over the last 4 years or so has been crazy and full respect goes out to the folks working to deliver this world beating BI tool.

This article doesn’t flag any of the many awesome things Power BI does brilliantly. It’s really meant to be a helpful pointer to those items I find new users struggle with the most.

With the new Ribbon (released in preview recently) steps are clearly being made towards making Power BI features more discover-able, and I applaud this progress greatly.

If you’d like your team or colleagues to get some great training in Power BI and set them up for the best possible start then you’ll find details of our public courses and in-house training here.

We’ve trained way over 1,000 people from all types of organisations and departments in Australia and all around the world. We’re very proud of the fact that 85% of attendees giving feedback rate the courses a 9 or 10 out of 10, and don’t worry, the remaining 15% gave us a rating of 8 !

The post Revealing Power BI’s Hidden Gems appeared first on Access Analytic.

Use HYPERLINK and XLOOKUP to jump to a result

XLOOKUP will return a range so we can use it inside other formulas such as HYPERLINK to be able to jump to our result.

Want to learn more? Jump on to one of our courses

We’ve trained over 1,000 people from all types of organisations and departments in Australia and all around the world. See what attendees are saying about our courses:

The post HYPERLINK and XLOOKUP to jump to a result appeared first on Access Analytic.

Financial Modelling using Dynamic Array Functions

Best practice financial modelling has always been to enter your formulas in blocks: enter a formula, then copy this across and possibly down also, making sure you have your absolute and relative references set correctly.

This is very useful because it is faster to build a model, easier to enter lots of formulas, less error-prone and easier to audit … all very desirable things when you’re constructing a financial model!

Excel’s dynamic array functions (more info here) take this concept to a whole new level because they can be setup to automatically copy themselves.

This means they are perfectly suited for financial modelling because they make it impossible to simply change a formula halfway along a row or manually overwrite a formula with a number.

What’s great about Financial Modelling using Dynamic Array Functions?

If we setup our dynamic array functions and formulas correctly, we only need to type each formula once, and it will automatically spill (i.e. copy) itself across and down (if need be).

In addition, by changing one number, our entire financial model can be extended to incorporate additional periods.

And, by inserting additional row(s) into our setup tables, we can automatically add as many new products, staff, projects or whatever else we’re modelling as we want … and these new items are automatically inserted into our model with the correct formulas at every relevant point.

Dynamic array functions can also be used to automatically summarise our detailed calculations and generate all our financial model reporting using the standard SUMIFS formulas and others like these.

In addition, dynamic array functions can even be used to produce simple scenario managers.  These have a number of advantages over data tables since all the results are live all the time plus they’re about 1,000 times faster.

Because all dynamic array functions are processed in arrays (as you might guess!), they are incredibly fast, even over millions of calculations.  I don’t need to tell modellers how useful this will be for their incredibly complex are ever-growing financial models.

In short, dynamic array functions are absolutely revolutionary when it comes to financial modelling!

You can also use them for balance sheet calculations, although this is a little more complex at present. More details in our blog on Corkscrew Calculations.

Take a look at the video & the free example file provided to see how we built an entire financial model without copying and pasting a single formula!

Note: Office 365 is required to use this file (ideally monthly or insider channel)

Are there any Disadvantages?

So far, I’ve focussed on the incredible advantages of using dynamic array functions for financial modelling, however, there are a few things to consider:

• Auditability: it’s not possible to trace a particular formula in a given cell back to see its individual components.  A reviewer can simply look at the original formula entered and check that this is correct, however, unless they’re used to these kinds of formulas, they may find it more difficult to answer the question of “Where does that number come from?”.  On the plus side, some people may find dynamic array spreadsheets easier to audit because there is no way for formulas to be modified halfway across a row.
• Complexity: the formulas are a little more complex and some functions that are very simple in standard Excel functions are quite complex if done using dynamic array functions (e.g. SUM doesn’t spill so we used the obscure MMULT function to simply add a column of values!).  Hopefully, this will change over time as more dynamic array functions are added.
• Intuitiveness: until you get used to thinking in arrays, the new dynamic array functions aren’t as intuitive as the standard Excel functions e.g. =MATCH(D7#,D7#,0) is a weird way of saying, “Match the value of D7 in this column with the spilled array of values that start at cell D7”.
• Unexpected Behaviour: not all functions spill and some don’t behave like you might expect them to e.g. EOMONTH doesn’t spill unless you add a value to the month offset, and none of the logical functions (AND, OR, NOT) don’t spill.  However, DATE spills, as does =(A1#>0)*(A1#<100)
• Compatibility: like most new functions, the dynamic array functions aren’t compatible with earlier versions of Excel so some will display #NAME errors in earlier versions while others will appear are array formulas that have been entered using Ctrl-Shift-Enter (refer to Excel’s compatibility checker for more information).

Help Improve Dynamic Array Functions

There are some great ideas in Excel UserVoice so please support these and help make dynamic arrays even more useful!

• Add SUMCOLS and SUMROWS functions so we don’t have to rely on the ugly, difficult and non-intuitive MMULT function just to add up a column of numbers
• Copy the formatting when a formula spills (at the moment, you need to do this manually or pre-format the range you think your formula might spill to)
• Array formulas should inherit their number formatting from the source data (at the moment, you need to manually format your dynamic array formulas)

The post Financial Modelling using Dynamic Array Functions – no Copying & Pasting! appeared first on Access Analytic.

How and why to use XLOOKUP

It’s August 2019 and we get our first glimpse of this glorious new function.
In May 2016 I raised a UserVoice request to create a simpler and safer version of VLOOKUP and INDEX MATCH.
As of May 2020 many Office 365 users now have it, and everyone else on 365 will get access to it in the July 2020 update release.

The idea received a lot of votes and gradually momentum built until it was significant enough to really interest the Excel Team. Joe McDaid, Senior Program Manager on the Excel team, ran with the idea and some clever folk got involved and began working on this solution.

XLOOKUP is here

=XLOOKUP( CellValueToLookFor, ColumnOrRowToLookIn, ColumnOrRowToReturn, [other optional things] )

It will eventually put an end to the debates of which is better VLOOKUP or INDEX MATCH. I say “eventually” because while those of us on Office 365 subscriptions will hopefully get access to this in the next 6 months or so, those folk who bought Office 2019 or older versions Excel will never get to use this unless they change to an O365 subscription.

XLOOKUP is easier to write than both formulas, and harder to get wrong. Users often misunderstand or omit optional match / search criteria or hard-code numbers into the VLOOKUP formula which then causes unintended errors when columns are inserted or deleted.

Let’s Demonstrate

Watch the video and/or read on…

(XLOOKUP was released to all channels of Office 365 in July 2020, it will never become available in Excel 2019 or earlier versions)

LOOKING LEFT (Simpler)

One of the well known problems of VLOOKUP is that it can’t look to the left.

OK that’s not entirely true: You could use CHOOSE{1,2} inside a VLOOKUP

=VLOOKUP(F8,CHOOSE({1,2},tblBlue[Food], tblBlue[Amount to Left]),2,FALSE)

But that is not a nice formula to remember or explain. With XLOOKUP it’s now simple…

= XLOOKUP( F8, then select the column to look in, then select the column to return )

=XLOOKUP( F8, tblBlue[Food], tblBlue[Amount to Left] )

That’s it! SIMPLE!

DEFAULT EXACT MATCH (Safer)

One risk with VLOOKUP and INDEX MATCH is if you forget to specify an Exact Match by using FALSE or 0.

In this screenshot the formula doesn’t have a FALSE on the end

It gives the right answer -for now!

But look what happens if I change Cherry to Grapefruit

The approximate match gives us the result for Banana!

No issue with XLOOKUP as the default behaviour is EXACT MATCH.

SAFER!

ALSO REPLACES HLOOKUP

The formula above in yellow results in 3, as it returns the value from the range P7:AA7

For most users that’s it. Simpler and Safer than VLOOKUP and INDEX MATCH

Cherry on top…

As of 30th October 2019 we also have an Optional [IF NOT FOUND] parameter as the 4th option largely thanks to people voting via the link below!

=XLOOKUP( A1, C1:C100, B1:B100, “Item is Missing”, [other optional things] )

or

=XLOOKUP( A1, C1:C100, B1:B100, “Please Add Item to Lookup list”, [other optional things] )

**** This section was in my original article and I’m leaving it in for posterity*** While it seems wrong to raise any comment about this awesome new function, I’d also like to have an optional parameter that allows me to avoid having to use a separate IFNA or IFERROR wrapper when a match isn’t found. Bill Jelen has a UserVoice item here… Please vote…

Optional Parameters (more advanced use cases):

IMPROVED APPROXIMATE MATCH

We’ve always been able to use VLOOKUP without a FALSE when we are genuinely looking for approximate matches, such as nearest value to the value you are searching for.

e.g closest result lower than 23 will give us Banana

But…

The source table MUST be sorted in ascending number order and we can only find an exact match or the next smallest item.

XLOOKUP does not require the source table to be sorted for approximate match to work.

=XLOOKUP(F8, tblData[Amount], tblData[Name], ,-1) gives me Lisa and +1 gives Jackie.

AWESOME! Note the two commas after [Name]. This is because I haven’t entered the optional [if not found] message.

or including the optional [if not found] parameter

WILDCARD SEARCH IS NO LONGER THE DEFAULT (SAFER)

If you select the value of 2 rather than -1 or 1 then you can do a Wildcard search. In the image it finds the first item beginning with A, whereas without setting it as option 2 then you would only get a result if it could find A*

If I wanted to look for a specific missing character I would search for A? rather than A*

Note: INDEX MATCH and VLOOKUP perform this wildcard action by default, which could result in unintended consequences. e.g. imagine this scenario where you are actually searching a column where items have * or ? in the item names

SEARCH LAST TO FIRST OPTION

Finally, there is one last optional parameter, Search from last to first.

By default XLOOKUP searches down a list and finds the first occurrence. However, by tagging an optional -1 at the end you can find the last occurrence (i.e. it starts searching from the bottom up)

=XLOOKUP(H6,tblOne[FirstName],tblOne[Output 3],,,-1) results in 6,000. Without the -1 at the end we would’ve returned 4,000 (this first match for Betty looking from top down)

There are 2 additional options under this final parameter relating to Binary Search. Don’t worry about these unless you’re a devil for speed as this option might (and I repeat might) be a bit quicker than the default options under the new calculation engine. For me and 99% of others we’ll never use this option.

Multi Criteria Lookup

One last tip. The new Dynamic Array version of Excel which is starting to role out among Monthly Channel users allows us to do Multi Criteria lookup without using Control Shift Enter.

=XLOOKUP( C12&D12, tblGrey[FirstName]&tblGrey[LastName], tblGrey[Start Date] )

This results in 4th August being returned

And even multiple outputs

There’s more examples to follow but that’s enough for now.

Stay tuned / connect / follow for future examples and updates.

Extra Info…

If you want more detail, or to find out about XMATCH check out these links..

XLOOKUP Release Blog

XLOOKUP Help

XMATCH Help

Business News article Tech scene: Spreadsheet users win as problem solver excels

The post Using XLOOKUP appeared first on Access Analytic.

Adding a password box to a Power BI report

Here’s a process that allows you to add a password input box to your Power BI report.

DO NOT, REPEAT DO NOT use this for sensitive data.  We have no idea if there are ways to hack this but it’s just an interesting exercise in using some features in Power BI. This is not to be used as a substitute for proper security. See our warning at the end.

To play around with the finished report and see how it works download here.

The concept

Once you can capture a name and password you can give access to otherwise hidden pages or even set certain measures to zero by multiplying by zero if the password isn’t correct.

The end result

This is what the user would see when they open the report

We’ve published the report for you to try it out: Power BI Report

(click the Show Passwords button to see the user names and passwords to try out)

After clicking the login button 2 Text boxes are shown

The Text boxes are actually the Text Filter custom visual from the marketplace

The first text filter requires the user’s name and the 2nd requires their password.

After entering the correct user name and password a single button appears (actually it’s a pie chart with the number 1 as a value) which enables the user to then drill-through to the main report

Navigation through the “hidden” part of the report is then driven by buttons and bookmarks.

How to set it up

This is how we initially set up the User and Password table.

Table Name: USER LOGIN

(We just used Enter Data to set this up)

Then reference that query and remove the User column to just give you a password table

Table Name: USER PASSWORD

We also added a table called PieChartLabel for the “button” label to prompt the drill through

These all get loaded as disconnected tables

Now we move back to the text filter custom visuals…

The first one is set with the User Column from the User Login Table

The second search box references the Password column from the User Password Table

Time for 4 measures

Firstly, we need 2 measures to “harvest” the values entered in the search boxes

User Entered = SELECTEDVALUE ( ‘User Login'[User], “No User” )

Note: the search box is filtering this table leaving just the one name in the table

Password Entered = SELECTEDVALUE ( ‘User Password'[Password], “No Password” )

Note: the search box is filtering this table leaving just the one password in the table

We then need a measure to get the Password that was required for that User and see if it matches the password entered.

Password Required =

LOOKUPVALUE (

   ‘User Login'[Password],

   ‘User Login'[User], [User Entered]

)

The last measure needed generates a value of 1 (True) if the [Password Entered] = [Password Required]

Password Control =

IF (

    OR (

[Password Entered]=BLANK (),

[Password Entered] <> [Password Required]

    ),

    0,

    1

)

Then we can use this Password Control measure in pie chart along with a label from the Entry table

THE END RESULT / TRICK

The result of this is if the password entered doesn’t match the expected password then the measure results in the value 0 and the Pie Chart visual will not display the drill-through option

Once the drill-through option is available then you have full access to the report via buttons and bookmarks.

****Not quite the whole story – KEEP READING!!!!****

On the face of it, it appeared to work.

On testing we realised that if the customer name is entered then as soon as you enter the first letter of the password the screen unlocks. This is due to the nature of using the search box, it filters the table with all words beginning with what you are typing.

So a little rework is required….

We used Power Query to append a duplicate set of customer data with passwords but removing the last letter of both the customer name and the password for the duplicate part.

each Text.Start([User],Text.Length([User])-1)

This way, when the customer name and password are being typed into the search boxes the full name and full password must be entered before the search box returns a single record for customer and single record for password.

Then it works well.

BE WARNED

Just like password protection inside Excel this is not a process to rely on for proper protection.  The Password you type in is obvious to anyone looking over your shoulder and it’s probably easy to hack this, so please don’t rely on it for anything important!

Read about proper ROW LEVEL SECURITY if you really want a robust solution to limit the data that users can access.

The post Adding a password box to a Power BI report appeared first on Access Analytic.

In July 2019 conditional formatting Icon Sets were added to Power BI and it caused us to revisit how they work in Excel.

In Excel you would highlight a range of numbers and choose Conditional Formatting : Icon sets. But you’d get odd results such as in the screenshot where negative numbers have up arrows.

This happens because the default option is set with the values split into 3 groups of the lowest 33%, middle 33% and upper 33%.

The Maths (for those number junkies out there)

To see how the calculation actually works, take a look at this example (thanks to fellow MVP Tim Heng for setting us straight on this)

So using the Min and Max of the selected values (A5:A8) the Total Difference between this Min and Max is calculated (e.g. 9 in the left hand side example)

Then the difference between the value being considered (e.g. cell A7 contains 4) and the Min (1) is calculated giving 3 and finally that is expressed as a % of the total difference = 3/9 = 33.33% which sneeks over the 33% threshold and the icon in A7 turns yellow.

In the right hand image the max number in E5:E8 is 11 therefore the difference is 10. Then looking at a particular value say E7 we have the number 4. So 4 minus the min of 1 is 3 and then 3 /10 = 30% which falls bellow the 33% threshold and the icon is Red.

This default setting generally isn’t useful and we always change it to something more practical

Our process is to click on Manage Rules and change the rules to something to control more directly by switching the Type (down the bottom right of the image) to Number instead of Percent

Power BI

A similar methodology is required for Power BI

You are then presented with this screen:

In order to apply simple negative / positive formatting you should set it up as follows:

The options presented in Excel and Power BI are confusing so hope this is helpful.

The post How to use Icon Sets in Power BI and Excel appeared first on Access Analytic.

Want to learn from the best? Jump on to one of our courses

The post 5 Great Excel tips from 5 Excel MVPs appeared first on Access Analytic.

Dynamically consolidate multiple ranges when loading Excel files from Folder in Power Query

We’ve come across a task where we need to consolidate a client’s budget data from Excel files, each representing a division, where any of the files may have multiple similarly structured tabs with sub-divisional data. In our project, each subdivision tab contains a local range rng_data, which includes 12 months of forecast data, row headers and column headers. Below is a snapshot of source data:

We are going to use Get Data from Folder to load data into Power Query (Data>Get Data>From File>From Folder). After opening the ‘Combine Files’ dialogue (by clicking ‘Combine & Edit’), we come across the issue that we can only select a single range to combine data from, while the similarly named Excel local ranges get an incremented numbering at the end (as shown on the image below, where the range name for Subdivision B tab gets ‘1’ at the end of ‘rng_data’).

At this stage, let’s select rng_data and click ‘OK’. As a result, Power Query will return consolidated data for Subdivision A (only) in each of the division files.

From the Source Data preview window we can see that we need to rectify data headers and unpivot forecast data to show it in one column. We could easily accomplish these tasks in the ‘Transform Sample Files from Source Data’ query by selecting ‘Use First Row as Headers’ and unpivoting columns other than ‘Forecast Item’, ‘Division’ and ‘Subdivision’. However, before doing it, we need to bring in missing data from other subdivisions.

Here are the steps how we can accomplish this:

Step 1: Let’s get rid of the ‘Navigation’ step in the ‘Transform Sample File from Source Data’ query, which will leave us with the ‘Source’ step only.

Step 2. In the ‘Name’ column let’s set a filter to show anything beginning with ‘rng_data’

Step 3. Remove all columns apart from ‘Data’ column, which contains source data by selecting ‘Data’ column>right click>’Remove Other Columns’. Rename the step to ‘SourceTables’

Step 4. Expand column ‘Data’ by clicking the arrows pointing sideways as shown in the image below.

This step returns combined data for all local ranges named ‘rng_data’. However, we can see from the formula bar that the ‘Expanded Data’ step hard-codes column names, which may cause query to break if new columns are subsequently added to the source data.

A better approach would be to dynamically generate a list of column names and replace the hard-coded column names with this list.

To achieve this we will apply ideas mentioned by Marc in his recent post ‘Dynamically expanding and appending tables with Power Query’. In particular, we will use Table.ColumnNames function to generate a list of column names in a new column added immediately after the ‘Filtered Rows’ step. We will do it as part of the next step.

Step 5. Select the previous ‘SourceTables’ step in ‘Applied Steps’, then Add Column>Custom Column. Type ‘=Table.ColumnNames([Data])’ in the ‘Custom column formula’ window and click ‘OK’

This will add a new column with lists of headers for each table.

Step 6. Select ‘Expand to New Rows’ at the top of the ‘Custom’ column.

Step 7. Right-click the ‘Custom’ column header, after which we get the following table

Step 8. Now we need to create a list of column headers, which we can use in the subsequent ‘Expanded Data’ step to substitute the hard-coded column names. The easiest way to create the list is to right click on the ‘Custom’ column header, and select ‘Drill Down’. However, while this action would, helpfully, generate the code ‘#”Expanded Custom”[Custom]’, it would, unhelpfully, wipe away the ‘Expanded Data’ step, where we need to use the newly created list in the first place.

To work around this, we can slightly modify the code in ‘Expanded Custom’. In particular, while having the ‘Expanded Custom’ selected, add ‘[Custom]’ at the end of the code in the formula bar, so that it looks as follows:

= Table.ExpandListColumn(#”Added Custom”, “Custom”)[Custom]

This would generate a list column names. This list, however, contains duplicate values, which we will deal with in the nest step.

Step 9. We assume that the number of columns is exactly the same across all input files, therefore, it is safe for us to remove duplicates from the list. To do so, right-click on the list header and select ‘Remove Duplicates’.

Step 10. Now, we are able to use the list in the ‘Expanded Data’ step. At first, let’s rename the ‘Removed Duplicates’ to ‘ListOfColumnHeaders’ (as shown on the image below) to make it consistent with what the step returns.

Now is the time to modify the ‘Expanded Data’ step to return data from all ranges of the source files.

At this stage, when you select the step ‘Expanded Data’ in ‘Applied Steps’, you will get the following error message.

This is because it refers to the preceding ‘ListOfColumnHeaders’ step instead of the original table with data in the ‘SourceTables’ step. Therefore, we need to repoint the step to ‘SourceTables’.

At the same time, we want to replace all hard-coded column names with ‘ListOfColumnHeaders’. As a result, we will get the following string of code:

= Table.ExpandTableColumn(#”Filtered Rows”,”Data”,ListOfColumnHeaders)

The step will generate combined data from all divisions in the source excel files:

Since we have replaced the hard-coded values with a dynamic list, any new column additions in the source data will automatically be processed by the query without any errors.

Step 11. Now we can use the first row as headers by selecting Home>Transform>Use First Row as Headers.

Step 12. As we can see, headers are repeated for each local range loaded by the query. Since we have now got correct headers, we can get rid of the other header rows by filtering out ‘Forecast Item’ value in the ‘Forecast Item’ column

Step 13. Last modification in the ‘Transform Sample File from Source Data’ query is to unpivot columns other than ‘Forecast Item, ‘Division’ and ‘Subdivision’. To do so, select the ‘Forecast Item, ‘Division’ and ‘Subdivision’ columns > right click > ‘Unpivot Other Columns’. Subsequently, rename the ‘Attribute’ column name to ‘Date’. Here is a snapshot of data we will have after the modification.

Step 14. Finally, we need to modify the ‘Changed Type’ step in the ‘Source Data’. It would be the easiest to remove the existing ‘Changed type’ step (if it was automatically generated by the query), and recreate a new one manually. This is required as the old ‘Changed Type’ would refer to old columns, which do not exist anymore. The resulting query contains combined data for all divisions and subdivisions form the files saved in our source folder.

You can download files from the link below if you wish to re-create the steps above, or to have a look at the solution file. Please change the folder path in the ‘Setup’ tab to where you saved the source files.

The post Dynamically consolidate multiple ranges appeared first on Access Analytic.

Want to learn more? Join one of our courses

The post Create a dynamic drop down list in Excel appeared first on Access Analytic.

Excel Summit South is hitting Perth

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.

The post Excel Summit hits Perth appeared first on Access Analytic.

SQLSaturday training event

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.

To find out more visit SQLSaturday here.

The post We’re going to SQLSaturday 2019 appeared first on Access Analytic.

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.

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.

The post Power Pivot basics appeared first on Access Analytic.

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.

Your data is now ready for analysis!

The post 5 Ways to use Power Query more effectively appeared first on Access Analytic.

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.

What a magnificent tool Power Query is!

The post Read a Range Name, Table or Sheet from all Excel files in a Folder using Power Query appeared first on Access Analytic.

Move over Siri, Alexa

Get information from your Power BI reports faster by using your voice.

Excel file to a voice generated Power BI Mobile report. Learn how in less than 7 minutes!

Here’s how to build a voice generated report on a mobile starting with Excel and ending in Power BI Mobile .

It takes 7 minutes to build from raw data to interactive mobile report, including some refresh-able data transformations – that’s ridiculous!

This would have required multiple developers and taken days in the past.

Want to learn more

Take the next step and access your data seamlessly today with Microsoft Power BI in Perth. Take a course or contact us on

+61 8 6210 8500 or by email or leave your details below.
[contact-form-7]

The post Voice generated Power BI Mobile report appeared first on Access Analytic.

Loading 10 million rows of data into Excel

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.

Check out the 3D Map created using just Excel!

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.

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.
[contact-form-7]

The post 10 Million rows of data in Excel? appeared first on Access Analytic.

AI and the Future of Financial Modelling

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.

Evolutionary Models

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.

Conclusions

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!

Further Reading

• Expert Systems 
• On Conceptual Modeling: Perspectives from Artificial Intelligence
• Castaway Forecasting Software 
• Modano Financial Modeling
• Invest for Excel
• What is Prescriptive Analytics 

Contributions gratefully received from Lance Rubin (Model Citizn) and Miron Meydan (Parity Analytic)

The post AI and the Future of Financial Modelling appeared first on Access Analytic.

VLOOKUP v INDEX MATCH – You decide

Let’s think of VLOOKUP as a screwdriver and INDEX MATCH as a power drill…

wait…wait…. I’m not saying INDEX MATCH is faster than VLOOKUP that isn’t what this analogy is leading to.

If  I need to screw something together I just pick up the screwdriver and hey-presto done.    Why would I bother unpacking the Power Drill, plugging it in and finding the right toolbit?

Then I need to drill a hole.   In this scenario I need to get the Power Drill out.

For me, something changed several years ago when they invented the cordless drill that never needs charging and made it as easy to carry as a screwdriver.

This came about as the result of 2 things

1. Tables were introduced in Excel 2007 with it’s structured referencing.

2. I came up with an autocorrect trick to help write the INDEX MATCH formula (see below)

So for the last 10 years, regardless of context, I pick up my cordless drill. The screwdriver is just gathering dust.

Once in a while I might need a screwdriver to open a tin of paint though….

Here’s a short video showing VLOOKUP v INDEX MATCH in action, including our Auto Correct trick mentioned below

Summing up

It’s not that VLOOKUP is wrong or bad, used in the right context it works great. It’s just that once I discovered the benefits of INDEX MATCH I’ve never had to think about the context as it works really well all the time.

Want to end this debate for future users?

Microsoft are listening to users expressing their views via voting on the UserVoice sites. We’d like to end the debate by having a new formula that takes the best of both, but you need to vote to make it happen. Vote here

So how do you “get on board” with INDEX MATCH?

If you want to see how to write a really quick INDEX MATCH then check out these articles here –

Using Autocorrect to write your INDEX MATCH formula

Clicking a button to write your INDEX MATCH formula with our Great 48 Toolbar (watch the video below before clicking on this link)

The post VLOOKUP and INDEX MATCH appeared first on Access Analytic.

Power Query saves you days of time manually manipulating messy data. This leads to improved productivity and efficiency.

A quick tip to save you time is to generate a % of group total. It’s relatively straightforward in Excel, but it’s trickier in Power Query.

This demonstrates a couple of techniques including Group plus merging a query with a previously applied step.

Thanks to a couple of extremely helpful folk including Imke Feldman (https://www.thebiccountant.com/) and Dale Cameron (https://www.linkedin.com/in/dale-cameron/) for pointing us in the direction of this little gem of a step in order to avoid having to do the merge query part at all !

What is Power Query

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.
[contact-form-7]

The post Percent of group in Power Query appeared first on Access Analytic.

Connect to Excel files using Power Query

Power Query can connect to Excel files held in OneDrive for business and in SharePoint. But it’s not obvious how. Whether you use Excel or PowerBI this approach will work.

Also, for a pure  “M” code approach to SharePoint you can use something like this to get your data from a Table in an Excel file-

let

//Set your variables

mySite = “https://aasolutions.sharepoint.com/sites/clientdemos/“,

myFile =  “Reconnenct Data Demo.xlsx”,

myTableName = “DataSet”,

//this is then the code….

Source = SharePoint.Files( mySite , [ApiVersion = 15]),

SelectFile = Table.SelectRows(Source, each ([Name] = myFile )),

GetContent = Excel.Workbook(Binary.Combine(SelectFile[Content])),

GetTable = GetContent{[Item=myTableName, Kind=”Table”]}[Data]

in

GetTable

What is Power Query

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.
[contact-form-7]

The post Connect to files on OneDrive and Sharepoint appeared first on Access Analytic.

Dear CFO / CIO,

I am writing on behalf of your office staff – they need your help.   There are FOUR decisions you can make today that will make your business a more efficient and a more engaging place to work.

1. Commit to providing regular training for your staff

Picture a scenario where a business employs someone to work on the factory floor but gives them no training in how to use their machinery. The employee says they’ve used “machinery” before so therefore they must know what they are doing. The business just relies on that person copying others and picking up pointers, hints, tips, good and bad habits.

Not very efficient and probably quite costly in the long run.

Why do we view office staff differently?

• Companies often assume that their self-taught staff inherently know how to do things the best way.
• Rarely is training in “the tools of the trade” offered as a default.
• Few companies provide ongoing structured software training for their staff.

What’s the business benefit?

• The speed of change and functionality improvements in business software is amazing. Never have things improved so quickly and the pace of change is only going to increase.
• In the absence of regular training staff are likely to be doing things the long, inefficient way, simply because they aren’t aware of what’s now possible.  This training should be focussed on the specific software they currently use, but it’s also important to let them explore and be made aware of a broader range of capabilities.
• Getting the best value from the software you have requires staff to be educated on what’s available to them.
• I’ve experienced people almost breaking down in tears when they see how existing technology can save them days of effort every month. The tears are part joy and part sadness as they realise they could have been doing things differently for the last 3 -5 years but just didn’t know it was possible.

All businesses will benefit from happier more productive staff.

2. Identify the “information superusers” in your business and give them the equipment they need

• We are in the age of data, and staff who spend their days analysing and reporting will benefit greatly from the latest processors and machines with 16GB of RAM (or more).   More RAM means more “room” for processes to take place and things just work faster.
• Imagine pouring water from a bottle with a narrow neck compared to pouring from a jug.  The second is a lot faster.
• Note that Excel users will only benefit from more RAM if they 3. also have 64 Bit Office – see item 3.

What’s the business benefit?

• Staff will be able to perform more work and analysis in a more efficient manner.
• It’s a short-term cost for a long term gain, including increased staff satisfaction.
• Staff developing reports in Excel, Power BI or other BI tools will love you for it.

3. Set a goal of implementing 64 Bit Microsoft Office in your organisation

• When installing Office, you can choose 64 bit or 32 bit
• There’s no cost difference, it’s just an option to select when installing Office

What’s the business benefit?

• It’s highly likely that most of your office staff spend many hours of their day using Excel
• 64 Bit Excel is simply faster and crashes less than standard 32 Bit Excel
• 64 Bit Excel can utilise all of the RAM on computer while “normal” 32 Bit Excel can only access 4GB in the latest version of Excel
• Big files and processes that take 5, 10, 15, mins or more can be sped up considerably –  these processes happen every day and are a source of massive frustration and unproductive hours
• Processes that were previously impossible can now be done on software you’ve already paid for = money saved
• Less crashes = happier, more productive staff

What’s the downside?

IT may make a valid point that there might be incompatibility with existing drivers and add-ins throughout the business.  But these should be investigated and resolved, not used as a reason for your business to remain inefficient.

4. Adopt Office 365 rather than signing up for a “perpetual licence”

• Every 3 years Microsoft have released a new “perpetual licence” version of Office  e.g. 2007, 2010, 2013, 2016 and they have just announced 2019.
• However, the pace of change in technology these days is incredible and 3 years is a VERY long time to be without the latest improvements and features.
• The solution is Office 365, where you get semi-annual or monthly updates with significant new features.

What’s the business benefit?

• One misconception is that O365 if just online.  Far from it, it’s your normal desktop software PLUS online.
• O365 is also more than just your traditional Excel, Word and PowerPoint.   O365 is now an entire business ecosystem.  We often find companies paying for 3rd party software that’s already included in their O365 subscription!
• O365 software such as SharePoint, Flow, PowerApps, Teams, Planner and Skype for Business can save you on many 3rd party licencing costs.
• While some updates to Excel etc are cosmetic and nice to have, many are extremely significant like the ever-evolving Power Query and Power Pivot, Rich Data Types, Data Insights, Custom Visuals and a raft of upcoming improvements.
• New members of staff that have been using O365 in a previous role will be frustrated to discover that features they have been using for the last 2 years are no longer accessible to them.
• You may not attract and retain the best talent if you are running outdated software.
• You may find your organisation receiving files from 3rd parties with features that do not work.

I have worked with many organisations over the years and these 4 things are the perpetual cause of inefficiency in the office workplace.

The solutions are not complex and the efficiency gains are significant.

Yours sincerely,

Wyn Hopkins

Microsoft Excel Most Valuable Professional

Director at Access Analytic

The post Letter to the CFO – Key decisions for a more efficient business appeared first on Access Analytic.

Are you experiencing an issue when unpivotting Excel data?

Unfortunately as soon as you try to Unpivot a table of data containing a #DIV/0 or #/NA you get a very strange warning….

“The operation failed because the source databases does not exist, the source table does not exist, or because you do not have access to the data source.”

This is totally misleading, however the “More Details:” part of the message does tell you it’s related to a #DIV/0!.

Now, the best approach is to investigate the source file and fix the #DIV/0. However, for a number of reasons this is 1) highly time consuming, 2) the errors are known and the data isn’t required. You just need to convert the errors to 0.

So the simple approach would be to highlight all of the columns in Power Query and do a replace all errors. However, life is never that simple. If you don’t want your code to refer to any of the column names, if the column names are not consistent in the files you are consolidating – which is usually the case.

The solution can be found here…

https://stackoverflow.com/questions/39299331/replace-all-error-values-of-all-columns-after-importing-datas-while-keeping-the

and this is the 4 lines of code…

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    

 // Generate a list of all the column headings

    AllColHeadings = Table.ColumnNames(Source), 

    
    
 // Use List.Transform to create a list of the the column headings with a value of 0 against each one
 
   ColsAndReplacementVals = List.Transform( AllColHeadings, each {_, 0}),
    

    
 // Then use Table.ReplaceErrorValues using the original table (Source)  and  and the new list of columns and replacement values

    ReplaceErrors = Table.ReplaceErrorValues(Source, ColsAndReplacementVals)  



in
      ReplaceErrors

You can now happily unpivot your data without issue, and more importantly for, in this scenario, you don’t have to reference any of the columns in the code.

Want to learn more? Follow these useful links

Power Query articles | Power Query books

The post Remove all errors with Power Query appeared first on Access Analytic.

Microsoft UserVoice

We all want to make Excel and Power BI better. There are new features we’d love to see, and there are existing issues we’d like to fix. Microsoft are listening to users expressing their views via voting on the UserVoice sites.

Vote for the features you want

A number of experts (including our own Wyn Hopkins) have listed the features they’d like people to vote for in an interactive Power BI report.

Here’s how to navigate around the UserVoice dashboard

UserVoice Dashboard

The post Vote for the Excel and Power BI features you want appeared first on Access Analytic.

Every Excel Power Pivot Model or Power BI Desktop file needs a Calendar.

With the addition of a Calendar to your Data Model you can start to do all sorts of useful analysis such sorting the data by Fiscal month and Fiscal Year or performing calculations such as TotalYTD, Year On Year Growth, Actual v Full Year Budget etc… this list goes on.

It’s a critical element.

There are several ways to create this Calendar, but the most flexible way is to use Power Query.

This does involve a little coding – using “M” – which is the language of Power Query.

Or, you’ll be glad to hear, just some simple copy pasting!

Once you’ve taken 2 minutes to set it up you can re-use it again and again.

Download a pre-built calendar

If you don’t want to read all about the “how to” below, you can simply download a prebuilt Power Query Calendar (in an Excel file) here

Then you can open that Excel file, show the query pane, and then right-click on the Calendar query , choose Copy and then paste it into your project (Excel or Power BI)

You can then easily change the Fiscal Year End, Start Date and End Date in the first 3 steps of the query.

Alternatively, here’s the Power Query “M” code I use for all of my projects to create a calendar automatically. You can create a blank query and copy and paste it into your advanced query editor.

let
   

    EndFiscalYearMonth = 6,   //set this as the last month number of your fiscal year : June = 6, July =7 etc


    StartDate= #date(2016,7,1),     // Change start date  #date(yyyy,m,d)   
    EndDate = #date(2018,6,30),  // Could change to DateTime.LocalNow() if you want to always show up to the current date


/* Comment out the above StartDate and EndDate using // if you want to use a dynamic start and end date based on other query/table
   You will need to change "Sales" and "Invoice Date" in 2 lines below and then remove the // 
*/


    //TableName = Sales,    
    //DateColumnName = "Invoice Date",
    //StartDate = Record.Field (   Table.Min(TableName,DateColumnName)  ,DateColumnName), 
    //EndDate = Record.Field(Table.Max(TableName,DateColumnName),DateColumnName),


    
    DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Calendar Year", each Date.Year([Date]), type number),
    #"Inserted Month Number" = Table.AddColumn(#"Inserted Year", "Month Number", each Date.Month([Date]), type number),
    #"Long Month Name" = Table.AddColumn(#"Inserted Month Number", "Month Long", each Date.MonthName([Date]), type text),
    #"Short Month Name" = Table.AddColumn(#"Long Month Name", "Month", each Text.Start([Month Long], 3), type text),
    #"Fiscal Month Number" = Table.AddColumn(#"Short Month Name", "Fiscal Month Number", each if [Month Number] > EndFiscalYearMonth  then [Month Number]-EndFiscalYearMonth  else [Month Number]+EndFiscalYearMonth),
    #"Changed Type1" = Table.TransformColumnTypes(#"Fiscal Month Number",{{"Fiscal Month Number", Int64.Type}}),
    #"Fiscal Year" = Table.AddColumn(#"Changed Type1", "Fiscal Year", each if [Fiscal Month Number] <=EndFiscalYearMonth  then [Calendar Year]+1 else [Calendar Year]),
    #"Changed Years to Text" = Table.TransformColumnTypes(#"Fiscal Year",{{"Fiscal Year", type text}, {"Calendar Year", type text}}),
    FYName = Table.AddColumn(#"Changed Years to Text", "FYName", each "FY"&Text.End([Fiscal Year],2))
in
    FYName

If you’re not familiar with M code then the // is a way of adding comments or “turning off” bits of code

I’ve set it up so that it can be used in a number of ways, but as it stands we have a StartDate of 1st July2016 and an end date of 30 June 2018.

However, you can amend the code to a dynamic End Date using DateTime.LocalNow()

Best to start with the 1st of your current or any prior fiscal year and end with the last day of your current or future fiscal year.

Dynamic Start and End Dates

Maybe you want the Start Date and End date to be dynamically derived from the earliest and latest date in a column in a specific table / tables.

In that case, you add a double slash // before the StartDate and EndDate in rows 2 and 3 then remove the // from the 4 lines below. Then replace the word Sales and Invoice Date with the relevant query (table) name and column name.

//TableName = Sales,    
//DateColumnName = "Invoice Date",
//StartDate = Record.Field (   Table.Min(TableName,"DateColumnName")  ,"DateColumnName"), 
//EndDate = Record.Field(Table.Max(TableName,"DateColumnName"),"DateColumnName"),

Want to learn more? Follow these useful links

Power Query articles | Power Query books

The post Download your own Power Query Calendar appeared first on Access Analytic.

Power Query is the greatest addition to Excel in 10 years, it’s amazing at extracting and transforming data ready for you to use in a report. In this two minute video we show you how easy it is to extract the data you need without using any formulas!

Find out more

Extract and clean your data using Microsoft Power Query. 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).

Power Query articles | Power Query books

The post Free yourself from using formulas appeared first on Access Analytic.

This 3 minute video shows how easy it is to link to web page data with Excel.

In this scenario it’s connecting to the Reserve Bank of Australia exchange rates web page.

Get & Transform is built into Excel 2016.

It was previously known as Power Query and is still a free download for Excel 2013 / 2010.

This identical functionality is available in PowerBI Desktop.

Find out more

Power BI is free to a large extent, and you can do a huge amount without any cost.

Download Power BI Desktop here

The post How to pull exchange rates from a web page into Excel or Power BI appeared first on Access Analytic.

Excel and Power BI are amazing analysis and data visualisation tools. Knowing what’s possible is just the beginning.

You can see the possibilities in this video recently recorded at the Microsoft Studios in Seattle.

The speed of change and capabilities of Excel and Power BI are dizzying. The benefits of learning these technologies will save your business money and make your job easier.

Want to find out more?

Power BI is free to a large extent, and you can do a huge amount without any cost.

Download Power BI Desktop here

If you’d like to know more about Power BI look at this demo Power BI.com overview demo (video)

The post Excel and Power BI – From the Microsoft Studios appeared first on Access Analytic.

Excel or Power BI – where to start?

So… Excel has Power Query and Power Pivot built in.

Power BI uses the same technology.  So which one should you learn first?

I say which should you learn “first” because if you learn one it’s a relatively small step to learn the other

2 Questions:

1. What’s your aim?

POWER BI

• Creating dashboards and interactive visualisation that can be shared with multiple users via a web browser, or mobile app?
• Control what data is displayed via the user’s login credentials?
• A single version of information
• Higher level trend analysis, spot outliers, get email alerts

…too many other things to list here

EXCEL

• Creating dashboards and charts controlled via Slicers and Pivot Tables
• More granular analysis
• Combine with all the other functionality of Excel
• Handle more complex data presentation

…too many other things to list here

2. What’s your experience?

EXCEL

If you’re an existing strong Excel user familiar with Pivot Tables then it’s more natural to learn Power Query and Power Pivot in Excel

If your aim is to learn both Power BI and Power Pivot/Power Query in Excel then I’d recommend learning in Excel first, and when needed you’ll be able to transition to Power BI.

POWER BI

If you’re not an experienced Excel user or just don’t spend much time there and your aim aligns with the needs of Power BI then learn Power BI and when needed you’ll be able to transfer your skills to Excel.

Wrap up

The speed of change and capabilities of Excel and Power BI are dizzying. The benefits of learning these technologies will save your business money and make your job easier. Pick one and start learning it as soon as you can.

Want to find out more?

Power BI is free to a large extent, and you can do a huge amount without any cost.

Download Power BI Desktop here

If you’d like to know more about Power BI look at this demo Power BI.com overview demo (video)

or check out our blog https://accessanalytic.com.au/blog

The post Excel or Power BI – where to start? appeared first on Access Analytic.

Want to control which data fields to keep in Power Query when removing other columns?

When using the ‘Remove Other Columns’ transformation in Power Query (‘Get & Transform’ in Excel 2016+) the query editor hard-codes the remaining column names in the Advanced Editor. This is fine if your database structure is supposed to remain static. However, if you wish to add additional field at some later point, you would have to do it manually in the ‘Advanced Editor’, which may be inconvenient. In this situation, it would help having an external list of field names, which you can edit and let the query keep only those appearing on the list.

Below are 3 easy steps on how you can manage your data fields in an external list:

To start with, let’s look at the following table (named tblSource) into Power Query, remove all the fields apart from “Full Name” and “Address”.

This would generate the following line in the Advanced Editor:

 #”Removed Other Columns” = Table.SelectColumns(Source,{“Full Name”, “Address”})

The field names {“Full Name”, “Address”} in the ‘curly brackets’ is a ‘list’ for the purposes of Power Query. We will have to replace it with a variable.

Now, let’s follow the steps to make the fields flexible:

Step 1. Create a table in an Excel sheet as shown below, and name it as ‘tblFieldsToKeep’.

Our table contains an extra field for “Phone Number”, which we wish to add to the final query result. 

Step 2. Copy the following line into the clipboard and paste into your source table query before the ‘Removed Other Columns’ line.

FieldsToKeep = Excel.CurrentWorkbook(){[Name="tblFieldsToKeep"]}[Content][Fields to Keep]

Here we have read the table contents into Power Query and converted the column “Fields to Keep” into type ‘list’. The list is stored in the variable ‘FieldsToKeep’.

Step 3. Replace the field names in the ‘Removed Other Columns’ line with the variable, so the code looks as follows:

#"Removed Other Columns" = Table.SelectColumns(Source,FieldsToKeep)

At this point, our job is done. The new ‘Phone Number’ column will appear in the resulting table. If you add an extra field name to the list, it will appear after the query is refreshed (off-course, as long as it exists in the source data).

Here is how the resulting query will look:

let
   Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
   FieldsToKeep = Excel.CurrentWorkbook(){[Name="tblFieldsToKeep"]}[Content][Fields to Keep],
   #"Removed Other Columns" = Table.SelectColumns(Source,FieldsToKeep)
in
   #"Removed Other Columns"

You may ask, though, what if you had to assign a specific type to each of the fields? The resulting line in the advanced editor would also contain hard coded values looking as follows:

#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Full Name", type text}, {"Address", type text}, {"Phone Number", type text}})

In our next blog we will be talking about a way to automate this feature. Stay tuned!

In the meantime if you want to find out more about Power Query Discounted Software? Click here