cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnn4282
Helper I
Helper I

Unvipoting columns

https://we.tl/t-yjVsqZaXm3

I wonder if there is any possibility of unpivoting my columns as follows.

jnn4282_0-1656317996676.png

Want to be like this ↓

jnn4282_1-1656318066683.png

 

I tried a lot in Power BI  but was unable to get the desired result.

Would you

1 ACCEPTED SOLUTION
Seanan
Super User
Super User

Hi @jnn4282 

Under the Home tab click transform data -> hold CTRL and left click on all of your date columns (Jan-Dec) -> Right click and select unpivot columns.

NVIDIA_Share_NpJKJuFzR9.pngNVIDIA_Share_x0CB5SekK3.png

 

 

 

 

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

View solution in original post

10 REPLIES 10
Seanan
Super User
Super User

Hi @jnn4282 

It seems like pivoting the acc_title column has caused some mismatch data types. Could you please take a picture of your PL table.

https://we.tl/t-qFsvFtcHav

 

Hi @Seanan 

I have attached the link to my pbix file for your reference.

jnn4282_0-1656326532792.png

 

Hi @jnn4282 

Unfortunately, I am unable to view the data in your report as the data source is coming from your hard drive (DataSource.Error: Could not find a part of the path)

Could you please take a picture of your PL table from within the transform data view.

jnn4282_0-1656327752171.pngjnn4282_1-1656327781383.png

 

I hope you can figure it out. Its so small though

Hi @jnn4282 

I've managed to figure out where the errors are coming from. If you click tranform data and in your PL table scroll to the far right until you find the Net Profit column. Right click the column and select remove errors (These rows won't matter as it seems like there is no data stored for these rows so they have no value).

m2Wdry4Y2H.png

 

 

 

 

 

 

Click apply and close and your data should load without any errors now.

 

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

I found the cause as per your instruction and managed to solve the problem.

Thanks and big KUDOS @Seanan 

Seanan
Super User
Super User

Hi @jnn4282 

Under the Home tab click transform data -> hold CTRL and left click on all of your date columns (Jan-Dec) -> Right click and select unpivot columns.

NVIDIA_Share_NpJKJuFzR9.pngNVIDIA_Share_x0CB5SekK3.png

 

 

 

 

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

Hi Seanan,

Thank you for your prompt response. Month and Value were fine with this way. But my desired result is as per the excel pivoted sheet. Please download it and see it for me. I want Acc_Title as the header

Hi @jnn4282

After unpivoting the month columns, click on your acc_title column and select pivot column (under the home tab in transform data), set the values column to the value column containing the monthly numbers and set the advanced options to "Don't Aggregate". This will then give you the result you are looking for.

NVIDIA_Share_bwFqrJhfth.png

 

 

 

 

 

 

 

 

 

 

NVIDIA_Share_hE8o1Ujxyx.png

 

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

jnn4282_0-1656322358309.png

Thank you @Seanan 

It worked well but through an error.

jnn4282_1-1656322667253.png

Is there anything that I did wrong

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.