Merging multiple Dynamic Reports in PAfE using Power Query

Ever wondered how good it would have been to merge multiple dynamic reports next to each other for doing your analysis from disparate reports, only to be realise that it can’t be possible?

Fortunately, Power Query is here to our rescue and that’s what we will be looking at in this blog post.

So, to start with, what is Power Query? Power Query, also known as Get & Transform in Excel 2016, is an Excel interface that finds data from wide range of data sources and performs data transformations using its ETL processing editor called Power Query Editor. Power Query then creates queries and reuses them by refreshing the queries. 

We can use Power Query on top of Dynamic Reports in PAfE to merge different reports from different cubes and dimensionalities and position them alongside each other and analyse the data in Excel. 

Below are the summary steps involved to achieve the desired layout:

Step 1: Create Dynamic Reports for merging in PAfE.

Step 2: Create a named range for each Dynamic Report.

Step 3: Go to Data tab in Excel, select Get Data 🡪 From Table/Range.

Step 4: Perform data transformations in the reports, if any, in Power Query.

Step 5: Create queries for each report in Power query.

Step 6: Finally, merge the queries.

Imp: For the query to be successfully created, you’d need at least one column in each report that acts as a key.

In the example below, I have created two queries namely rFTE and rExp for my two Dynamic Reports in PAfE where I’ve used the Organization dim as the key. 

One report shows the FTE data of the organization from the Employee cube (rFTE) and the other report shows the Expenses data of the organization from the Compensation Reporting cube (rExp).

Fig 1: Shows the Dynamic Report for FTE and the query named rFTE being created in Power Query.

Fig 2: Shows the Dynamic Report for Expenses and the query named rExp being created in Power Query.

To demonstrate one example of how powerful the Power Query transformation feature is, in my report, I had the employees on my column and in Power Query I unpivoted them and converted it into rows to display column data in rows, without changing the underlying structure of my dynamic report as can be seen in the screenshots below.

Before Unpivot:

After Unpivot:

Once the queries are created, we can then merge it together to show the reports next to each other.  

Below screenshot shows two dynamic reports placed next to each other as table in Excel.

Columns A-B is one report and columns C-E is another report.

To wrap up, summarising few points to take note of:

1. The connections that are created in Power Query are live, meaning its always pulling the updated records and data from the reports.
2. The output is displayed as Table in Excel that means all Table formatting can be applied to merged output.
3. All the steps that are created in Power Query are recorded and executed each time the report is refreshed.
4. Multiple reports can be merged and as long as they have at least one homogenous column, Power Query will map it and merge the reports.
5. The columns can be unpivoted to display them as rows.
6. Can work in conjunction with data imported from other sources