Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    Works like a charm! Thank you very much!