cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Can anyone help me in combining two columns data into one column and one column data into one row?

Hi All,

 

I have a raw data and I need to combine two columns (Status Mapper and Ultimate Group Mapper) into one column and one column data  (Value Mapper) into one row , as shown in New Table. Can anyone please suggest how to do this.

 

Raw Data   
Status MapperUltimate Group MapperValue MapperItem Counter
PLDW+$250m1
PLDW+$250m1
PLSPG+$30m1
PLDW+$10m1
TDDWMissing numeric value1
PLSPG+$30m1
PLSPG+$250m1
TDDW-$10m1
#N/A#N/A+$250m1

 

 

New Table     
 -$10m+$10m+$30m+$250mMissing numeric value
PL 123 
TD1   1
DW11 21
SPG  21 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Can anyone help me in combining two columns data into one column and one column data into one ro

you can get what you ask by making two pivots and an append table.

 

image.png

 

 

image.png

 

image.png

 

 

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Can anyone help me in combining two columns data into one column and one column data into one ro

I would suggest using a Matrix visual and keeping the raw data as is.
In Matrix visual:
Item Counter in Values (as SUM)
Value Mapper in Columns
Status in Rows

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

2 REPLIES 2
Highlighted
Memorable Member
Memorable Member

Re: Can anyone help me in combining two columns data into one column and one column data into one ro

you can get what you ask by making two pivots and an append table.

 

image.png

 

 

image.png

 

image.png

 

 

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Can anyone help me in combining two columns data into one column and one column data into one ro

I would suggest using a Matrix visual and keeping the raw data as is.
In Matrix visual:
Item Counter in Values (as SUM)
Value Mapper in Columns
Status in Rows

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors