Financial Consolidation in D365 Finance

Financial consolidation is a feature you would come to expect as standard within Tier 1 ERP offerings and in that regard D365 F&O does not disappoint. However, for many in the industry, Financial Consolidation is one of those areas that you come to know because you’ve had to rather than because it was at the top of your reading list. This post aims to give an overview of the financial consolidation capabilities and some of the key components when looking to configure it for real world applications.

The first thing to note when looking to use the financial consolidation capabilities in D365 F&O is that you must have a legal entity marked as ‘use for financial consolidation process’ otherwise you won’t have access to the following content.

Typically the sole use of a consolidation company is denoted by its name, and as such, although the other operational sub-ledgers are listed in the menu path, it is only the ‘Consolidations’ module that you should be concerned with.

A screenshot of the consolidations menu path

Within D365 Finance you have the capabilities to consolidation within the application (consolidate online), import files from 3rd party finance systems for consolidation (consolidate with import) and export company data for consolidation within a 3rd party application (Export company balances). This blog post will be focusing on the ‘Consolidate Online’ process.

Criteria

A screenshot of the standard consolidation criteria form

When looking to execute or configure the consolidation process, the first screen you encounter will be the consolidation criteria tab. An overview of the key parameters is as follows:

Description – This is the description tagged to the consolidation transactions within the consolidation entity. Although I wouldn’t suggest it become a reporting field due to its free text nature it is worth adding in an relevant description such e.g. January Consolidation.

Main Accounts – This is where you specify your range of main accounts to consolidate. Typically this would be the first and last main account configured in your Group’s chart of accounts.

Use Consolidation Account – This parameter can be used for multiple purposes, e.g. elimination of intercompany trading balances, mapping local to group chart of accounts etc. If this is set to ‘yes’ it will consolidation the balances of a legal entities main accounts to the main account specified within the ‘consolidation account’ on the chart of accounts configured for that ledger. Note – if you choose ‘Yes’ and do not add a consolidation main account the process will skip this step and use the source main account.

Consolidation Period – Often an area which can trip people up as instinct would imply that you are only consolidating your past accounting period. However, this should cover the year to date upon which you plan to consolidate. This is critical when it comes to the currency revaluation aspects, which I will touch on later.

Include actual amounts – Although this may seem like a no-brainer on first inspection it is worth noting that the leger consolidation process can be used to consolidate budget entries as well as actual transactions. This setting, along with the ‘Include budget amounts’ allows you this flexibility.

Rebuild balances during consolidation – This process enables the complete deletion and rebuilding of dimension set balances. For performance gains it is recommended that this actually be run as a separate batch job and not as part of the consolidation process. However, in the above scenario the separate batch job is not configured.

Budget Models – These settings are only relevant if you choosing to consolidation your budget amounts, see above, and allow you to consolidate multiple budget models at once.

From an ongoing/BAU perspective it is only the criteria page that end users will be interacting with and updating, as consolidation settings are saved at an application level for future use. However, for the initial setup additional activities are required.

Financial dimensions

A screenshot of the consolidation financial dimensions setup

Once set, it is unlikely that this configuration is often changed due to the likely onward reporting dependencies. This screen presents users with all of the active financial dimensions. The segment order column is used to not only specify whether this dimension value should be consolidated, but also in what order on the transaction they should be displayed. This allows the flexibility to both simplify the consolidated transactions i.e. by removing the operational financial dimensions not required for group reporting; and also the ability to standardise your reporting balances where you have variability in your account structures across the group.

Legal entities

A screen shot of the source legal entities used for consolidation

This is where you specify the legal entity balances which should be included as part of the consolidation process. If this is a single consolidation tier then all group legal entities will be included here. However, where a multi-layer consolidation process is required this could be a sub-consolidation of a sub-set of legal entities. You can also specify the percentage share of the legal entity balances you wish to consolidate and also where currency conversation differences should be posted (P&L or balance sheet). This latter setting references the accounts for automatic transactions within the ledger setup and will depend on the customer’s reporting requirements, but it is often used to create the revaluation reserve within the balance sheet.

Elimination

A screenshot of the elimination rule definitions

It is highly likely that companies who operate a consolidation process will need to eliminate intercompany balances. These could originate from intercompany trading, cash transfers, recharges etc. This section allows users to specify pre-configured elimination rules that will be executed as part of the consolidation process.

Currency translation

A screenshot of the currency translation rules used on consolidation

Note – This section is only relevant if you are consolidating legal entities with inconsistent ledger accounting currencies.

This section supports compliance with International Accounting Standard (IAS) 21 and allows you to correctly account for the consolidation on foreign currency legal entity balances. Typically this would involve consolidation of the balance sheet at closing rate and the P&L at transaction rate. However, there are obvious exceptions, such as equity transactions (original share captial) which will be at a fixed rate. The above matrices allows you to specify the main account , exchange rate type and exchange rate date to support accurate consolidation of foreign currency balances.

Run in background

A screenshot of the batch processing parameters for the consolidate (online) process

As with most recurrent processes within D365 F&O there is the option to execute it as part of a batch process. This not only allows users the flexibility to continue operating with the application while the process is executing, but also allows for the scheduling of common activities.

Hopefully the above has provided some more context to the generic settings and tooltips widely available. I acknowledge that some of the areas above are deeper than this post implies e.g. elimination on consolidation, and will cover that off in future content, but I hope you found this useful and would welcome any feedback and comments.

Embracing the “Adopt not Adapt” approach in ERP implementation

In the world of ERP, there’s a term and strategy that’s gaining traction: the so-called “adopt not adapt” approach. It encourages businesses to use best practice software solutions as they are, rather than customising them to fit their internal processes.

An abstract image representing adopting on the left compared with adapting with the right

What is the “Adopt Not Adapt” Approach?

“Adopt not adapt” is mainly associated with cloud-first models, which are typically characterised by being provider-hosted, accessible across multiple types of device, sold on subscription-based models, and notably, include evergreen updates.

Key aspects of “adopt not adapt” include:

  • Avoiding customisation: Businesses are advised to, or aspire to, use the software as it is, without making modifications to suit their specific needs. This can fast-track implementation and avoid the costs and complexities associated with upgrading customized solutions.
  • Embracing best practice scenarios: Companies are encouraged to re-/define their processes based on best practice scenarios provided by the software.
  • Regular product enhancements: The approach promotes the acceptance of regular product enhancements, which in the case of D365 F&O are available four times a year.

The Benefits and Challenges

The benefits of adopting a system and minimising customisation are faster implementation, lower costs, and easier upgrades.

The main challenge is that the approach represents a significant cultural shift for many businesses. Existing processes may (i) be considered “unique” by the business, which is rarely the case, (ii) be deeply entrenched, and/or (iii) be dependent on specific individuals and their knowledge. These factors make it harder to prepare for and implement the change necessary to successfully adopt.

Summary

The “adopt not adapt” approach is a bold strategy for businesses looking to streamline their ERP implementation process. By embracing best practices and avoiding unnecessary customisations, businesses can potentially save time and resources.

However, it’s crucial to carefully consider specific needs, circumstances, and culture of a business, recognising that any software chosen will necessitate some level of organisational change.

In reality, there’s happy medium to be found somewhere between all out “adopting” and all out “adapting”. Keep “adopt not adapt” as a mantra, to maintain focus on a key goal of ERP implementation; improving business processes and efficiency.

Life is like a keyboard; know the shortcuts to play it well

It’s easy to get drawn into the depth and breadth functionality of D365 F&O, or even just to know exactly what you need to know, in order to get a particular task done. In doing so, users might miss out on some very useful basic features, like keyboard shortcuts.

Keyboard shortcuts can speed up repetitive tasks (who doesn’t use Ctrl+C and Ctrl+V every day?!), but also makes the system more accessible to the widest possible audience of users. Most F&O consultants probably know that “T” will quickly enter today’s date in a date picker; how many know that pressing “N” will pick the maximum date available?

The platform gives us the ability to see these shortcuts in every context menu, on every page, but I wonder how many users have ever clicked on “View shortcuts”:

A screenshot of the default dashboard in D365 F&O showing the right-click context menu, with the option to “View shortcuts”

This is the dialogue box that comes up, with a selection of commonly used shortcuts:

However, the real gem is the link the bottom left of that dialogue, to “View all product shortcuts”. That will take you to the following page on Microsoft Learn, with a full listing of shortcuts available: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/shortcut-keys

So, did you learn something new today? Either way, let us know your favourite shortcut in the comments!

On your marks, get set, Data!

Excitement, expectation, motivation and passion are all feelings associated with kicking off a new ERP implementation project. It is also understandable why these feelings often dissipate when the subject of ‘data’ comes up. However, a failure to prioritise, understand or even acknowledge data could create serious issues later down the line and, in the most extreme cases, result in a failed implementation.

Data activities can, and should, be one of the first streams of work initiated by customers. Some of the key considerations and activities should include:

Identifying your data – The majority of business users are consumers of data, as such they often don’t acknowledge the underlying sources/process drivers. Knowing what data you have, what creates its, where else it is used and how is it structured is one of the critical first steps for any data stream within a project.

Mapping your data sources – Legacy finance systems are often in house/on premise solutions where the customers own and can readily access their data. However, it is highly likely that over time tactical solutions have been deployed resulting in data being stored in 3rd party databases that may require access requests to be approved and/or have limitations in place in how to access/query that data. Understanding this dependency, timelines for access/extraction, and visibility of that data will be key when planning the ETL (Extract, Transform and Load) activities for your data migration.

Cleansing your data – Changing an ERP or finance system is not a recurring business activity so it is highly likely that, after having your current solution for a lengthy period of time, your data structures, formats etc have changed to adapt to your business needs. Identifying legacy data items no longer required and preparing consistent, clean and complete data sets is an absolute must when you are seeking to migrate to a new system.

Allocating data owners – Once you have an understanding of your data flows it is strongly recommended that you specify data owners. Business users who regularly interact and consume the data are ideal candidates. These data owners will act as one of the primary contacts when discussing the ‘to-be’ business processes in the future solution. Specifying/validating transformation rules, approving data extracts/imports, defining test criteria and confirming successful data migration will often be responsibilities of the respective data owners.

Technology/solution agnostic project activities like those listed above can be easy wins when it comes to preparing your business for a transformation programme.

These items are by no means an extensive list of data activities undertaken during a project implementation. However, with the consideration that a critical part of any data stream is to ‘Get your data house in order’ they should all be items given strong focus and consideration as part of any ERP implementation project initiation.

Understanding Pre-Sales in the Context of ERP

ERP systems have revolutionised the way businesses operate, offering integrated management of core business processes like finance, supply chain management and HR. However, before implementation gets underway, there’s a critical phase known as pre-sales. Let’s delve into what pre-sales means!

What is Pre-Sales?

Pre-sales is a set of activities that precede the “sale”, i.e. the contracting for implementation of a new system or managed service offering. It’s an integral part of the sales cycle, because ERP solutions are complex and require a significant investment of time and resources.

Pre-sales involves understanding the customer’s needs, demonstrating how the solution can meet those needs, and preparing a proposal and estimates for the customer. Because every customer takes a different approach to their procurement, the steps undertaken in pre-sales are rarely identical, even within strictly-governed public sector frameworks and tenders.

Due to the breadth and depth of functionality of tier-1, enterprise-grade systems like D365 F&O, pre-sales is usually a full-time role for one or more consultants or architects at an implementation partner, who draw on their own platform knowledge and experience, leaning on members of the delivery practice for specific area of functional, technical, or industry expertise.

Animation showing the word LEAD morphing into the word DEAL

The Role of Pre-Sales in ERP

The pre-sales process is crucial for several reasons:

  1. Understanding Customer Needs: We’ve said it before and we’ll say it again, ERP systems are not one-size-fits-all solutions. They need to be tailored to the specific needs of each business, including configuration, customisations, and integration builds. The pre-sales process allows the partner to understand:
    • the customer’s requirements (of which there can be hundreds, if not thousands),
    • their existing business processes (essential to confirm scope, even where the approach is “adopt not adapt”1),
    • particular challenges such as lack of automation, inefficiency, resource intensity, and
    • the goals of the implementation, such as target ROI, or less tangible efficiencies.
  2. Product Demonstration: During pre-sales, the partner will demonstrate aspects of the software to the prospective customer, often using a relatable end-to-end scenario. This allows the customer to see firsthand how the ERP system looks and feels, how it will solve specific challenges, and how it can benefit their business. With D365 F&O, it involves calling out the many unique selling points of the platform, especially how seamlessly it works with the rest of the Microsoft ecosystem.
  3. Proposal Preparation: Based on the understanding of the customer’s needs and the product demonstration, pre-sales will prepare a proposal. A proposal can include:
    • the solution at a high-level, including scope (functional and technical), ISVs, integrations, customisation work, etc,
    • estimates on implementation timeline, resources, and cost, worked up with delivery leads,
    • high-level risks, assumptions, dependencies, and responsibilities,
    • training, change management, and/or managed services components, and
    • licensing estimates, to support the customer with calculating their total cost of ownership (TCO) over a defined period.
  4. Building Trust: The pre-sales process is also an opportunity for the partner to build trust with the potential customer. By demonstrating expertise and understanding the customer’s needs, the partner can establish a strong relationship that can lead to a successful contract, but more importantly, a successful implementation.

As you can see, there’s many facets to pre-sales. If you want to know more, I’d recommend checking out Practical Presales, a blog by Craig McGeough which is dedicated to the subject.

  1. The “adopt not adapt” approach for ERP implementation is a strategy that encourages businesses to use best practice software solutions as they are, rather than customizing them to fit their internal processes. We’ll plan a future post that covers this in more detail. ↩︎

Capturing purchase invoices with OCR

In the world of finance, efficiency and accuracy are paramount. One area where these qualities are particularly important is in the management and processing of invoices.  This is where the Invoice Capture solution in D365 comes in.

How does it work?

Invoice Capture is a feature in D365 F&O that automatically creates supplier invoices from digital invoice images, including JPG and PDF file formats. The solution leverages OCR technology and AI to digitise printed text, making content electronically editable, searchable, and storable.

The image is a screenshot from the D365 Invoice Capture solution, showing a sample invoice and data extracted from the same
Screenshot of the D365 Invoice Capture app, showing a sample invoice image and the data captured
(N.B. sensitive data is redacted)

The Invoice Capture app is easily configured to receive invoices from different channels, including email, OneDrive and SharePoint, with optional constraints on file names, types and sizes. Tolerances for AI confidence can be manually applied. The app is then connected to specific legal entities in your target environment, allowing supplier accounts (vendors) to be imported and selected for mapping.

Once processed, supplier invoices are passed directly into the Purchase ledger module of D365 F&O, where further automation can include three-way matching, posting, and scheduled payments.

Screenshot of the same invoice image and extracted data, as passed automatically to D365 F&O

What do I need to use it?

Once the “Setup system” configuration wizard is completed – and security roles are configured – the app works quite effectively out-of-the-box and only gets better with experience. It will need its virtual hand held initially, whilst it gets used to the nuances of different suppliers, but that also allows AP users to gain confidence in the output.

The following licenses must be considered for Dynamics 365 Finance customers:

  • Power Apps license (per user) – To access Invoice capture, users must access Power Apps.
  • Invoice processing fee based on number of invoices – Dynamics 365 Finance customers are entitled to 100 invoice capture transactions per tenant per month. If customers need more transactions, they must purchase extra Electronic Invoicing stock keeping units (SKUs) for 1,000 transactions, per tenant, per month.
  • Azure Data Lake Storage subscription – Becomes a consideration in the event that the standard 20 GB user storage becomes insufficient to store original invoice documents.

Anything else to know?

Invoice capture became general availability (GA) in April 2023, finally bringing a native invoice processing feature to D365 F&O. Previously, the only way to adopt such functionality was to license an independent software vendor (ISV) such as Axtension® Invoice Processing in conjunction with Kofax Readsoft.

Is a new implementation/ upgrade the answer?

Not all ERP and finance systems are created equal and over time may become outdated or misaligned with the changing needs of the business. In this case, an upgrade or replacement may be considered necessary to keep up with the market demands, leverage new opportunities and achieve the businesses growth strategy.

The initial appeal to upgrade or implement a new finance system is clear with the ability to leverage benefits such as:

  • Access to new features and functionalities that can boost productivity, business performance and competitiveness. especially with the emergence of AI.
  • Reduced ongoing costs in the form of lower maintenance, service and (often) licence fees.
  • Risk mitigation by removing dependency on complex inhouse developments, unsupported applications and inferior reporting & analytics.
  • Alignment of your ERP system with your businesses short and long term strategic objectives, with a more resilient platform better able to adapt to changing market, customer and industry demands.

There are many other benefits available with the quantifiable gain often unique to each company. However, the decision to implement a new system can be costly and may not necessarily be the right decision for everyone. Key factors to consider when performing this assessment include:

  • An assessment of your current ERP/finance system to identify its strengths and weaknesses. Ask yourself questions such as; How well does it support your current and future business needs? How satisfied are the users with its performance, usability, and reliability? Do you often face technical downtime/disruption?  It’s worth noting that one of the common drivers for a new system can be a lack of reporting and analytics. However, discrete reporting solutions such as Power BI, Solver BI, etc. may be a more appropriate solution.
  • Perform a market evaluation of the current solutions and upgrades with a comparison to your current system. Note – ERP systems are not a ‘one-size-fits-all’ solution and often have performance, costs, and features applicable to business and industry tiers. What are the features and benefits that they offer and how do these align to your current system weaknesses and business challenges? How compatible are they with your existing infrastructure and processes?
  • Complete a cost/benefit analysis of upgrading or replacing your ERP system. How much will it cost to implement, maintain, and train your staff on the new system? How much will you save or gain from the improved efficiency, productivity, and profitability? This activity is key to properly understand and quantify the potential benefits to derive an informed ROI.

The key considerations and specific benefits of whether to implement a new ERP system or upgrade a current solution will be unique to each customer, but the above can act as baseline when embarking on this journey.

Navigating the D365 F&O interface (Part 1)

We were excited to announce the blog on LinkedIn this past Friday. One commenter lightly jibed that boats might be easier to navigate than F&O, so I thought we should bring forward the subject of some navigation tips!

(There’s a reasonable chance that even experienced users might learn something here – look for bold sections.)

The logical place to start is the Home page – the Default dashboard as standard – viewed below with the system administrator role, which means all workspaces are:

A screenshot of the Default dashboard for a system administrator in D365 F&O, with navigation pane pinned open
  1. Microsoft app launcher (aka waffle menu), which at time of writing, may or may not still be functional.
  2. Shortcut to Home page, which can be changed to one of six other workspaces under User options > Preferences.
  3. Company selector, for jumping to other legal entities, that a user has been granted access to.
  4. Navigation search, which allows use of partial terms and initialisms, e.g. typing “t d g” will shortcut to Tracking dimension groups, and “orders” will give results such as Sales orders or Purchase orders. Results are limited to the pages a user’s security role gives them access to, so if you can’t find something, your assigned role(s) could be the issue!
  5. Action centre, which shows user notifications such as completed activities and errors.
  6. Feedback, which submits user feedback on the current page/form directly to Microsoft.
  7. Settings, which gives a submenu linking to (i) User options, (ii) Task recorder, and (iii) Mobile app workspace configuration. More on these in a future post.
  8. Help, which gives a submenu linking to (i) Trace, which allows users to record how an environment is responding to their actions, in order to share with a Microsoft support professional, and (ii) About, which provides valuable information such as the current application and platform versions of your environment.
  9. Logged in user, which when clicked, gives the option to Logout.
  10. Navigation pane button (aka burger menu), which options and closes the navigation pane. Using the push pin allows you to lock the navigation pane open.
  11. Shortcut to Home page (same as #2 in this list).
  12. Favourites – users can mark a favourite form or page by clicking the star next to the corresponding menu item under Recent, Workspaces or Modules. We’ve tested and over 200 items can be added, but it’s probably best to keep it more streamlined than that!
  13. Recent, which shows the last 10 forms or workspaces that you have navigated to.
  14. Workspaces, which lists all the same workspaces found on the Default dashboard, per the user’s security role.
  15. Modules, which shows all the modules, and beneath them, forms and pages, that a user has access to, per their security role.
  16. Work items assigned, such as native workflow approvals.
  17. Apps, including the Business performance analytics tool (still in preview at time of writing), plus any other Power Apps that administrators or users may choose to share here. Power Apps (or Workspaces or Websites [external links]) can be added by right-clicking nearby, selecting Personalise: Apps, and then +Add a page.
  18. Workspaces – all workspaces that a user’s security role permits them access to can be found here, as well as any custom workspaces. Workspaces (or Power Apps or Websites [external links]) can be added by right-clicking nearby, selecting Personalise: Workspaces, and then +Add a page.

What’s in a name?

You’ll notice that throughout this site, we will refer to either Dynamics 365 for Finance & Operations, Dynamics 365 Finance & Operations (without the “for”), or D365 F&O. We stick pretty rigidly to this nomenclature, in line with Microsoft’s own Finance and operations application documentation, but there’s some ambiguity in the market, so we thought it deserved a brief explanation.

The platform has had different names over the years:

  • 1998: IBM Axapta
  • 2000: Axapta
  • 2002: Microsoft Axapta
  • 2006: Microsoft Dynamics AX
  • 2016: Microsoft Dynamics 365 for Operations (D365O)
  • 2017: Microsoft Dynamics 365 for Finance & Operations (D365 F&O)
Screenshot of the top banner in D365 F&O
A screenshot of the top banner of D365 F&O (taken January 2024)

In 2020, the licensing for the product split, making it possible to purchase…

  • Microsoft Dynamics 365 Finance, and
  • Microsoft Dynamics 365 Supply Chain Management (D365 SCM)

…separately. This has led to some people, including occasional Microsoft employees, referring to the combined platform as D365 FSCM or D365 F&SCM.

The problem is, the term D365 FSCM is flawed, because there are actually four licensable products within the platform:

  • Dynamics 365 Finance1
  • Dynamics 365 Supply Chain Management
  • Dynamics 365 Commerce
  • Dynamics 365 Human Resources

With this logic, the acronym should be D365 FSCMCHR, or some other alpha-spaghetti combination of those initials, which is clearly ridiculous!

Furthermore, as there’s almost no use of the term “D365 FSCM” on any official Microsoft webpage2, we’re sticking with D365 F&O, to be fully inclusive.

There’s a very real chance that in the future, we’ll see a change to the architecture, application names, and further wholesales changes to the platform in general – likely all three. If you know, you know. But until then, it’s still D365 F&O!

  1. There’s now a Finance Premium option too, but that’s for a future post. ↩︎
  2. We’ve only found two exceptions, (i) this FastTrack page on GitHub, and (ii) this MS Docs page, which is actually about Oracle PeopleSoft! Any references on AppSource don’t count, as descriptions aren’t written by Microsoft. ↩︎
A screenshot of the D365 F&O loading screen (taken January 2024)

Trial balance using OData in Excel

We had a user who was keen to have a trial balance report in Microsoft Excel, rather than having to access D365 F&O directly. They also preferred not to use Power BI for this particular case.

Firstly, a quick reminder of what a trial balance is. A trial balance is essentially a financial report that summarises the debit and credit balances of all ledgers. Typically prepared by a company at the end of each reporting period, the main objective of a trial balance is to confirm that the total of all the debit entries in the company’s ledgers is equal to the total of all the credit entries.

Here’s a screenshot of a sample trial balance report in D365 F&O:

D365 F&O screenshot of a sample trial balance report
A sample screenshot of a trial balance report in D365 F&O

Now, due to the complexity of the system, there is no single table for trial balance available in D365 F&O, as you might find in a lower-tier, single ledger accounting system. It’s also not possible to use the Microsoft Dynamics Office Add-in – there’s no friendly “Open in Excel” option, available throughout much of the rest of the system.

It seemed like a good opportunity to revisit OData!

We first needed to connect to our D365 F&O environment to Excel. The screenshot below show the steps needed:

The URL is simply the address of the environment (copied from the browser bar), with “/data/” appended1.

It’s necessary to sign-in with your Microsoft account – the same account as you use to access D365 F&O. This makes sure you can only access data permitted by your assigned security role(s).

Once you’ve connected, Excel will load all available tables from the D365 F&O environment.

The primary information for creating a trial balance is found in GeneralLedgerActivities, but it’s also helpful to get MainAccounts and FiscalCalendars, in order to filter by legal entity and display by period.

Excel includes the same Power Query Editor as Power BI, so if you’re familiar with one, you won’t struggle in the other, when it comes to building your trial balance report. It does require a couple of joins, grouping, and sorting, but the output is quite pleasing to the eye – for an accountant!

For reference, here’s the query behind the scenes:

let
    Source = Table.NestedJoin(GeneralLedgerActivities, {"LedgerGregorianDateId"}, FiscalCalendars, {"LedgerGregorianDateId"}, "FiscalCalendars", JoinKind.Inner),
    #"Merged Queries" = Table.NestedJoin(Source, {"MainAccountRecId"}, MainAccounts, {"MainAccountRecId"}, "MainAccounts", JoinKind.Inner),
    #"Expanded FiscalCalendars" = Table.ExpandTableColumn(#"Merged Queries", "FiscalCalendars", {"FriendlyPeriod", "PeriodOffset"}, {"FiscalCalendars.FriendlyPeriod", "FiscalCalendars.PeriodOffset"}),
    #"Expanded MainAccounts" = Table.ExpandTableColumn(#"Expanded FiscalCalendars", "MainAccounts", {"MainAccountId", "Name"}, {"MainAccounts.MainAccountId", "MainAccounts.Name"}),
    #"Grouped Rows" = Table.Group(#"Expanded MainAccounts", {"MainAccounts.MainAccountId", "MainAccounts.Name", "FiscalCalendars.PeriodOffset", "FiscalCalendars.FriendlyPeriod"}, {{"Sum.ReportingCurrencyAmount", each List.Sum([ReportingCurrencyAmount]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows", {"FiscalCalendars.PeriodOffset"}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[FiscalCalendars.FriendlyPeriod]), "FiscalCalendars.FriendlyPeriod", "Sum.ReportingCurrencyAmount"),
    #"TrialBalance" = Table.RemoveColumns(#"Pivoted Column",{"FiscalCalendars.PeriodOffset"})
in
    #"TrialBalance"

Do let us know what you think in the comments. What would you add or change about the query? Would you approach the challenge another way?

  1. (Added 15/01/24): To speed up loading of your data, try including the table name and filter criteria in your source, e.g. https://************.sandbox.operations.dynamics.com/data/GeneralLedgerActivities?$filter=YearOffset eq 0 and Ledger eq ########## ↩︎