Welcome to our comprehensive guide on manually editing the Power Query M formula language in Power BI. If you're a Power BI user who connects to data from Dynamics 365 Business Central, you understand the importance of ensuring your reports are pointing to the correct environment and company data. It's a common scenario: your organization might migrate to a new Business Central environment or restructure, prompting a change in the company dataset your Power BI reports should reflect. But what do you do when these changes occur? How do you rewire your reports without starting from scratch?
In this blog, we'll walk you through the precise steps needed to redirect your existing Power BI reports to a new Business Central environment and company. We'll take a deep dive into the world of Power Query M, the powerful language behind the data transformation process in Power BI, to make these adjustments with confidence and ease.
Whether you are a seasoned data professional or new to the Power BI platform, our straightforward instructions will help you update your data connections quickly and accurately. We'll guide you on how to access the Power Query Advanced Editor, make the necessary edits to the M code, and ensure that your data refreshes from the right source, every single time.
Let's begin by understanding the process of accessing and modifying the Power Query M formula language to seamlessly transition your reports to align with your current business structure. With this knowledge in hand, your analytics will stay as dynamic and adaptable as your business demands.
Changing the Business Central Environment and Company
1. Click on Home in the upper menu.
2. In the Data pane on the right side of the screen right-mouse on the table you want to modify. In my case it is the customers table. Then click on Edit query in the pop-up menu.
3. In the upper menu click on Home and then Advanced Editor.
4. Copy and save the Power Query M formula language in the pop-up editor into a Notepad as a backup.
5. Modify the environment and company to your new environment in the editor. Below is the original M formula language created by Power Bi:
let
Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
SANDBOX = Source{[Name="SANDBOX"]}[Data],
#"CRONUS USA, Inc." = SANDBOX{[Name="CRONUS USA, Inc."]}[Data],
v2.0 = #"CRONUS USA, Inc."{[Name="v2.0"]}[Data],
customers_table = v2.0{[Name="customers",Signature="table"]}[Data]
in
customers_tableBelow, you'll find the necessary modifications to update the environment and company within your Power Query M code, as well as some adjustments to variable names for better clarity. We've transitioned the environment setting from 'SANDBOX' to 'PRODUCTION' and altered the company reference from 'CRONUS' to 'XYZ'.
let
Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
ENVIRONMENT = Source{[Name="PRODUCTION"]}[Data],
COMPANY = ENVIRONMENT{[Name="XYZ, Inc."]}[Data],
API = COMPANY{[Name="v2.0"]}[Data],
customers_table = API{[Name="customers",Signature="table"]}[Data]
in
customers_table6. Click the Done button at the bottom of the editor and then Close & Apply under the File dropdown in the upper menu.
Conclusion
As we conclude this journey through the intricate pathways of Power Query M in Power BI, it's important to take a moment to reflect on the skills and knowledge you've acquired. You now possess the ability to navigate the unexpected turns that come with changes to your Dynamics 365 Business Central source environment. No longer should a migration or restructuring within your organization cause a ripple of panic about the integrity of your data connections in Power BI reports.
Through the steps detailed in this blog, you've learned how to pinpoint and edit the specific parameters within the Power Query M formula language that dictate where your data is pulled from. You've seen how to carefully orchestrate the transition from a 'SANDBOX' to a 'PRODUCTION' environment, and how to redefine the company data from 'CRONUS' to 'XYZ'. This level of manual editing ensures that your reports will not just resume their function but continue to provide insights that are accurate and aligned with your current business context.
Power BI's flexibility, combined with your newly honed skills in manipulating the Power Query M code, empowers you to maintain seamless business intelligence operations, even amid changes. Remember, always keep a backup of your original code before making changes, and verify your data sources after applying new settings.
We trust this guide has empowered you with confidence to maintain your Power BI reports' accuracy and relevance, providing your stakeholders with reliable data no matter the internal changes your company may undergo. As your business evolves, so too should your analytical tools. With the know-how to adapt quickly to new environments and datasets in Power BI, you're now equipped to keep your organization's data strategy as agile and resilient as it needs to be.