Help with a certain type of data transformation

[tusmo]

I have only worked with Power BI for a couple weeks now, but I've noticed a certain pattern of data transformation I need to do repeatedly. For example I might have time series data with columns: Date | Sales of product A in region X | Sales of product A in region Y | Sales of product B in region X | Sales of product B in region Y. Now I want to visualize sales accross different product types and regions. In order to do that I transform the data into the following form: Date | Product | Region | Sales, now I can easily create interactive visualizations. However, as the number of differerent labels, in this case products and regions, increases doing this manually in the Query editor becomes tedious. Is there a way to easily do this within Power BI? I did write a Python script to do this for me but that's an additional step in the workflow I'd rather get rid off.

[Greg_Deckler]

Would need an example of your source data and desired output. Can't really visualize your manual steps. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[tusmo]

Thank you for you answer, I'm sorry if my description of the problem was inadequate. Lets say that for example I have data that looks like this:

 , and I want to create a chart where you can interactively visualize the data like this:

So what I need to do is to transform the data into the following form:

My question is, what would be the easiest way to do this transformation within Power BI for some arbitrary numbers of paint colors and countries? The way I'm doing it right now is by manually duplicating the original table, deleting all but one row, for example "Sales of red paint in Germany" and then adding custom columns "Red" and "Germany" and then appending the tables.

[vmakhija · Accepted solution]

@tusmo

You can do Edit Queries, Select your Date column, right click and do Unpivot Other Columns.

This will give you Date column, Attribute Column and Value Column. You can rename Attribute and Value as required.

After this, select Attribute column, go to Add Column, use Column from Examples, create 2 columns for Color and Country.

Regards

[tusmo]

This works great! I'm new working with data in general so I wasn't familiar with the concept of pivoting and I didn't know of the "Column from examples" function, it seems extremely useful. The data I'm working with is formatted nicely enough that I can also use "split column - by delimiter" to achieve this even more easily.