Creating functions

Power Query allows you to create your own functions, which can be useful when you want to apply the same logic multiple times. One way to create a custom function is by converting a query to a function. If your query already uses parameters, then those will become the parameters of the new function.

Let’s continue our AdventureWorks example and create a function that will output sales for a particular year based on the year you enter:

Right-click the CompanySales query and select Create Function.

In the Function name box, enter SalesByYear.

Select OK.

This creates the SalesByYear group of queries, which contains the Year parameter, the CompanySales query, and the SalesByYear function.

Now test the new function:

Select the SalesByYear function in the Queries pane.

Enter 2008 in the Year box.

Select Invoke.

This creates the Invoked Function query that returns 73 rows and contains data for year 2008 only, which you can verify by selecting the OrderYear column filter button.

Note that there is a special relationship between the SalesByYear function and CompanySales query: updating the latter also updates the former, which in turn updates all function invocations. For example, we can remove the ID column from the CompanySales query, and it will disappear from the Invoked Function query, too.

Exam Tip

You should be able to identify scenarios when query parameters can be beneficial based on the business requirements of a client.

Connect to a dataflow

In addition to Power BI Desktop, Power Query can be found in the Power BI service: you can prep, clean, and transform data in dataflows. Dataflows can be useful when you want your Power Query queries to be reused across your organization without the queries necessarily being in the same dataset. For this reason, you cannot create a dataflow in your own workspace, because only you have access to it.

To create a dataflow in a workspace, select New > Dataflow. From there, you have several choices:

  • Add new tables Define new tables from scratch by using Power Query.
  • Add linked tables Linked entities are tables in other dataflows that you can reuse to reduce duplication of data and improve consistency across your organization.
  • Import model If you have a previously exported dataflow model file, you can import it.
  • Create and attach Attach a Common Data Model folder from your Azure Data Lake Storage Gen2 account and use it in Power BI.

The Power Query Online interface (Figure 1-14) looks similar to Power Query Editor in Power BI Desktop.

FIGURE 1-14 Power Query interface when editing a dataflow

Once you finish authoring your queries, you can select Save & close and enter the name of the new dataflow. After saving, you’ll need to refresh the dataflow by selecting Refresh now from the dataflow options in the workspace—otherwise, it won’t contain any data.

When a dataflow finishes refreshing, you can connect to it from Power BI Desktop and get data from it.