In this video, I’ll demonstrate how to use some of the advanced functionality in the power query editor to query things from Microsoft Dynamics 365 Business Central/Tensoft SemiOps/ DemandOps or any OData source.
Firstly, I’ll cover how to combine queries together in two different ways – Merge and Append.
Merge is joining two related datasets together. I’ll show you how to join journal entries with the chart of accounts. These are two different data sources but are related by the Account Number. In DemandOps, it might be like joining sales order with the customer/business table so that you can get more data from the business table into the same query as orders/addresses/contacts.
Appending is the same exact dataset, but different sources combined to look like one query. This is very helpful if you have got multiple companies. It is a Business Central type query, and you wouldn’t really need to do this in SemiOps/DemandOps unless you are trying to append some history into the same dataset. For example- You go live in 2022 and start invoicing but you want to put in your history of invoices in Power BI/Excel that aren’t in DemandOps. In this case, you can have an offline dataset of invoicing activity or an addendum with the current. When you are viewing it, you can see the appended history of invoices back as far as you want.
In this demo, I’ll also show you how to create a dynamic variable to easily change certain query criteria like the data source itself or filters within the data source.
3:00 – How to Append Query
10:54 – How to Merge Query
To learn more about reporting in Microsoft Dynamics 365 Business Central, visit: https://dynamics.tensoft.com/blog/ or contact us.
– Jason has over 20 years of financial leadership experience in high growth technology companies. He is a Microsoft Certified Dynamics 365 Business Central Functional Consultant Associate. See Credential.
