cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kevinjhk Frequent Visitor
Frequent Visitor

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

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. 

 

image.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
MalS Member
Member

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

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: 

Conditional.png

3. Pivot the column you just created:

Pivot.png

 

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

Replace.png

5. Then group the rows:

Group.png

 

View solution in original post

5 REPLIES 5
MalS Member
Member

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

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. 

MalS Member
Member

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

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: 

Conditional.png

3. Pivot the column you just created:

Pivot.png

 

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

Replace.png

5. Then group the rows:

Group.png

 

View solution in original post

kevinjhk Frequent Visitor
Frequent Visitor

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

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

Thiyagu Member
Member

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

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

rahulm Regular Visitor
Regular Visitor

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

thanks for solution....

 

kudosSmiley Happy

 

regards,

Rahul M

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,231)