Power Query Tips 2

Power Query Tips 2:

How you transform columns values into rows in Power Query?

You can easily transfers/converts columns data into rows through Excel Power Query. Suppose you have a monthly project costs data in column wise in Excel workbook for 2019 and 2020 calendar year. You want to convert monthly project costs into row wise and add two years data into one worksheet file.

Please follow my example for details.

Note: Please visit my previous blog for Introduction of Power Query which I posted before.

Loading your data into Power Query, follow below steps

Step 1: For loading your data into in Power Query, click on Data, Get Data, From File > From Workbook as below pictures

Step 2: You need to open workbook file from your folder which you want to load into power query.

Step 3: For loading multiple sheets click Select multiple items under Navigator, click your sheet. In this example, I selected Project_cost_2019 and Project_cost_2020

Note: you can select single worksheet

Step 4: Click Transform Data,

Step 5: Click

for return original columns heading, select all columns which you want to transform into row level.

In my example I highlighted months columns as below picture.

Step 6: Click Transform, Unpivot option then select Unpivot Only Selected Columns as below picture.

Step 7: Now your selected columns will be transformed into rows, for rename your column title double clink on Attribute and type the meaningful name.

In my example I changed it Month, similar way I rename the Value column into Project Cost.

Step 8: For changing the column data type, select your column click Data Type: from drop down menu you can pick up any data type as you need.

In this example I changed Month column into Date type and Projects Costs into Currency format.

Step 9: Click Close & Load option then your data will be automatically loaded into sheet as below picture.

In this example in Sheet3 loaded Project_cost_2020 data and Sheet2 loaded Project_cost_2019 data

Step 10: Similar way you can load multiple sheets into Power Query. For loading Project_Cost_2019 repeat the Step 1 to Step 9.

Step 11: Now you need to save this data into new file name.

Next blog I’ll explain How to merge multiple sheets into one table or single file in Power Query....

Please subscribe my blog for more and helpful Power Query tips and Power BI tips.

Stay well..Happy learning