how to pivot two data types of multiple columns into three columns

[kevinjhk]

I have an origianl data to show Quantity and USD by product name and by quarter (in format of YYYYQQ) as below.

I want to transform this to the table form at the right side. I know how to unpivot multiple columns into two columns if the columns are the same type, either quantity or US dollar. But i couldn't do two different data types into three columns; so one for quarter that is shared with Quantity and USD. 

[MalS]

You can duplicate the data table, then unpivot the quantities in one copy of table and the dollar amounts in the other copy. 

Finally, you can bring it all back together again with a 'Merge Queries' operation.

Let me know if you need more detail on the steps required to do this. 

[Anonymous]

thanks for solution....

kudos

regards,

Rahul M

[MalS · Accepted solution]

Or, a better way might be this:

1. Unpivot all columns except Product Name (rename the "Attribute" column to, say "Quarter")

2. Add a conditional column using this condition: 

3. Pivot the column you just created:

4. Replace values (to get rid of the $_ prefix):

5. Then group the rows:

[Thiyagu]

Hi @MalS

I have a same problem, please help me on this,

https://community.powerbi.com/t5/Desktop/Conditional-Formatting-Slicer/m-p/207232#M91309

Many Thanks,

Thiyaga

[kevinjhk]

 Hi MalS, the second one," a better way", works very well. it's a brilliant idea. thanks a lot for your help.