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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Certain columns to rows

Hello,

 

I have a big table which is updated every month (includes sales plan, sales fact and execution) for each product and divided by months. Every month 3 columns are added and that sales data is written in. For example, in March this table would look like:

 

table.JPG

Then in April 3 new columns would be added and on top of "Plan" there would be a cell with a text 2017 Apr (please note that month is written only above Plan column; they are not merged). Hopefully it is clear how the input data looks like 🙂

What I want to do is to leave only 5 columns: Product, Plan, Fact, Execution and new column "Date". Date should be taken from that particular cell in row 1.  Is there a way to create a query without altering the initial data?

This is how I want my data (in a query) to look like in the end:

eventualy.JPG

Thank you in advance!

 

 

 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

In Power BI deksotp, when you get data from Excel file, please open Query Editor. Follow below steps: 

 

1. Select the Transpose to get below table: 

 

1.PNG

 

2. Click Use First Row as Headers, it will genearate two steps in Applied Steps pane automatically. If you don't want the date "2017 Jan" change to acutual date type, you can remove Changed Type 1 step in applied steps then get below results: 

 

21.PNG22.PNG

 

3. Select the first column, click Fill -> Down. 

 

3.PNG

 

4. Select columns Product 1, Product 2 and Product 3, click Unpvot Only Selected Columns. 

 

4.PNG

 

5. Select column Product and click Pivot Columns. 

 

5.PNG51.PNG

 

6. Renamed first two columns to get final results. 

7.PNG

 

 

 

You can also download attached pbix file, when you open it, you can change Data Source to your local file then you can modify steps in Query Editor. 

 

q3.PNG

Best Regards,
Qiuyun Yu

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

In Power BI deksotp, when you get data from Excel file, please open Query Editor. Follow below steps: 

 

1. Select the Transpose to get below table: 

 

1.PNG

 

2. Click Use First Row as Headers, it will genearate two steps in Applied Steps pane automatically. If you don't want the date "2017 Jan" change to acutual date type, you can remove Changed Type 1 step in applied steps then get below results: 

 

21.PNG22.PNG

 

3. Select the first column, click Fill -> Down. 

 

3.PNG

 

4. Select columns Product 1, Product 2 and Product 3, click Unpvot Only Selected Columns. 

 

4.PNG

 

5. Select column Product and click Pivot Columns. 

 

5.PNG51.PNG

 

6. Renamed first two columns to get final results. 

7.PNG

 

 

 

You can also download attached pbix file, when you open it, you can change Data Source to your local file then you can modify steps in Query Editor. 

 

q3.PNG

Best Regards,
Qiuyun Yu

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Works like a charm! Thank you very much!

Helpful resources

Announcements
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!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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