Certain columns to rows

[Anonymous]

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:

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:

Thank you in advance!

[v-qiuyu-msft · Accepted solution]

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: 

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: 

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

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

5. Select column Product and click Pivot Columns. 

6. Renamed first two columns to get final results. 

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. 

Best Regards,
Qiuyun Yu

[Anonymous]

Works like a charm! Thank you very much!