Use Parameters to Combine Data

[AllisonKennedy]

There are many reasons you might need to combine multiple queries into one appended table, and the Power BI Get Data From Folder makes this process much easier for many data sources. However, it has some limitations that we can overcome through the use of parameters.

I have used parameters to combine data from multiple Excel sheets, but in this post I am going to use data from a public website to demonstrate one use of parameters to combine data in Power BI.

Follow along using the NZ Population data on my site.

Stage A: Create a parameter values table and parameter. 

Before we can combine the data, we need to create a parameter and a list of values that will be used to populate that parameter.

1. Start with a blank Power BI report, Get Data from Web.
2. Paste the URL: https://sites.google.com/site/akdatasamples/nzpop/ and click OK.
3. Select Table 1 and Transform the query. Rename this query to NZ Population. 
4. In the Home tab in the ribbon, select Manage Parameters > New Parameter.
5. Fill in the Parameters properties:
1. Name: PopRegion, 
2. Type: Text, 
3. Current Value: north_island_a
   (note the current value is the same value as in the first row of our NZ Population table).

Stage B: Create sample file query using first subset of data.

Now we need to get our first sample file. In this example, that will be the table in the north_island_a regions on the North Island A subpage from the above website.

1. Select New Source, Web.
2. Paste the URL: https://sites.google.com/site/akdatasamples/nzpop/ and this time select 'Advanced'.
3. We have the first URL part, now we need the second URL part. Choose 'New Parameter' for the second URL part drop down. Name the parameter "PopRegion" and set the data type to 'text'. 
4. Set the current value to north_island_a
5. Click OK.to create the parameter.
6. Click OK to load from Web.
7. Select Table 1 from the Navigator pane and click OK.

You should see a table of NZ population from the North Island A regions of Northland, Waitemata, Auckland and Counties Manukau. Think of this as your 'sample query' that determines everything that must be done to the data before it can be combined.

Stage C: Create a custom function based on the sample query.

1. Right click on Table 1 in the queries pane, and choose 'Create Function'. 
2. Name the function 'GetPop'.

That's it! Power BI works its magic in the background and creates a custom function based off the sample file we created in the previous stage.

Stage D : Apply the custom function to each row of the parameter values table.

Now we just need to apply that custom function to each subpage in the NZ Population parameter values table we created in Stage A so that we can see the population for the entire country.

1. Select the NZ Population query (our parameter values table).
2. In the Add Column tab, select 'Invoke Custom Function'. 
3. Type 'PopTable' for the new column name. Select PopRegion for the function.
4. In the row that now appears, select the SubPage column as the parameter input for the custom function. 
5. Click OK. 

Power BI will now use the text in the SubPage column to populate the URL and get data from each subpage listed.

You should now have a table for each row/subpage of the NZ Population query. Click in the white space in the same cell as the word Table to see a preview of the table in the lower half of the screen.

Stage E: Expand the data custom function column.

Our final step is to expand the data from these tables and combine into one query. As you can from the table preview above, all the information we need is in the table itself, so we can remove our other two columns now. All you should be left with is the PopTable column of tables.

1. Click the double arrow in the column heading.
2. Keep Expand selected.
3. Tick Select All.
4. Untick Use original column name as prefix. 
5. Click OK.

Congratulations! You have successfully used parameters to combine data of similar structure into one long appended table. Set the data type for each column before you Close and load the data. See what insights you can gather about the NZ population.

Download the NZ Population.pbix file.