Dynamic Query Parameters in Power BI for Business Central

In today's fast-paced business environment, agility and efficiency in data management and reporting are not just luxuries—they are necessities. For those utilizing Microsoft Dynamics 365 Business Central alongside Power BI, the challenge often lies in seamlessly integrating and manipulating data across these platforms. This is where the magic of parameters in Power BI becomes a game-changer. In this blog, we'll dive into a step-by-step guide on how to create and implement Power BI parameters for both the company and environments when dealing with Business Central tables. This approach not only enhances the flexibility of your reports but also ensures that you can swiftly adapt to different business scenarios with minimal effort.

By the end of this guide, you'll have a clear understanding of how to create these parameters, modify the M Formula Language to reference them, and see firsthand how these small adjustments can lead to significant improvements in your data handling capabilities. So, whether you're a seasoned Power BI user or just starting out, prepare to unlock new levels of efficiency in your reporting workflow!

Creating a Business Central Company Parameter

1. Click on Home in the upper menu and then click on Transform data to open the Power Query Editor in Power Bi.

Click on Transform Data to Open the Power Query Editor

2. Click on Home in the upper menu and then New Parameter under Manage Parameters.

Click on New Parameter

3. Enter the following in the Manage Parameters pop-up window and then click the OK button.

a. Enter CompanyParameter in the Parameter Name.

b. Add a Description for the parameter.

c. Accept the default of Required.

d. Change the Type to Text.

e. Change the Suggested Values to List of values and then enter your companies in the list below. Warning: the company names are case sensitive.

f. Enter the Default Value company name.

g. Enter the Current Value company name.

Creating a New Parameter

4. You will now see the following screen with the Parameter. If at this point you need to modify the parameter you can do so by clicking the Manage Parameter button.

You can at any point in the future enter the Power Query Editor and change the company drop down value to change your Business Central company for all Business Central tables in the report.

A Finish Business Central Company Parameter

Referencing the Business Central Company Parameter

5. In the Report View click on the Data Pane on the left side of the screen and right-mouse on the Business Central table you want to modify.

Edit the Customer Table Query

6. Click on Home in the upper menu and then Advanced Editor.

Click on the Advanced Editor

7. Following is the original M Formula Language for the Business Central Customer table.

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_table

Perform the following changes in the editor to make use of your new CompanyParameter. When you are finished click the Done button and then close and save your changes.

let
    Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
    SANDBOX = Source{[Name="SANDBOX"]}[Data],
    COMPANY = SANDBOX{[Name=CompanyParameter]}[Data],
    v2.0 = COMPANY{[Name="v2.0"]}[Data],
    customers_table = v2.0{[Name="customers",Signature="table"]}[Data]
in
    customers_table

Creating and Referencing a Business Central Environment Parameter

You can also create and reference a parameter for your Business Central environment.

8. Create an environment parameter as you did with the company parameter. When entering the environments make sure to enter them as all capital letters.

Adding the Business Central Environment Parameter

9. Using the company example we created earlier make the following changes to the M Formula Language. Close and save the changes.

let
    Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
    ENVIRONMENT = Source{[Name=EnvironmentParameter]}[Data],
    COMPANY = ENVIRONMENT{[Name=CompanyParameter]}[Data],
    v2.0 = COMPANY{[Name="v2.0"]}[Data],
    customers_table = v2.0{[Name="customers",Signature="table"]}[Data]
in
    customers_table

Conclusion

As we wrap up this guide, it’s clear that the integration of Power BI parameters with Microsoft Dynamics 365 Business Central tables is more than just a technical process—it's a strategic approach to elevate your data reporting and analysis. By following the steps outlined for creating and referencing both Company and Environment parameters, you have added a layer of dynamism and adaptability to your reports.

Remember, the key to harnessing the full potential of these tools lies in understanding the nuances of the M Formula Language and how it interacts with the parameters. The ability to quickly switch contexts in your reports, adjusting for different companies and environments, is not just about saving time—it's about gaining insights that are tailored, precise, and relevant to the specific scenarios your business encounters.

In today’s data-driven world, such agility is invaluable. Whether you're analyzing trends, forecasting, or simply keeping track of day-to-day operations, the combination of Business Central and Power BI, enhanced through the use of parameters, provides a robust platform for making informed decisions. So, go ahead and apply these techniques, experiment with the possibilities, and watch as your reports transform into more powerful, flexible, and insightful tools in your business intelligence arsenal. Happy reporting!