Following on from the ‘Harness the potential of Power BI to solve data challenges’ webinar, Rishi Sapra, Chartered Accountant turned Microsoft Most Valuable Professional (MVP), returns to address some of the key questions that came out of the webinar on using Power BI for financial reporting.
Do you need to know how to use Power Query to use Power BI? Is it the same but with easier dashboards?
Power Query is one component of Power BI – In the webinar I showed an overview of Power BI, and it was the "Connect & Transform" component of the overall solution (with the others being Model & Calculate, Visualise & Analyse, Share & Secure, Query & Explain and Govern & Scale). It is the component that allows you to connect to data sources (databases/financial systems, spreadsheets,APIs etc) and clean/shape the data as required to bring it into a suitable format for analytics.If the data is already clean and well-structured then there will potentially be very little work required in the Power Query component, but it will still be necessary to use it to connect to the data in the first place. However, by itself this won't give you a Power BI report - you will still need to define any logic in DAX and visualise the data using the charting and interactive features of Power BI.
We are struggling to find analysts with DAX experience - any ideas? What is the best source / way of learning DAX?
For those unfamiliar, DAX is the formula expression language used in Power BI (and Power Pivot in Excel). Anyone with Power BI experience should have a fundamental understanding of DAX anyway, but working with DAX and getting to grips with all of its technical nuances requires a slightly different way of thinking for those coming from an Excel background.
There are many resources available to help with learning DAX - the free DAX course from SQL BI is a great place to start with learning the fundamentals (and Microsoft have provided a series of example Power BI reports which include DAX measures) – but the challenge is being able to apply these concepts to individual problems. ChatGPT or CoPilot can obviously help, but a learning/problem-solving mindset is key. So rather than necessarily looking for someone with a certain amount of experience specifically in DAX, instead look for those who have a growth mindset (are curious/passionate to learn) and support them in that journey!
It is also important to note that writing complex DAX often isn’t the right answer! If you are able to build the right kind of data models (a "star schema") then the DAX should be relatively simple for the most part, and models with simple DAX are also easier to maintain, validate and audit!
SQL BI also have a free course on data modelling and there is a recent feature in Power BI called Visual calcs which is aimed at replacing some of the complexity required in DAX with calculations in the visual layer instead. There are also other ways to simplify your data models/logic, such as building the logic into your source system/application rather than Power BI where possible.
Given that there are many ways to achieve the required outcomes with Power BI, look for training which can help instil the right approaches/ways of thinking and get help in applying technical training to your data and processes.
How is Power BI in formula creation? For example, for creating formulae such as a Future Value Factor (e.g. FVF = (1+r)^t . Can you do that in Power BI, or do you have to go with MS "black box" formulae, which a Treasurer may prefer not to use.
DAX contains several financial and mathematical functions, that all work in a very similar fashion to their Excel equivalents. In a sense these functions become "black box" by having the logic encapsulated within them, but just like with Excel you can break it up into separate calculations (measures can refer to other measures so you can display an overall result as well as the component calculations).
Excel will always have more flexibility for modelling these kinds of calculations with different parameters, but it is also possible to do this to an extent in Power BI using "what if" parameters.
How did you access the Xero data? Can you link Xero to Power BI directly? Is that effective?
I used a tool called Fivetran, which has a free plan users can sign up to. In my case, I was writing directly to Fabric OneLake (and then bringing the data into Excel via the SQL endpoint) but there are many other data destinations available on this platform.
The Xero website also highlights a number of other tools to connect Xero to Power BI. The app OdataLink is one that has come recommended by a friend who works a lot with Xero data in Power BI. The Acterys tool is also very popular amongst finance professionals as it not only contains a Xero connector but brings the data into an Azure SQL database which can then be used as a central location for other finance/operational data (e.g. budgets and mapping tables) with a write back capability from Excel/Power BI that can then also be used for FP&A.
Xero do make the data (and accounting functions – e.g. creating transactions/invoices) available via their API. For some APIs, it is possible to connect directly from Power BI using the Web connector though the authentication method required for Xero (the Service principal) makes this a bit tricky. If you want to create your own connection to extract data, it is best to do this in Azure or Microsoft Fabric and load the data into a database/Lakehouse for Power BI to consume.
If you have a payroll report and you want to create a report showing each manager the overtime of their department, can you ensure the manager can only see their own data extract and nobody else's - how can you build it up monthly?
Yes, this is one of the main benefits of using Power BI over standard Excel as it supports ‘row level security’ - the ability to define access rules to subsections of a dataset. The Row Level security feature in Power BI lets you set up simple rules to ensure that each time someone views the report it will automatically be filtered down only to the data they are allowed to see, without the need to create different cuts/views of the report for different audiences.
To set this up with an organisational hierarchy, you would likely need to use Dynamic Row Level Security where a table of users and corresponding levels of permissions is used as an input in Power BI.
For more granular security controls it may be worth considering use of external tools (e.g. Tabular Editor for Object level security) or using Azure/Microsoft Fabric as a data source where you can set up dynamic data masking and permissions on a data source level which flow through into Power BI or other tools which access the data.
What recommendations do you have to make Power BI useful for P&Ls? We have connected Power BI directly to Business Central but downloading from Power BI, putting into Excel, and creating a more useful version in Excel seems backwards!
Yes, exporting from Power BI to Excel to then produce reports there is going backwards in the analytics process flow and likely defeats the point of using Power BI in the first place! If the reason you are doing this is because you need to have specific layouts for the P&L, then you may be able to design these layouts in a separate table to be used as a data source in Power BI as explained in the next question.
If there are specific reasons why you need to present the P&L or other outputs in Excel, then it is also possible to use the Power BI data model as a live connection link into Excel which is a better option than downloading static extracts of the data from Power BI.
The biggest challenge I have with using Power BI for financial reporting is getting the financial statement view represented due to the complexities of the various sub-totals and sort orders etc. I've been (on and off) looking for a .pbix with a template to be able to learn from / copy but have so far been unsuccessful. Is this something you can help with?
Yes! The first module of my e-learning course, which is available for free on www.powerplatformfinance.com, includes a PBIX template that has the Income statement in there with all the sub-totals/sort orders and dynamically formatted values (numbers and percentages) in the same measures.
This uses the native matrix visual in Power BI to produce a formatted financial statement with the required layout by using a separate layout table (a manual "helper" table if you like) that can connect into your Chart of Accounts and can drive calculations for the subtotals and other lines.
I've done Excel files with many months of TBs and budget to do trends and forecasts. Every month, the mapping table needs expanding for new codes. Some of the process for identifying the need for new codes is clearly repeatable, but some of the new mappings require personal judgment. Does Power BI allow that integration with personal judgment in updating the transformation?
Power BI wouldn't generally be the place where you would update those mappings, but rather the latest mapping table would be one of the data sources in Power BI.
When working with mapping tables in Power BI, you can utilise them just inside Power Query (to create the Chart of Accounts dimension table for example); the mapping table itself doesn't need to be brought into the data model. To do this you would have the query that connects to the mapping table as a query in Power Query but set that query to disable load (right click and deselect "Enable load").
In terms of maintaining the mapping table and applying that personal judgement, this could be done in Excel as presumably it is done today. If you wanted to see the unmapped rows at any given point, you could utilise Power Query in Excel perhaps leveraging the anti-join merge which shows rows that exist in one table but not another. Alternatively, if you wanted to have a clear audit trail of updates and have a workflow around the update process, you could use Power Apps/Power Automate connected to a database or SharePoint list for example that could also be a data source in Power BI.
In Power Query, you appeared to have structured your queries in folders, could you share the best practice on the folder structure for different stages in financial modelling?
The folders I was using in Power Query is just a way to organise queries so that their purpose is easier to understand. This is one of the best practices for Power Query highlighted by Microsoft in this article (the other recommendations are worth reading too!).
Rather than having all the logic contained in a single query, I split it out across multiple queries and referenced queries together in a chain so the output of one is the input for another. This makes it easier to then split the queries by the type of operation/logic they are performing - Connecting to a source/Extracting data, transforming it, and the final queries which are the only ones then loaded to the data model. This was the set of folders I used (Extract, Transform, Load) though of course you should adopt a structure that makes sense for your process and - if you’re working as part of a team – one that everyone can follow.
You showed the MTD/QTD/YTD views, can you get YTD for non-December year ends?
Yes! The last parameter in the TOTALYTD function in DAX is the year-end date which is what you would use for non-December year-ends!
Regarding the reporting solution, wouldn't you already have all this from your accounting software? It would be more interesting to see one with data from different systems say your HR system? Do you have any resources you can share on whether to link directly into the system VS using reports downloaded from a system?
Whilst in an ideal world all of this reporting would be available from financial systems, in reality this is rarely the case for a variety of reasons (multi-entity groups being on different systems, systems not handling financial consolidation well/at all, reporting modules not being very flexible etc) which means that finance teams the world over consistently fall back on manual Excel solutions that can become a source of pain to update each period, especially as data volumes and complexity grows.
Power BI is part of a potential solution for this where you can combine data from a financial system with mapping/reference data that may be coming from an external source (such as Chart of Account mappings or exchange rates) in an automated fashion.
Provided there is a way to link them, it is of course also possible to combine financial and operational data into a single data model/report and analyse data across them to get an overview of the entire business. This is a great use case for Power BI!
Also, even if you are only connecting to a single data source, there are still often differences between the standard reports available in a financial system and what you can build in Power BI. One of the main differences is the reports from the system tend to be static/fixed, whereas with Power BI reports you can build your own visuals and story around the data that can include interactions such as drill-through, bookmarks and cross-filtering to allow users to explore data points and hone in on areas of interest.
Do you have any thoughts or guidance on how governance should work with Power BI. How should the governance tools in fabric be used? Do you see this as joint ownership between finance and IT?
Along with financial reporting, governance for Power BI and Fabric has been my main area of expertise and interest over the years! I presented a session at SQL Bits 2022 and also presented one on Fabric Governance this year.
To summarise my approach for Power BI governance, it is to not treat all BI the same - there is team/personal BI where the content and processes are managed by individual workspace owners, and there is Enterprise BI which is where data/BI artefacts are designed upfront and managed in a more rigorous and controlled manner (e.g. access only granted through Entra ID groups, separately managed semantic models, Git Integration/deployment pipelines etc).
Of course, technology is only a small part of the overall approach to Governance - it's also about the people, processes and controls that help to make it work. There are different operating models with different levels of centralisation, and especially with Microsoft Fabric where there are many more components to manage! The decentralised "Data Mesh" approach is one that is becoming increasingly popular, particularly in large organisations.
Other handy resources include the webinar I did on MS Fabric for the community in February 2024, and the new Data Governance capabilities in Microsoft Purview.
