Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sb_powerbi
Helper II
Helper II

how to convert column names and values in to single column as row level values?

Hello Team,

 

Thanks for spending time to read my post. I need to help to resolve my issue.

 

I have excel sheet data which contains Column names Sale Rep,Q1,Q2,Q3,Q4 with data. I want to convert all the Column names with Row level data. Please see the screen shot wiht examples.

 

Example Data.PNG

 

IN Power BI I want to show the quarter names in X axis and Sales amount in Y axis. I want to add the Quarter into drill down of Sales Rep field.

 

It would be great help if i can get any suggestions.

 

Thanks,

 

2 ACCEPTED SOLUTIONS

Select the Sales Rep column first and the choose unpivot other columns.

Specializing in Power Query Formula Language (M)

View solution in original post

i highlighted the quarter columns and then selected unpivot





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
vanessafvg
Super User
Super User

you need to use pivot functionality in power query 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks for the quick response.

If i use Pivot functionalty the columns will comes to row values and Sales amount will go into columns as values. Please correct me .

sorry if you unpivot not pivot yes... that is what will happen.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




If we use unpivot no use. for each column unpivot it will create new Attribute & Value fileds.. Actuallly for each unpivot columns It should append with the same Attribute & Value  fields, so that i can get my output.

 

is your desired output this? because if it is i just did the unpivot and it worked fine

 

Capture.PNG





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




This is great .. I want to convert like this only ..Pls suggest me how to do this.. because if i try to unpivot in edit quereis . I am getting out put like this.

 

 

UNpivot DAta.PNG

 

i highlighted the quarter columns and then selected unpivot





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Edit: this reaction was entered while other reactions came in at the same time.

 

That's also possible, the code generated will still be using Pivot Other columns.

 

But all quarter columns should be selected at the same time before choosing unpivot columns, not 1 by 1.

Specializing in Power Query Formula Language (M)

Hello Marcel,

 

I have facing some other issue. because I have unpivot other columns also for Example.. now we have done Sales amount ,same like we have to  do other star sales by quertely with the same excel sheet. If we do the unpivot I am getting duplicate values to the first unpivot values. Is thr any way to complete this taks without any duplication. I mean I want the out put like separately ..

 

 

Your information is not very clear.

 

It looks like you need to create separate queries to have your data normalized in different tables and then define your table relationships.

Specializing in Power Query Formula Language (M)

Thank You

 
Really helped your idea.
 
 

Select the Sales Rep column first and the choose unpivot other columns.

Specializing in Power Query Formula Language (M)

Thank you so much Marcel . you saved my day.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.