Quarterly Columns to Date

[ChrisR22]

Hello,

I currently have data regularly coming to me in the format 

Group	Q1	Q2	Q3	Q4
Alpha	$50,000	$45,000	$45,000	$90,000
Beta	$25,000	$49,000	$71,000	$38,000
Gamma	$88,000	$68,000	$57,000	$63,000

Because of this format I cannot build charts or slicers based on the time (quater) because they are in separate columns. Does anyone have a good method of applied steps or DAX formulas that can automatically transform my dataset into the following format

Group	Quarter	Revenue
Alpha	Q1	$50,000
Alpha	Q2	$45,000
Alpha	Q3	$45,000

... and so on. In addition to revenue there are also separate data tables for headcount and targets that also arrive in this format. Ideally I'd like to be able to apply the transformations to all three incoming data tables at once to get something like:

Group	Quarter	Revenue	Headcount	Target
Alpha	Q1	$50,000	50	$48,000
Alpha	Q2	$45,000	52	$47,000

I can use an unpivot function in Power Query, but there are two issues with that:

1. unpivoting multiple bunches of quater columns (i.e. the 4 columns for revenue and the 4 colums for headcount) return more than 4 rows, in fact it returns 8. I'd need them all consolidated into 4 rows, one for each quarter.

2. I cannot figure out how to make that automatically apply outside of building a macro, which in itself is a manual process as someone would have to run it each time data was provided.

Alternatively, if someone has a good method to be able to call the quarter field out in visuals without having to transform the data that would be sufficient.

Thanks in advance!

[Ritaf1983 · Accepted solution]

Hi @ChrisR22 

The most efficient way to transform your tables is definitely through Power Query (PQ). The fact that the number of rows increases after unpivoting shouldn’t be a concern because once you aggregate the data in DAX, everything will be summarized based on the level of granularity in your visualizations. Also, there’s no need for a macro because Power Query steps are recorded and automatically repeat every time you refresh the data.

Here’s how you can approach this:

1. Unpivoting the Data:

   • In Power Query, select the columns for each quarter (e.g., Q1, Q2, Q3, Q4), and use the "Unpivot" option to turn these columns into rows. This will create a column for Quarter and another one for the Revenue (or other metrics like Headcount and Target).

2. Combining Multiple Data Tables:

   • To handle multiple data tables like Revenue, Headcount, and Target, you can unpivot each table individually and then merge them on the Group and Quarter columns. This way, you’ll end up with a combined table that has Group, Quarter, Revenue, Headcount, and Target in a single structure.

3. No Manual Macro Needed:

   • Power Query automatically applies the transformations when refreshing the data, so there’s no need to build or run a macro. This ensures that every time new data arrives, it’s automatically transformed without any manual intervention.

4. Using a Date Table (For Larger Datasets):

   • If your dataset is large or spread across multiple tables, it might make sense to use a Date Table to link all the data tables together. This allows for easier management of Quarter data, helps align time intelligence functions, and improves performance. By creating relationships between your Revenue, Headcount, and Target tables with the Date Table, you can manage large datasets more effectively.

By following this approach, you can avoid the issues you mentioned, keep the data transformation automated, and build dynamic reports with a flexible structure. Also, using a Date Table will help you work efficiently with larger datasets, allowing you to scale without manual workarounds.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly