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

[sb_powerbi]

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.

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,

[MarcelBeug · Accepted solution]

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

[vanessafvg]

you need to use pivot functionality in power query 

[sb_powerbi]

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 .

[vanessafvg]

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

[sb_powerbi]

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.

[vanessafvg]

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

[sb_powerbi]

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.

[vanessafvg · Accepted solution]

i highlighted the quarter columns and then selected unpivot

[MarcelBeug]

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.

[sb_powerbi]

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 ..

[MarcelBeug]

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.

[sb_powerbi]

Thank You

vanessafvg

Really helped your idea.

[MarcelBeug · Accepted solution]

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

[sb_powerbi]

Thank you so much Marcel . you saved my day.