Thinking Outside the Box: Leveraging OData Entities and Power Query in Dynamics 365 for Finance

In today’s fast-paced business environment, the ability to think outside the box and find innovative solutions is crucial. One such example is how we recently addressed a customer’s requirement in Dynamics 365 for Finance without resorting to customizations. By harnessing the power of OData entities and Power queries in Excel, we were able to deliver a real-time, efficient solution that exceeded our customer’s expectations.

The Challenge

In essence our customer needed a way to summarize weights based on commodity codes for consolidated packing slips, a task that traditionally might have required custom development. However, we aimed to achieve this using the standard tools available within Dynamics 365 for Finance, avoiding the time and cost associated with customizations.

The Solution

We turned to OData entities and Excel, leveraging their capabilities to create a seamless and efficient process. Here’s how we did it:

  1. Utilizing OData Entities: OData (Open Data Protocol) entities in Dynamics 365 for Finance allow for easy access to data. We identified the relevant OData entities that contained the packing slip and commodity code information we needed.

    –  To see all publicly available entities add the keyword /data in the URL

    In that list we have identified the entities:
    CustPackingSlipTransBiEntities and ProductCategories

    – To check if the entity provides all fields needed, add the entity name in the URL

  2. OData Feed in Excel: OData Feed in Excel allows us to connect to these entities and manipulate data further within Excel.

    – To expose the entity data in Excel, copy the URL with the entity name and maybe add some filters to reduce the data load, like:
    https://xxxxxx.operations.dynamics.com/data/CustPackingSlipTransBiEntities?$filter=dataAreaId eq ‘xyz’  (indicating to fetch only data from ‘xyz’ legal entity).
    – Paste that URL in:

  3. Power Query in Excel: Power Query is a powerful tool within Excel that enables data transformation and combination. We used Power Query to merge two queries from the OData entities, effectively summarizing the weights based on commodity codes on the consolidated packing slips.
  4. Real-Time Data Extraction: By connecting Excel to the OData entities, we ensured that the data extraction was real-time. This means that any updates in Dynamics 365 for Finance were immediately reflected in our consolidated packing slips.

The Outcome

The result was a streamlined process that met the customer’s requirements without any customizations. The real-time data extraction ensured accuracy and timeliness, and the customer was extremely satisfied with the solution.

Conclusion

This experience highlights the importance of thinking creatively and utilizing the tools at our disposal. By leveraging OData entities and Excel, we can achieve powerful results without the need for custom development. This not only saves time and resources but also provides flexible and scalable solutions for our customers.