Today we are going to discuss about 2 methods to unstack rows into columns using M-Code in power query. This task can be accomplished through the user interface and M-Code.
Before that, let me show you the requirement and desired result.
Our source data resides in the range C2:C22. In this data, take a look at first five rows,
We can see that, Date, Customer Id, Customer name, Region and Sales Amount details are stacked in single column. Each set of five rows consists of field data. Our task is to transform each set of this data into columns as shown in the table in G2 cell.
Let me start this from scratch, Lot to learn, let's dive in.
Convert our source data into table format. To do that, click inside the data and press shortcut Key Ctrl + T or Ctrl + L. Name the table by pressing the shortcut key Alt + JT + A.
I named it as 'tStack'.
Press shortcut key Alt + A + P + T, to get this table into power query editor.
Method 1 - User Interface:
In right side Applied steps pane, delete the step 'Changed Type'.
Add an index column starting from zero as shown below.
In menu bar -> Add Column -> Drop down in Index Column -> select from zero.
Now, select [Index] column, then in
Menu bar -> Add Column -> Drop down in Standard -> select modulo.
In modulo dialog box, feed 5 as value. We fed 5, since we have 5 set of data in our source table.
What modulo does?
Modulo is the replacement of MOD function in excel. It divides the [Index] Column value by fed value which is 5 and gives the reminder.
Numerator = [Index],
Denominator = 5.
Again, select [Index] column, then in
Menu bar -> Add Column -> Drop down in Standard -> select Divide (integer).
In Integer-Divide dialog box, feed 5 as value.
What Divide (Integer) does?
This simply divides [Index] column value by 5 and picks the nearest smaller integer.
Modulo column will act as column indicator and Integer-Division column will act as row indicator. [Index] column served its purpose and so we can delete this by,
Select the column, right click and click remove.
We are going to pivot this data by placing,
Modulo in column area,
Integer-Division in row area and
Data in values area.
So, select Modulo column, then in
Menu bar -> transform -> select Pivot Column,
Select [Data] from values column drop down as shown below.
Click on Advanced option, select Don't aggregate and click OK.
In excel pivot table, if we drop the text fields in values area, it will do count operation by default. But here, in power query, we do have options not to do aggregation and so we selected Don't aggregate option.
Now, our transformation result will look like below,
We can delete [Integer-Division] column and rename each column by double clicking on the column header.
0 -> Date,
1 -> Customer Id,
2 -> Customer Name,
3 -> Region,
4 -> Sales Amount.
Then, click on the ABC123 in Date column and select Date data type.
In the same way change data type into Decimal Number for Sales Amount column.
Now all the transformations are done, and the table is ready to load into excel.
Menu bar -> Home tab -> Drop down in close & load -> select Close & Load to.
Now, 'Import data' dialog box will open, select table option and Existing sheet, click on the cell based on your preference.
Method 2 - M Code:
Load the source table into power query editor as I told you earlier.
Delete Changed Type step in Applied step pane at right side.
As of now Source is a table, to convert that into list, we are going to use field access operator [] (Square Brackets).
Click fx in left side of formula bar,
Enter [Data] after Source,
=Source[Data]
This is list now, you can see in the left side pane, new icon for 'tStack' compared with previous picture. From now on, we will work on this list.
We are going to split this list, so that each list will carry 5 values.
Enter below formula in formula bar,
= List.Split(Source[Data],5)
Arguments of List. Split function is,
[Data] column in source table is fed in list argument,
Value 5 is fed in pagesize argument.
Output of this step will look like below.
Our list got split into 4 list and each list consists of 5 values. I named the step as 'ListSplit'.
Click fx in formula bar to create new step.
Enter below formula in formula bar,
= Table.FromRows(ListSplit, {"Date", "Cutomer Id", "Customer Name", "Region", "Sales Amount"})
Arguments of Table. FromRows function,
Output of Last step fed here in rows as list argument.
In 2nd argument, enter the desired column names in list format.
Change data type for Date and Sales Amount columns and load this data into excel sheet.
コメント