“Can PowerBI cut down the time and work it takes to update a report? Every month, I export last month’s data into an Excel spreadsheet template. I need to make sure my current report is updated with new data. Is there an easy way to do that in PowerBI?”
Such is a common and important concern across organizations. Once a report has been created using PowerBI, how can it be kept it up to date as new data comes in with minimal time and effort?
Fortunately, PowerBI does some of the heavy lifting for us already. Any visuals in a report would automatically update to reflect any additional data. The only remaining hurdle is the tedious task of shaping and transforming the new data, which can be easily made repeatable using PowerQuery’s custom functions and parameters.
Anyone who has used PowerQuery has encountered Applied Steps. Applied Steps enumerate the actions performed to shape and transform a dataset. A custom function neatly packages these Applied Steps together for portability. Instead of manually and painstakingly performing each action for every new dataset, we can use a custom function. This makes it particularly ideal for simplifying repetitive tasks such as transforming multiple datasets that follow the same structure, but contain different data.
Same structure, but different data
Custom functions rely on datasets following the same structure, specifically:
- The same kind of data source (i.e. Excel spreadsheet, CSV) must be used across data sets
- Datasets must share the same number and order of columns, and column header names
- If using an Excel spreadsheet as a data source, the worksheet name must be consistent across different files.
Otherwise, a custom function will be unable to run against the dataset.
Where do parameters fit into all this?
Parameters are value placeholders and allow us to easily provide custom functions some arbitrary value as input. This value usually differs each time a custom function is run. In this scenario, the arbitrary value in question is the file path. Given some file located in a specific directory, the custom function will perform a set of Applied Steps.
Before anything else, we need to create a parameter for our file path. In the Home tab, click Manage Parameters > New Parameter.
The Parameter dialog will prompt you for the following:
- Name – A friendly name to identify the parameter.
- Description – (Optional) Additional information about the parameter (i.e. purpose, expected values)
- Type – The data type of the value to be stored in the parameter.
- Suggested values – The kind of value/s to be stored in the parameter.
- Current value – The current value to be stored. It must NOT be empty.
Click OK to create the new parameter. Once created, the parameter will be listed by name in Queries.
In Queries, you may notice that the parameter name is followed by some text enclosed in parenthesis. This text conveniently displays the current value stored in the parameter. When the value is changed, the text will automatically update itself as well.
Using the parameter
Now that we have a parameter for our file path, we need use it as a data source. In Queries, select the dataset whose Applied Steps we want to use in a custom function.
In Applied Steps, double-click Source.
In the Excel dialog, the file path is explicitly stated. By replacing it with a parameter, we can easily change the file path by updating the value in the parameter.
To use a parameter instead, click on the dropdown box to the left of the text box and select Parameter.
Select the appropriate parameter in the dropdown box to the right.
Select the last action in Applied Steps to ensure that no errors have occurred.
Finally, custom functions!
After setting up the parameter, we need to create a custom function. Right-click the dataset and click Create Function.
In the Create Function dialog, provide a name for the new custom function and click OK.
The custom function will then be listed in Queries.
To use the custom function with another dataset, click on the function name in Queries and input the full file path of the new dataset, including the file extension (i.e. *.xlsx, *.csv).
The output of the custom function will be listed as Invoked Function in Queries.
From the Invoked Function dataset, we can perform additional actions to further transform the data as necessary.
Updating a custom function
Sometimes, we may need to make some changes to a custom function after it has been created. The good news is that we don’t need to delete and recreate the custom function to support any changes. Actions can be added to or removed from the original dataset we used to create a custom function and these will be automatically applied to all outputs of the custom function.
At this point, we’re now able to quickly and easily apply a set of Applied Steps against datasets that share the same structure. However, using custom functions outputs a new dataset each time. In the next post, we will explore how we can neatly consolidate the output of a custom function for multiple datasets.